by
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.
Process:
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.
Conclusion:
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.
