As a SQL Server DBA, you frequently modify Data Transformation Services (DTS) packages, either to correct errors in logic, to extend DTS package functionality or to change the server, database or object name. But rather than debug DTS packages in production environments, you should debug in QA/test environments until you are confident they are working correctly and then promote them to the production environment.
Debugging in a QA/test environment can help do the following:
Minimize the impact of testing on live production processes, particularly if testing degrades performance.
Insulate your production environment from mistakes in your code, which could delete or destroy the integrity of your data.
Provide a versioning system. So if something goes wrong, you can always revert back to the version currently used in production.
So, how should you go about debugging and promoting DTS packages? There are several approaches, which I've orderd below according to popularity. Modifying the server alias is the easiest approach. Microsoft recommends using Dynamic Properties to test DTS packages in a QA environment. Global Properties allow you to pass server and database names from the command line when you run DTS packages. And with Visual Basic's (VB) 6.0 IDE (integrated development environment), you can use the rich debugging facilities of VB. We will examine each of these options in detail.
[TABLE]
[TABLE]
The advantage to modifying your Server Alias to point to your QA or debugging environment is the ease of use; it's simple to use. However, you might make a mistake in the server name and wind up using a critical environment -- or forget to point the Server Alias back to the production environment when you are done. You have also lost versioning. For example, if you are working on a DTS package and want to revert back to a previous version of the package, your options are limited if you
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
saved your work along the way. Restoring the msdb database from yesterday's backup is an option; but this often introduces other problems.
To take advantage of aliasing, use the Client Network Utility to make your test server masquerade as your production server. Click on Start, point to Programs, then Microsoft SQL Server and click Client Network Utility. Click on the Alias tab and then Add.
[IMAGE]
Figure 1: Add Network Library Configuration
The Server Alias is the ProductionServerName, but the Server Name is the QA Server Name. In your DTS package, any reference to ProductionServerName will be redirected to QAServerName. After you have completed the debugging process all you have to do is open up Client Network Utility again, and change Server Name back to the ProductionServerName.
[TABLE]
You can actually edit your DTS package in the Visual Basic (VB) 6 IDE. The method allows you to access VB's rich debugging features to step through your package, as well as its Quick Watch features. You can modify your connection object to point to your QA environment, and step over portions of your package. The disadvantage of this approach is the tremendous amount of code you need to navigate through, and it is not compatible with the VB.Net IDE.
To use the VB 6 IDE, save your DTS package in the Package Designer as a bas file and import it into VB. To import your bas file into the VB 6.0 IDE, create a Standard Exe project, add the bas file to it by selecting Project and Add Module, then click on the Existing tab and browse to the location of your bas file. Then add a reference to Microsoft DTS Package Designer, and in the Project Properties dialog box (select Project – Project Properties from the VB IDE) set the startup object to Sub Main.
Modify oConnection.DataSource ="ServerName" and oConnection.Catalog = "DatabaseName" for your debug or test environments here. You can test the execution of your DTS packages here using the VB Run and Debug menu options.
After you have completed the debugging process, modify oConnection.DataSource and oConnection.Catalog to point back to your production server and then run the DTS package from within VB 6 IDE using the VB IDE debugger. This will publish your DTS package back to your production SQL Server. To do this, notice these lines in your bas file.
[TABLE]
Dynamic Properties are the intended method of changing the server and database where your DTS package executes. When properly executed, this is the simplest method of the four types. However, it requires an INI file – just one more thing for you to worry about – and your DTS package is no longer self contained. Let's have a quick look at how this works.
Create a text file called Properties.txt and save it to your C drive. Edit the text file so its contents look like this:
Then create a DTS package by connecting to your production SQL Server in Enterprise Manager, expanding Data Transformation Services, right clicking on the Local Packages node and selecting New Package. The Package Designer comes up to create two connections as per Figure 2 and Figure 3.
[IMAGE]
Figure 2: Connection Properties 1
[IMAGE]
Figure 3: Connection Properties 2
Then create a Data Transform task to export the authors table from the pubs database to the Northwind database. You will have to select the authors table in the drop-down box. In the Destination tab, you may have to click the Create button. The Source and Destination tabs for this task are illustrated in Figures 4 and 5 below.
[IMAGE]
Figure 4: Source tab for the Transform Data Task
[IMAGE]
Figure 5: Destination tab for the Transform Data Task
Then create a Dynamic Properties task. Drill down to the Connections node through Microsoft OLE DB Provider for SQL Server into the OLE DB Properties, set focus on Data Source and click on Value in the right pane. This is illustrated in Figure 6.
[IMAGE]
Figure 6: Dynamic Properties Task; package properties dialog; setting the connection property
Then click on the Set button. This is illustrated in Figure 7.
[IMAGE]
Figure 7: Set button
In the File section, click the browse button and navigate to the properties.txt file. Note that the extension does not have to be INI. Click the drop-down box to populate the section text box. In our INI file we denoted the section portions in square brackets. For the Key section click on the drop-down box to select QAServerName. Click OK and accept all defaults to close down the Dynamic Properties task.
Recall that we selected the local server in the connection object; well that's about to change to our QA environment! In the Package Designer locate the Dynamic Properties Task, right click on it and select Execute Step. Click on the Successfully Executed Step dialog to clear it. Then right click on your first Connection object and select Properties. Notice how the name has now changed to Server1 from local.
Repeat the process for the Database Name using the initial Catalog setting in the Package Properties dialog box.
Your Task Properties dialog box should resemble Figure 8.
[IMAGE]
Figure 8: Dynamic Properties Task Properties
To change from Production to QA Server you can either change the key used in the Dynamic Properties Task back to the ProductionServerName or you could edit the INI file (in our case properties.txt) and change the value of QAServerName to point another QA or Debug server. You can also control which database is used by modifying the Database section.
[TABLE]
Global Properties allow you to pass parameters to your DTS package as part of a job from a batch file or the command line. These parameters could be the name of your server and/or database name. The advantage here is that your DTS package is self contained – there is nothing to document for future DBAs who take over your task. The disadvantage is that you will find yourself overwhelmed as the parameters you have to pass from the command line grow. Four parameters are easy to deal with, but five or more quickly become unwieldy, especially if you have to modify them frequently. Let's look at an example.
Return to the Dynamic Properties task. Right click on it and select Properties, click on Edit and give focus to Value. You should see the same dialog as we saw in Figure 6. Click the set button to launch the Add/Edit Assignment dialog box, which we saw in Figure 7. This time, instead of selecting an INI file as our Source, select the drop-down box and choose Global Variable. This will launch the Add/Edit Assignment dialog box as illustrated in Figure 9.
[IMAGE]
Figure 9: Add/Edit Assignment dialog box
Click on the Create Global Variables button. The Global Variables dialog will be launched as illustrated in Figure 10.
[IMAGE]
Figure 10: Global Variables dialog box
Create two variables: one called ServerName and the other called DatabaseName. Assign defaults to these variables. Click OK. You will return to the Add/Edit Assignment dialog box, but this time it will be populated with the defaults for your Global Variables. Repeat for the database name. Your Dynamic Task Properties dialog box should resemble Figure 11
[IMAGE]
Figure 11: Populated Dynamic Properties Task Properties dialog box
Click OK and accept all defaults. Then, from the menu, click Package and select Properties. Click the Global Variables tab and enter the values you wish to use for this execution, otherwise the defaults will be used. This is illustrated in Figure 12.
[IMAGE]
Figure 12: Entering Global Property values in package designer
To pass the values of the Global Properties from the job schedule or command name use the /A parameter. Here is an example:
Summary
This tip reviewed various methods for debugging and promoting DTS packages. To promote a DTS package, all you need to do is save the DTS package to the production server. There are several options to use when you want to debug stored procedures. You can masquerade the production server name by creating an alias; export your package to a bas file and use the VB 6.0 IDE when rich debugging is called for; use the Dynamic Properties task with an INI file to configure a large number of properties at run time; or use Global Properties when working with a small number of properties or when you want your DTS packages self contained.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
