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

Automating DTS Execution

By Augustin Carnu,

Automating DTS execution

We use extensively DTS technology to move and transform data in our BI Application; we had to find an acceptable solution to re-link the packages on Production server after moving them from the development and QA environments. This article presents a solution to the time consuming job of manually linking 50+ DTS packages after importing them on a new SQL box.

The Problem

We create parent packages that use the Execute Package task to call a child package. By default the Execute Package Task will use the PackageID (a GUID) to load and execute a package. The PackageID is assigned when you save a new package together with a VersionID and a timestamp of creation time.

Figure 1

A simple way to illustrate this is to open a package and then use 'Save As' option, giving the package a new name. Then in the Initial package create an ‘Execute Package Task’ and establish a dependency between the Master package and a Child package by linking (STG_ED) connector to the Child package (Demo Child-Task) like in Figure 1.

If you were to replace the Child package for some reason, like saving it after making changes, this would create a completely new VersionID, with a new time stamp and a new ProgramID! And this would break the linkage between packages, as the VersionID is pointing to an old VersionID with the same name. The PackageID {543E….184} in the background of Figure 2 is obsolete, and the execution will fail!

Figure 2

Our Solution

We removed the dependency on the PackageID/VersionID, by blanking out the PackageID property of the ExecutePackageTask. By doing this, this will cause another property, the PackageName property, to be used. You can test this easily via the Disconnected Edit feature. This is available on the Package menu, right click the blank space of the package and select "Disconnected Edit". But for our run-time environment this is not enough.

Our solution was to add a Dynamic Properties Task to perform the NULL assignment for dependent Child packages at run-time. This task will form the basis for our process along with a blank Global Variable, which will be assigned to all PackageID properties. The execution of the child package will then use the name of the package instead of the PackageID. The steps in the process are as follows:

Figure 3

Create a Dynamic Property Task, named Blank out PropertyID in the picture 3 above. Click on Edit button; the next dialog will be like the one in Fig 4.

Figure 4

Figure 5

This brings up the Package Properties dialog. For every task in the left side ( DTSTask_DTSExecutePackageTask_1, 2, etc) edit the PackageID and Click on Set button (as in Figure 5).



Figure 6

Next, you’ll create and Assign a Global variable (or reuse an existing one). If you create a new Global variable DO NOT assign any value to it. This will be translated to a NULL value at run-time; you can reuse it repeatedly for the entire Task list.

To insure the portability between servers, please do the following:

  • make sure that the SQL Server you’re using for packaging the DTSs, is registered as a (LOCAL)(Windows NT) like in the Figure 7.
  • ensure all inner packages use the (local) SQL reference server, no hard coded server name.

Figure 7.

In a future document we’ll show how to implement a DTS packages distribution and activation on a new SQL box.

Total article views: 10291 | Views in the last 30 days: 1
 
Related Articles
FORUM

Master and child packages performance issue

Calling child packages with Execute Package tasks increases execution times

FORUM

SSIS Child Package Configuration

SSIS Child Package Configuration

FORUM

Run a child package with its own configuration file from a parent SSIS package

Execute the child package by from a parent with the means of running the child package independently...

FORUM

Child package won't run from within Parent in an execute package task

No password set on the deployed package but the child package fails

FORUM

ssis traansaction managmnet between parent & child packages

Parent & Child packages use same configuration file

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