In SQL Server 7.0 Data Transformation Services (DTS), dynamically configuring packages was difficult. You had to know how to write VBScript and know the DTS object model. SQL Server 2000 to the rescue! With the added Dynamic Properties task, you can perform the same function as complex VBScript did in SQL Server 7.0. This article will show you how to dynamically configure a package with the new task.

The need to dynamically configure DTS packages is enormous. For example, if you are creating a data load package for your clients, rather than creating a package for each client, you could create one package and dynamically configure it based on the client number. You do this typically through global variables or an INI file. The Dynamic Properties task allows you to set any DTS property in the object model to a:

• Global variable
• Constant that is typically used to assign the default value of an item, such as DTSStepExecStat_Completed. This is one way of resetting a value back to the default settings after changing it in another task.
• INI File that supports a single line property value. It also supports INI file sections.
• Data file that can support multiple lines, but lacks in control. Data files are much more an “all-or-nothing” situation.
• Query that uses the first row returned. It is for that reason that it is recommended that you design your query in such a way that it only returns one row, like count, sum, or average queries.
• Environment variable from the System or User variables. These can be set in the control panel under system. Typical environment variables include COMPUTERNAME, TEMP, and PATH.

A very important addition to DTS in SQL Server 2000 is the ability to pass packages parameters that can be used by the Dynamic Properties task. This also allows you to pass parameters to other packages and make packages modular. We will cover how to do this in next weeks article.

In this example, we will dynamically point where a text file will be loaded. First create a sample tab delimited file with the following data:

Next create a table in a few of your databases (pubs and Northwind would work fine).

CREATE TABLE [dynamicpropertiestask] (
[Col001] varchar(50) NULL, 
[Col002] varchar(50) NULL, 
[Col003] varchar(50) NULL )

The bulk of the task should be a simple transformation. First create a connection to your SQL Server and specify the first database you’d like to insert your data into. I named the database connection Destination.

Next, pulling the flat file from the website, then transforming it to the SQL Server pubs database. You will need to configure the task as shown below. You will set the source to the newly created file. I named the file dynamicproperties.txt in the C:\Swynk folder. Ensure that your column delimiter is a Tab and your row delimiter is {LF}, which is a carriage return.

Now the more interesting part of the example begins. Select the Dynamic Properties task from under the Task menu in DTS Designer. Name your task and then click on the add button to assign your first dynamic property. You will then see the following screen that will list every configurable property in your package. Drill down the to the connection that we named Destination and to the OLE DB Properties node. Click on the Initial Catalog property and you’ll see the right pane display what catalog the Destination connection is currently configured for. This means that initially we’re inserting into the Northwind database.

Double-click on the value in the right pane to dynamically configure the property. You can then set the property to a number of items that we mentioned earlier in this article. For our example, let’s configure this property to a global variable.

Since we haven’t defined any global variables, you’ll need to create a new one by clicking the Create Global Variable button. Call the global variable gvCatalog and assign the value of pubs.dbo.dynamicpropertiestask to it. This means you’ll transfer the data into the pubs database to a table named dynamicpropertiestask. Once you click OK, the global variable drop-down box will be populated.

Now that you have the value assigned, you can click OK and finally the close button to save your dynamic property. With that finished, the final results of your task should look like the following

You can additionally make more properties dynamic by clicking on the Add button again. To review the property after it’s set, simply click on the edit button after highlighting the task. To guarantee that the Dynamic Properties step runs first, create an On Success precedence constraint between the Dynamic Properties task and the Bulk Insert task. You can do this by clicking on the first task and the holding the CONTROL key down while selecting the next task. After both tasks are highlighted, select On Success from the Workflow menu. The final result will look like the following screen shot.

You are now done with the package. After you execute the package, loading data into the pubs database, you can then change the global variable to point to he Northwind database (Northwind.dbo.dynamicpropertiestask table). To change the global variable, select Properties under the package menu in the Designer. After you change the global variable, you can execute the package again and the data will be bulk inserted into the Northwind database.

Although this is a simple example, it still saves you from creating two packages. You can now pass the gvCatalog parameter into the package you just created and call the package from any program or other package. Next week we’ll do just that. We’ll use the Execute Package task to pass global variables into a package and assign global variables to a remote package.