Making Connections Configurable and Dynamic


Connections are the most common object within a package that needs to be updated. This is because packages in development most often point to development databases. When you are ready to deploy your package to a test machine or a production machine, then you must have that connection updated. The wrong answer is to manually open the package and hard-code the new connection string because when the package is modified, you introduce the possibility of new bugs creeping in.

Another common example that requires connection strings to be dynamic is when file names change because of a change of date or because you are given a set of identical files that you need to loop over.

Within SSIS, there are many ways to accommodate dynamic connection strings. A developer can choose from one the following options:

  • Package Configurations- The connection string is stored somewhere external to the package (such as a file or database table) by leveraging SSIS Package Configurations. The connection can then be changed without touching the package. With this approach, the connection is updated once at the very start of the package execution. Setting up configurations is relatively straightforward and the details can be found in Books Online, or in Chapter 16 of the Professional SQL Server 2005 Integration Services book.

  • Property Expressions- The ConnectionString property of a package connection can be dynamically updated through the use of SSIS Property Expressions. When a property expression is implemented on a connection, then, when the connection is accessed during execution, instead of pulling the hard-coded value of the ConnectionString, the expression is run to generate the new ConnectionString. (Unlike configurations, the ConnectionString is updated when the connection is accessed, not when the package loads.) Chapter 3 of this book walks through an example where a set of Excel files is looped over with the For Each loop container and each iteration uses a Data Flow Task to pull in the Excel data, with the connection property updated through this mechanism.

  • Script Task- Package connection properties are accessible at runtime. This means that you can add a Script Task to your control flow, and within the script, perform updates to the connections when the Script Task runs. The remainder of this section focuses on accessing and updating connections in the Script Task.

Making connections configurable through the Script Tasks is extremely useful when building packages that will be run in different environments. The Script Task can also provide the flexibility of updating more than one connection, or even of building new connections through code on-the-fly.

To better understand configurable connections, the best starting point is the Microsoft.SqlServer .Dts.Runtime namespace. A Connections collection within the namespace holds the connections that are set up within the connection manager. These connections can be iterated through using an index or the actual name of the collection created. Each collection exposes a property called ConnectionString that can be configured at runtime.

To begin with a simple example, the following code (inside of a Script Task) shows how to display a connection string that is stored in a package variable (the first line of code). The second line then gets a count of how many connections are available within the connection manager, and exposes the results via a message box. Next, the code sets the ConnectionString property of the first connection within the connection manager to the value from the variable "strConnString". Note that this code uses an index or numeric value to get the connection object (the name of the connection object that was set when the connection was created can be used as well). In this simple example, the connection can be updated to run against different databases just by changing the variable that holds a connection string value.

  Public Sub Main()     Dim strCnStr As String = CStr(Dts.Variables("strConnString").Value)     MsgBox(Dts.Connections.Count.ToString())     Dts.Connections(0).ConnectionString() = strCnStr     Dts.TaskResult = Dts.Results.Success End Sub 

This next example creates a new connection from scratch, which, as you will see, is also straightforward. The code in Listing 2-2 shows how to create a SqlConnection object. To take advantage of a connection object, the code also uses a SqlCommand object and a SqlDataReader object. In this case, the SqlCommand object runs a query as a text statement. However, it can also run stored procedures. It uses the connection object that was created and opened (based on the connection string) to return a SqlDataReader, which is a forward-only representation of the data returned. The System.Data.CommandBehavior has been set to CloseConnection to ensure that the connection is closed after the SqlDataReader is iterated or looped through.

Listing 2-2: Creating a New Connection

  Public Sub Main()         Dim SqlConn As SqlClient.SqlConnection         Dim SqlCmd As SqlClient.SqlCommand         Dim SqlReader As SqlClient.SqlDataReader         Dim strCnStr As String = CStr(Dts.Variables("strConnString").Value)         Dts.Connections(0).ConnectionString() = strCnStr         MsgBox(Dts.Connections.Count.ToString())         SqlConn = New SqlClient.SqlConnection(strCnStr)         SqlConn.Open()         SqlCmd = New SqlClient.SqlCommand(" _             SELECT TOP 5 ADDRESSLINE1 FROM PERSON.ADDRESS", SqlConn)         SqlReader = _              SqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)         While (SqlReader.Read)             MsgBox(CStr(SqlReader(0)))         End While         Dts.TaskResult = Dts.Results.Success End Sub 

When using this approach, ensure that the package variable strConnString has been set to a valid connection string value (in this case, a table in the AdventureWorks database); otherwise, your package will fail!

In both of these examples (updating an existing package connection and creating a new connection), the dynamic connection strings were handled with only a few lines of code.



Expert SQL Server 2005 Integration Services
Expert SQL Server 2005 Integration Services (Programmer to Programmer)
ISBN: 0470134119
EAN: 2147483647
Year: 2004
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net