Creating a Configurable DTS Package

Want to leave a question, comment, or some criticism? Click here!

0. Update

Joe Priestley, the fellow that runs JSWare, tracked me down and informed me that in fact, it was I that was using his library incorrectly due to a misunderstanding of the INI format. This is the email that I received from him:

Charles,

I came across your webpage: [this page] through reading my web logs. I just wanted to say I appreciate that you provided a link to my site for the INI VBScript, but you may want to look again at the "parsing bugs" issue.

I'm not clear about exactly how you're using INIs, so forgive me if I'm mistaken here. But as far as I could tell, the bug you referred to seems to be connected with my version not using quotation marks around the INI values. If you look at standard INI files you'll see that quotes are not part of INI structure.

Joe Priestley

Indeed, I am the one at fault on this one as the INI file specification follows along the point that Joe makes (although I think it's easier to use without the quotes in place). The code has not been updated to reflect this, however, do keep this in mind when using my code and reusing Joe's library with your future projects.

1. Motivation

Having worked on a few DTS packages during my career, one problem that I've encountered is configuring the DTS package to work in different environments.

A common scenario for DTS is the import of data from multiple text files from somewhere on the file system. In general, when working on DTS packages, it can be a hassle to modify multiple settings in the DTS package itself each time you have to ship a production version, as is the case if you have different file names or locations in production, staging, and/or development. The ideal solution would minimize or eliminate editing of the package itself when moving from one environment to another.

To work around this, we can use some of the cool features of DTS and a little bit of VBScript to create a configurable DTS package that can be configured entirely (well, almost entirely if you use environment variables to set the configuration file path) independent of the package itself. This article covers one such workaround (I'm sure there are plenty of others as well).

While SSIS is coming soon, I feel that there are enough companies out there that will stick to DTS for quite a while that this info is worth sharing with those who still have to work with DTS in the coming years.

2. Objective

The objective of this mini-workshop is to create a DTS package that can read a configuration file (in this case, an INI file, but it could just as well be an XML file) and load the settings in the configuration file dynamically. Thus, when moving a package from a development to a staging or production environment, the package can be shipped with little or no change. All changes would only need to be made in an external configuration file.

3. Auxiliary Files/Prerequisites

To try this out for yourself, you will need the following:

I recommend creating a working folder on your local machine and save all the files to the directory.

4. Creating the Package

The first task is to create a new DTS package in SQL Server. Navigate to your database in Enterprise Manager and right click on the "Data Transformation Services" folder icon and select "New Package" from the submenu.

Once the package editor loads, save the package by clicking on "Package" and then "Save". For the purposes of this workshop, save the package as a "Structured Storage File". Save the package to your local file system where it's easy to find. For the rest of this workshop, my base path will be E:\Project Files\DTSSample\.

Once the package is created, right click on the workspace and select "Package Properties" from the context menu. In the "Logging" tab, check the "Write completion status to event log" checkbox and click "OK".

5. INI Loading Script

The INI file format is essentially a key-value pair broken up by section names. We will need an INI file parser to load the values from the file into DTS. Fortunately, jsware provides just a script. Unfortunately, the version available online contained a few small parsing bugs, which are fixed in the version you can download above.

The first step is to create a new "ActiveX Script Task" in the package. Click on the icon in the task palette and drag it to the workspace.

The script editing window should open, allowing you to enter the script to run for this task. Copy the INI loading script and paste the contents into the editing window and enter "Load INI Script Task" into the description field at the top.

The script is divided into two parts: the INI loading/parsing library and the main script body, which is executed by the DTS package. The key portion of the script is the following snippet:

Global variable initialization script.
'// RETRIEVE AND SET VALUES
If Not errorOccurred Then
    '// INITIALIZE THE REGULAR EXPRESSION 
    '// TO MATCH FOR INI-LINKED KEYS
    Dim reIniKey : Set reIniKey = New RegExp
    reIniKey.Pattern = "\w+\.\w+"
    reIniKey.IgnoreCase = True
    reIniKey.Global = true

    '// THE GLOBAL VARIABLE
    Dim var
    '// THE VALUE THAT IS RETURNED FROM THE CALL TO GetINIValue()
    Dim value
    '// THE SECTION NAME IN THE INI FILE (PART OF THE var BEFORE THE 
    '// ".")
    Dim section
    '// THE KEY NAME IN THE INI FILE (PART OF THE var AFTER THE ".")
    Dim key

    '// INITIAL LOOP TO SET GLOBAL VARIABLE VALUES
    For Each var In DTSGlobalVariables
        If reIniKey.Test(var.Name) Then
            section = Mid(var.Name, 1, InStr(var.Name, ".") - 1)
            key     = Mid(var.Name, InStr(var.Name, ".") + 1)

            If Ini.GetINIValue(section, key, value) = 0 Then
                If section = "[DATA]" Then
                    var.Value = dataFilePath & value
                Else
                    var.Value = value
                End If
            Else
                errorOccurred = True
                Exit For
            End If
        End If
    Next
End If

Using regular expressions, we go through each of the global variables (defined in the next section) and see if the variable has a matching section-key pair in the INI file. Looking at the regular expression, we see that the match we're looking for is <some word>.<some word>. Once we determine that the name of the global variable is of the format that we're looking for, we pull the first word as the section name and the second word as the key name.

For example, in our case, the global variable DATA.DataFileName will map to the INI key DataFileName in the section [DATA]. The global variable DTS.LogFilePath is mapped to the INI key LogFilePath in the section [DTS].

The last step is to set the value of the global variable (var.Value = value) to the value we obtain by looking it up in the INI file. This snippet is written so that if any global variable which matches our pattern does not have a match in the INI file, an error condition will be set to true and the package will fail. Alternatively, you can exclude the Else condition and allow for global variables which match our pattern, but are not defined in the INI file.

In this case, whenever we encounter a global variable which is in the [DATA] section, we also want to prepend the base path (as defined by the DataFilePath key). This is useful if you need to configure multiple files that belong to the same directory (so you don't have to modify the setting on each of the files independently).

6. Creating Global Variables

The next step is to create the global variables. Note that this script is dependent on a single global variable which must be set independently for each environment (unfortunately, I haven't figured out a way to have the package "find" the location of the configuration file). The INI_FILE_PATH global variable is the only setting we have to change in the package when moving the package from one environment to another.

In addition, we will add the following keys using the naming convention discussed above:

To access the global variable configuration, right click on the workspace and select "Package Properties". In the "Global Variables" tab, enter each global variable with a String data type and blank value.

Don't forget to add the INI_FILE_PATH global variable and set it's value to the location of the .ini file on your machine. For me this is E:\Project Files\DTSSample\configuration.ini.

7. Create Additional Objects

After loading the values into the global variables from the INI file, we need to set the properties on the objects in our packages. Before we can do that, we need to create some of the objects first.

Create the following objects on the workspace:

When you create the "Text File" connection, you will be prompted to enter in the location of the file on the file system. For now, point it to the data.txt file. Set the format to "Delimited", the "Text Qualifier" to "<none>" and click "Next" to preview the data.

When you create the database connection, you will be prompted for the settings as well. Select your server instance from the dropdown and check the "Use Windows Authentication" checkbox. Make sure that you select the "pubs" database.

When completed, your workspace should look like so:

9. Set Object Property Values

Once all of our objects are in the workspace, we need to configure the dynamic properties task to set the properties of our objects.

Right click on the dynamic properties task and select "Properties" from the context menu. This brings up a list of all properties that we are setting dynamically. Click the "Add" button to add our first property.

In the "Package Properties" window, expand the "Connections" node and find the data file we added in the previous step. Select the "DataSource" property and click "Set". You can see that we can set the value of this property using various data inputs. In fact, we could have used the INI file directly. The problem with using the INI file directly is that you would have to modify this setting each time you changed the location of the INI file (for example, when moving from development to production). In our case, since we should have loaded the value into a global variable, we'll simply select "Global Variable" from the dropdown.

After selecting "Global Variable" from the dropdown, choose DATA.DataFileName from the "Variable" dropdown.

Next, we need to set the values for the database connection. The relevant properties that we need to set are defined in th INI file under the [DB] section. Set each of the properties to each of the matching global variables.

Finally, the last property that we need to set is the location of the log file for the DTS package. Select the package in the tree and look for the "LogFileName" property. Use the DTS.LogFilePath global variable for this property.

When you're done, it should look like the following:

10. Importing the Data

The next step is to add the transform from the text file to the database. Hold the Ctrl key and click the data file connection and the database connection. Right click on the database connection and select "Transform Data Task" from the context menu.

Right click on the transform and select "Properties" from the context menu.

In the "Source" tab, enter "Data File Import" into the "Description" textbox. Next, in the "Destination" tab, select [pubs].[dbo].[authors] from the "Table name" drop down. Finally, click the "Transformations" tab and leave all of the default transformations intact. Click "Ok" to close the window.

11. Setting Up Workflow

After the transform task is in place, we can set up the workflow. For the purposes of this workshop, we will only consider the "On Success" path to keep it simple. We want the tasks to complete in the following order:

  1. Load the INI file and set global variables.
  2. Use the dynamic properties task to set object properties.
  3. Use a data transform task to move the data from the data file to the database.
  4. Send out an email.

To set the workflow for each object, right click on the destination step and select "Workflow"->"Workflow Properties" from the context menu.

Click "New" and select the "Source Step" and leave the "Precedence" as "Success".

The end result should look like so:

12. Sending a Confirmation Email

We still haven't filled in the confirmation email ActiveX script task yet. Copy the contents of the confirmation email script and paste it into the script editor window for the send email task. The script uses CDO to send an HTML email message to a recipient configured in the INI file (see the .vbs file for detailed comments).

14. Test the Package

At this point, we should be able to test the package. Hit the "Play" button to execute the package. Hopefully, you won't get any errors :)

If your package execution succeeded, then use the following SQL script to check that you've indeed added the new authors:

If it failed, then feel free to email me and I'll try and help you troubleshoot.

SQL statements to confirm results
USE pubs
SELECT * FROM AUTHORS

The point of this exercise, however, isn't to just make the DTS package import some data, but to also make it configurable independent of the package itself. To check that the INI script is doing it's job, create a directory called "Data" under the (for me) "DTSSample" directory. Move the data.txt file into the new subdirectory and modify the DataFilePath key in the INI file.

Delete the previous entries using the following SQL script:

SQL statements to delete previously inserted data.
USE pubs

DELETE FROM AUTHORS
WHERE au_id IN ('111-22-3333','222-33-4444','333-44-5555')

Run the package again and you should see that the data is again properly inserted into the database. Note that after you run the package once, the global variables will retain the values of the run when you open the package again. If you right click on the workspace and check the global variables, you'll see that, indeed, all of the configured values are now populated (as well as the properties on all of the objects). One byproduct of this "feature" is that if you move this to a different environment and a user opens up the package, the package cannot be saved if the database connection doesn't exist (it'll barf at you). The only workaround is to manually update the connection info in the global variables (remember, they should be cached from the last run) or change the object properties. If you are using an environment variable to set the INI_FILE_PATH global variable, then this isn't an issue since you can just do a test run and load new values for the database connection.

13. Wrap Up

So now we have a mechanism (using an INI file, DTS global variables, and some VBScript) to create an easily configurable DTS package that can be configured almost without editing the package itself. The only outstanding problem is that we still have to rely on one setting which may change from one environment to another: the INI_FILE_PATH global variable. There are some options available including using an environment variable and a dynamic property task to set this.

But even with this caveat, we've greatly simplified the process of moving DTS packages from one environment to another by pulling out the configuration data into an INI file independent of the DTS package.

My hope is that someone out there will find some use out of this :)

If you have questions or comments, please feel free to email me or leave a message in the thread.

14. Additional Resources

Other stuff to check out: