Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

More Portable DTS Packages

By Tito David,

MAKING DTS PACKAGES AND SQL JOBS PORTABLE

The popularity of DTS has continuously grown but it had and still has issues with portability. SQL 2000 offers a lot of alternatives to make DTS packages portable. I have tried the INI file approach and found that an INI file can only hold a limited amount of variables. The 'Query' Method, however, looks more promising.

This method requires a SQL Table that becomes a repository of connection variables. It can be as simple as a 2 column table, a common keyword and the actual substitution value. The table has to be in each Server Instance where the actual values can vary or be changed without editing the DTS Package; which is the strength of this method. Fig1 shows a snapshot of the table in the DEV instance.


Figure 1

To illustrate this method, I created a sample package that reads a Source table and writes to an Excel spreadsheet as shown in Fig2. On a typical development, you start out with defining the transformations after which you add the Dynamic Properties to make the Package portable.


Figure 2

To achieve portability, I will need to define the connection properties to Source and Target by looking them up from the GlobalVars Table. The challenge is that the connection to the Lookup Table needs to be predefined and this is overcome through another Dynamic Properties (Link2GlobalVars) task as shown in Fig3 .


Figure 3

Fig3 shows the need to make a local connection to the lookup table, GlobalVars, at execution time. This is done with the SELECT SERVERPROPERTY('SERVERNAME') query as shown in Fig4.


Figure 4

From here on, the next Dynamic Properties (Source2TargetConn) task defines the connection to Source and Target as shown in Fig5 and Fig6. At execution time, the connections can vary depending on the values present at the local Lookup Table, GlobalVars.


Figure 5


Figure 6

Deploying the DTS Package then becomes a breeze, with a right click on the white space and saving it to any Target Server. To complete the solution, the SQLAgent job that executes the package can likewise be made portable as shown in Fig7.


Figure 7

The dot notation on the DTSRUN command designates that it is to run on the local server. The SQL Job can be scripted and installed in any Target Server with little or no modifications.

Total article views: 9151 | Views in the last 30 days: 2
 
Related Articles
ARTICLE

Portable DTS Packages

Have you ever faced the situation where you have needed to move a DTS package you needed to move a D...

ARTICLE

Portable DTS Packages

Moving DTS Packages from one SQL Server 2000 server to another can be a real hassle. There are any n...

FORUM

What is the best way to make SSIS execute package task connections portable ?

What is the best way to make SSIS execute package task connections portable ?

ARTICLE

Yet another way to include portability on DTS packages

An proposal about using simple server name resolution and INI files to make a DTS package portable.

ARTICLE

Data Portability

Steve Jones comments on the new data portability options from Facebook.

Tags
dts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones