Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 5, 2003

Updating SQL Server Connection Properties from the DTS Package INI File

By DatabaseJournal.com Staff

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM