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