Updating SQL Server Connection Properties from the DTS Package INI File
September 5, 2003by MAK [Muthusamy Anantha Kumar]
In a typical development, QA or production environment, it can be very hectic to change all of the connection parameters, such as source file path, destination server name, table name, userID, password etc. in the DTS package when we move the packages from one environment to another environment. This article will step you through the use of the INI file with DTS package and how to update the connection properties from the INI file during run time.
Step1: Create a table BCPTEXT in the pubs database on a server.
Create table BCPText (Id int, Name Char(20))
Step 2: Create a text file such as the one below and save it as c:\winnt\BCPText.txt. Copy this file to C:\
ID Name 1 A 2 B 3 C 4 D 5 E 6 F 7 G 8 H 9 I 10 J
Step 3: Create a transformation between the source file and the destination server
Step 4: Map columns between source and destination and test the transformation by running the DTS package.
So far, it is a normal DTS package, which transfers data from the hard coded source file path to a hard coded destination server.
Step 5: Create the INI file with the following parameters and save the file as C:\Config.ini
[Source Filepath] value=C:\BCPText.txt [Destination Server] value=WINSQL2K [Destination Database] value=Pubs [Destination Table] value=pubs.dbo.BcpText
Step 6: Add Dynamic Properties Task and name it "Read Config file"
Step 7: Process of mapping INI file parameters to the DTS package task properties.
Open 'Dynamic Properties Task' [Read config File]
Click Add, and then click on the Source connection [Text File Source] and double click on the DataSource.
Now Select the INI file that you created in C:\ drive and select the parameters as shown in the figure.
This will map the value of the Source text file path to the INI file's [Source Filepath].
Click 'Add' in the Dynamic Properties and select the database connection. Double click on the 'DataSource' and map the server name from the INI file.
In order to map the database name you have to select the 'Catalog' in the server connection. Double click on the Catalog and map the database name from the INI file.
For assigning the table name from INI file, you have to select the transformation and double click on 'Destination Object Name' property. Then map the table name from the INI file.
All these will map the parameter values specified in the INI file to the DTS package connections. Now connect the 'Dynamic Properties Task' [Read Config file] to the transformation by adding a Success Workflow.
When you run the DTS package, first the Dynamic Properties Task will assign all of the values from the INI file to the corresponding Source file path and database connection properties. Then the transformation occurs.
By using the INI file and Dynamic Properties Task in the DTS package, it is extremely simple to move the DTS package from one environment to another environment. All we have to do is change the parameters in the INI file that will assign the value to all of the connections in the DTS package at run time.