Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using the Dynamic Properties Task

By Brian Knight,

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:

Data1Data2Data3
Data4Data5Data6

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.

Total article views: 11681 | Views in the last 30 days: 18
 
Related Articles
FORUM

Accessing Dynamic Properties task Properties in Activex Script

DTS Package/ Dynamic Properties task

FORUM

Create Variable Dynamically

Create Variable Dynamically in Stored Procedure

FORUM

Dynamic Properties @ Text Destination

Global Variable in Dynamic Properties not being picked up at File destination

FORUM

Creating Dynamic SSIS Packages

Suing an SSIS package or SSMS job to dynamically create a package.

FORUM

Dynamic SQL to create table variable

I need to create a table variable using dynamic sql

Tags
dts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones