Put DTS through its paces
During the past few months, we've seen numerous newsgroup postings and received several email requests for additional coverage of Data Transformation Services (DTS) scripting techniques within the Package Designer. So let's examine several common scripting patterns that you can apply to package development, including invoking a package from within another package, accessing properties the Package Designer doesn't directly expose, and dynamically changing the source or destination data store. We'll show you how to overcome a subtle but show-stopping gotcha that Visual Basic (VB) developers need to watch for, and how to create a splitter transformation to normalize one row with many repeating columns into a table where each of the repeating columns becomes a row.
Package Within a Package
How many times have you developed a complex transformation in one package, then found that you need the same functionality in another package? In this situation, most developers duplicate the code by creating a new package and copying the original task's scripts and transformations. Although this process works, it creates two separate code bases, resulting in two points of maintenance. Instead, we propose leveraging the original package by calling it from within the second package.
You have two options for invoking a package from another package. The first option is to use an Execute Process task to directly launch DTSRun.exe. The second option is to use an ActiveX Script task to programmatically create, load, and execute the original package. To illustrate these options, we'll create a package called PackageA, which Screen 1 shows. PackageA contains an ActiveX Script task that returns a simple message box. Now let's create a second package, PackageB, and add an Execute Process task to launch PackageA. Screen 2 shows how you invoke the DTSRun executable. You pass the /S parameter to specify which server the package is stored on, the /E parameter to instruct DTSRun to use a trusted connection when connecting to SQL Server, and the /N parameter to identify which package to execute. Executing PackageB, as you see in Screen 3, produces a spawned command process in which PackageA executes and produces a message box containing PackageA's output.
An alternative to using the Execute Process task is using an ActiveX Script task to invoke a package, which gives you greater control by exposing the entire package, through the DTS object model, to customization. For this example, we'll implement the simple ActiveX Script task that Screen 4 shows to load and execute PackageA. This task uses VBScript to directly access the DTS object model. To start, the task declares a local variable, moPackage, to hold the package. Next, the Create Object function instantiates a DTS package object. Then the package's LoadFromSQLServer method loads the definition of PackageA from the SQL Server machine Marble. The task is now ready to execute PackageA. After package execution, the script reclaims the package object's memory by setting its value to nothing and sets the task's completion status to report a successful execution. This time, instead of spawning a new process, PackageA executes within PackageB's memory space and produces the expected message box, which you see in Screen 5.
Looking Under the Covers
We've noted in previous articles that the Package Designer hides many details of using the DTS object model directly. For example, the Package Designer handles the task-to-step associations that control the task execution flow. The Package Designer also hides package methods such as GetExecutionErrorInfo, properties such as StartTime and FinishTime, and details of the underlying task and connection objects. Wouldn't you like to have access to this information, or better, be able to dynamically change the information at runtime? To get access, you need to write directly to the DTS object model. In May and June 1999 ("Unleash the Power of DTS" and "Pump Up the Power of DTS"), we explored how to create packages with VB. You can reuse many of those concepts within the Package Designer.
The key to accessing this information is creating a reference to the package you want to access. To see how this process works, imagine that you've developed a package that imports a data file. Later, your project's requirements change, and now you need to process two more data files with the same format and similar data content. Rather than creating two new packages or adding two additional tasks to your existing package, you can reuse the existing package and dynamically reset the source connection to process the three files.
Let's first build a package to process one file. Listing 1 describes the schema that holds the destination data. Before creating the task, run this script to set up your database environment. This task creates a database called StagingDB and the table SourceData, which will be the destination for the transformation. We'll use the other table, VisitPhysician, in a later example. Now let's create the package.
The package, DataLoader, consists of one SQL Server connection and one bulk insert task. Screen 6 shows the SQL Server connection, StagingDB, which defines the database connection. The bulk insert task, which Screen 7 shows, contains the reference to the data file that we'll later modify. We pointed the source file to the first data set, C:\Projects\Article\SampleData01.dat. For the purpose of this example, the data's format is unimportant. (Subscribers can download the format file, SampleData.fmt, along with all the other sample code, at the link to this article at http://www.sqlmag.com.) At this point, save the package to your local SQL Server and run it once to test it.
To complete the example, a second package, DataLoaderDriver, consists of a single ActiveX Script task. The task, in Listing 2, page 48, builds on the programmatic package-within-a-package technique we discussed earlier. This time, the task goes a step further by referencing the package's Tasks collection, specifically the DTSTask_DTSBulkInsertTask_1 task, and changing its DataFile property to one of the filenames in the FileNames array. With this process, you can quickly and easily iterate through the file list and process each file without having to recode any of the original package. (If you try this example, be sure to change the pathnames of the data files and the SQL Server server name in the script to match your environment.) To extend this example, you can read the file list from a database, the Active Directory, an external file, or even the Registry. Where the list originates is irrelevant. You need only to understand how to modify the package's properties from within another package.
Another variation on this example lets a package modify itself. Here, too, you need to create a reference to the underlying package and then leverage your knowledge of the DTS object model. For example, from within a package, you access the package through the DTSGlobalVariables meta data. Consider the ActiveX Script in Listing 3, page 48. This script drives the task in a package called CreatorName. The script begins by using the package's GlobalVariables collection to gain access to the parent package. After you have access to the package, you can access or manipulate any of its attributes. In this example, the task simply uses a message box to display the package's creator.
Prev. page
[1]
2
next page 
