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.