Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
How to add a Dynamic Properties task to a package and manipulate it using VB.
By Allan Mitchell
Version 2000
Level Advanced

With the introduction of SQL Server 2000 came the Dynamic Properties task. This allows you to read connection settings, table names and just about everything else for your DTS package from an INI file, a Global Variable, an Environment variable, a Query, a Constant or a Data File. Here is an example of how to drop one on onto a package and start making some assignments. The package itself is very simple and consists of two SQl Server connections and a Data Pump between them. The example is built in VB6 and you will need to make references to the DTS Package and DTS Custom Tasks object libraries.

Here is what the INI file looks like

[Database]

Name=Northwind

[Table]

Name=Suppliers



Dim pkg As New DTS.Package
Dim tsk As DTS.Task
Dim cus As DTSCustTasks.DynamicPropertiesTask
Dim assi As DTSCustTasks.DynamicPropertiesTaskAssignment
Dim oStp As DTS.Step2


'Load up the package

pkg.LoadFromSQLServer "AM2", , , DTSSQLStgFlag_UseTrustedConnection, , , , "DP"


'Add a new Dynamic Properties Task

Set tsk = pkg.Tasks.New("DTSDynamicPropertiesTask")

tsk.Name = "DTSTask_DTSDynamicPropertiesTask_1"
Set cus = tsk.CustomTask
cus.Name = "DTSTask_DTSDynamicPropertiesTask_1"
cus.Description = "Dynamic movement"



'You must have a step object associated
'with your task otherwise you will be able to see the task in
'disconnected edit but not in the GUI itself

Set oStp = pkg.Steps.New

        oStp.Name = "DTSStep_DTSDynamicPropertiesTask_1"
        oStp.Description = "Dynamic Properties Task: Change Some defs"
        oStp.ExecutionStatus = 1
        oStp.TaskName = "DTSTask_DTSDynamicPropertiesTask_1"


'Add the step to the package's steps collection


pkg.Steps.Add oStp
Set oStp = Nothing

'***
'Add a new assignment
'***

'When you ask to make something Dynamic in a Dynamic Proerties task
'You create a DynamicPropertiesTaskAssignment.  Here we create two.



'1  This looks for the section labelled Database
    it then assigns the value of the key to the Database Name of
    the SQl Server.



Set assi = cus.Assignments.New


'A Source type of 0 indicates an INI file.  The DestinationPropertyID
'Can be gleaned by walking the tree in a Dynamic Properties task and putting a semi 
'colon before each branch


assi.SourceType = 0
assi.SourceIniFileFileName = "f:\files\setup.ini"
assi.SourceIniFileKey = "Name"
assi.SourceIniFileSection = "Database"
assi.DestinationPropertyID = "'Connections';'Microsoft OLE DB Provider for SQL Server';" & _
         "'OLEDBProperties';'Initial Catalog';'Properties';'Value'"


'Add our new DynamicPropertiesTaskAssignment to the Assignments collection


cus.Assignments.Add assi
Set assi = Nothing


'2  This looks for the section labelled Table
    it then assigns the value of the key to the Source object
    table Name of the Data Pump.



Set assi = cus.Assignments.New

assi.SourceType = 0
assi.SourceIniFileFileName = "f:\files\setup.ini"
assi.SourceIniFileKey = "Name"
assi.SourceIniFileSection = "Table"
assi.DestinationPropertyID = "'Tasks';'DTSTask_DTSDataPumpTask_1';" & _
                            "'Properties';'SourceObjectName'"

'Again we add our new DynamicPropertiesTaskAssignment to the Assignments collection


cus.Assignments.Add assi

Set assi = Nothing


'Add the task to the package's tasks collection


pkg.Tasks.Add tsk

Set cus = Nothing
Set tsk = Nothing

'Save the package back


pkg.SaveToSQLServer "AM2", , , DTSSQLStgFlag_UseTrustedConnection



pkg.UnInitialize

Set pkg = Nothing