How do I embed the Connection Manager inside the C# code of the SSIS Script Component?

Question

When I open a Script component, I can choose a Connection Manager from a dropdown list:

This Connection Manager has it all, if I had it as an object in the C# code, I would not need to write a hardcoded connection string anymore.

I tried it with an OLEDB provider and SQL but failed.

Question(s)

• How should I use an OLE DB Connection Manager in a Script Component of an SSIS Data Flow Task?
• Or if an OLE DB provider cannot be used, how can I embed the Connection Manager in an SSIS Script Component instead?

Luehrmann

VTC: No details about what failed or why, both answers are simply links to MSDN, and no clear way to incorporate those links into reasonably scoped answers. – Sagittal

Answer 1

This is well documented on MSDN, covering both VB and C# type of scripts: http://msdn.microsoft.com/en-us/library/ms136018.aspx

Goodhen

This is not for a script component, but for a script task! there are two different things. – Bul

Answer 2

The syntax is different between a Script Task and a Script Component. Check out this article for more than a couple side-by-side comparisons:

http://msdn.microsoft.com/en-us/library/ms136031.aspx

Homocyclic

Answer 3

    IDTSConnectionManager100 connMgr = this.Connections.ADONetAppStaging ; //this we need to give name in connection manager in script component

    SqlConnection myADONETConnection = new SqlConnection();

    myADONETConnection = (SqlConnection)(connMgr.AcquireConnection(null));


    //Read data from table or view to data table
    string query = "Select top 10 * From ##AP_Stagging_Temp_ExportWODuplicates Order by 1,2,3 asc ";
   // string query = "Select  * From ##AP_Stagging_Temp_For_JLL_ExportWODuplicates order by 1,2,3 asc ";
    SqlDataAdapter adapter = new SqlDataAdapter(query, myADONETConnection);

DataTable dtExcelData = new DataTable();
    adapter.Fill(dtExcelData);
    myADONETConnection.Close();

Annisannissa

Answer 4

Sometimes you have to reinvent the wheel

I got it to work, but neither with the help of the answers here nor with It's possible to use OleDbConnections with the Script Component?, and the default remarks of the "main.cs" that loads in VS at the beginning were misleading, see further below.

But it was worth the time: now I do not need to write down any connection string anymore in the C# code but just load the chosen connection manager's connection string without needing to write its name in the code.

Code

Here is the code that takes up the connection manager of the script component so that you do not need to hardcode the connection string anymore:

    public override void PreExecute()
    {
        base.PreExecute();
        string connectionString = Connections.Connection.ConnectionString;
        conn = new SqlConnection() { ConnectionString = connectionString };
        conn.Open();
        // create temp table, make sure it matches your input
        using (SqlCommand cmd = new SqlCommand(@"CREATE TABLE ##tmpTable(
[my_column1][int] NULL,
[my_column2][int] NULL,
...

Trick 1

The one trick that the other answers already tell is to make an ADO.NET connection instead of an OLE DB connection.

Trick 2

Do not listen to the remarks in the default file, the main help inside the default "main.cs" C# file in "VstaProjects" in Visual Studio that opens up if you click on "Edit script" the first time:

#region Help:  Using Integration Services Connection Managers
/* Some types of connection managers can be used in this script component.  See the help topic
 * "Working with Connection Managers Programatically" for details.
 *
 * To use a connection manager in this script, first ensure that the connection manager has
 * been added to either the list of connection managers on the Connection Managers page of the
 * script component editor.  To add the connection manager, save this script, close this instance of
 * Visual Studio, and add the Connection Manager to the list.
 *
 * If the component needs to hold a connection open while processing rows, override the
 * AcquireConnections and ReleaseConnections methods.
 * 
 * Example of using an ADO.Net connection manager to acquire a SqlConnection:
 *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
 *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
 *
 * Example of using a File connection manager to acquire a file path:
 *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
 *  string filePath = (string)rawConnection;
 *
 * Example of releasing a connection manager:
 *  Connections.SalesDB.ReleaseConnection(rawConnection);
 */

You do not need to write the name of the connection manager anywhere, and if I am not mistaken, you cannot get the code to work by writing it down anywhere unless you hardcode the connection string yourself. Instead, the connection manager that you choose in the menu is the one that is loaded into the Connections object. Since I could not find the name in the attributes of this object as it is said in the default remarks, I debugged the Connections object live and found that at the point of loading Connections, the full connection string is already an attribute of the Dynamic View of the Connections object (and mind: this connection string is loaded from the menu without any hardcoding):

Strangely, you have to get the connection string from the Connections object in one go: you cannot at first just load the Connections object like a rawConnection object that you can ask for attributes, as the default remarks tells you. That is why I replaced the rawConnection = Connections with string connectionString = Connections.Connection.ConnectionString;. I guess that this is needed since it is in a "Dynamic View" so that it must be fetched right away, but this is just a guess, remarks are welcome. Here is live how the debugger steps in to see how the object gets built:

Jolson