Scripting Patterns - SQL Server Integration Services Design Patterns - page 116

two most common ways to connect to SQL Server—through the OLE DB connection
and the ADO.NET connection.
Connecting to an ADO.NET connection manager through the Script task is a two-
step process, as shown in Listing 3-1 . First, create a reference to your existing Con-
nectionManager object (using the name you gave it in the SSIS package), and ac-
quire a connection from that object in code.
Listing 3-1 . Use an Existing ADO.NET Connection in the Script Task
// Create the ADO.NET database connection
ConnectionManager connMgr
= Dts.Connections["ADONET_PROD"];
System.Data.SqlClient.SqlConnection theConnection
=
(System.Data.SqlClient.SqlConnection)connMgr.AcquireConnection(Dts.Transaction);
Using an OLE DB connection manager in the Script task requires a little more code
but is a similar exercise to its ADO.NET counterpart. As shown in Listing 3-2 , you
have to add an intermediate object to make the appropriate data type cast when using
the OLE DB provider:
Listing 3-2 . Using an Existing OLE DB Connection in the Script Task
// Create the OLEDB database connection
// Note that we'll need to add references to
Microsoft.CSharp and Microsoft.SqlServer.DTSRuntimeWrap
ConnectionManager cm
= Dts.Connections["AdventureWorks2012"];
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100
cmParams
= cm.InnerObject as
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
System.Data.OleDb.OleDbConnection conn
= (System.Data.OleDb.OleDbConnection)cmParams.GetConnectionForSchema();
Note It's worth mentioning that it is technically possible to reference a connection
by number rather than by name (e.g., using Dts.Connections[3] rather than