Updating SQL Server Connection Properties from the DTS Package INI File

DTS 에서 동적속성작업의 설정을 통해서 config.ini 만 변경하면 한번에 connection, source file path, server name, table name, userid 등등 설정된 값을 바꿀수 있는 방법입니다...

아래 Step별로 쉽게 따라 하실수 있습니다....

한꺼번에 변경작업이 있을때 하나하나 일일이 변경하지 않고 손쉽게 바꿀수 있어서..

활용하면 좋을거 같습니다..

--------------------------------------------------------------------------------------------------------------------------------

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.

 

출처 : http://www.databasejournal.com/features/mssql/article.php/3073161