SQL Server DTS: Dynamically Change Source and Destination in Transform Data Task

I really wish I had more experience with DTS and SSIS (Replication, Clustering, Hardcore HA too), especially since I've been a DBA for 8 years. I bought the Wrox book "Professional SQL Server 2000 DTS" years ago hoping that one day I'd be making 50-step DTS packages but I never had the need. Today, I finished a whopping 5-step package. Creating it took a few hours more than I expected because of an issue with the Data Task not appearing to execute within a loop.

Here's the scenario:

I need to export data from an application database that resides in each of our 6 offices. The query is the same for each city and pretty much the only things that change are the filenames (ex. cityabbr-prepdata.csv) and the names of the database servers (ex. cityabbr-SQL).

At first, I created a package using the Import and Export Wizard and I was saving them to each server, then I felt silly. There had to be a better way. I knew I needed to loop through the package a few times and after searching, I found this super helpful page at SQLDTS.com: Looping, Importing and Archiving.

So I started with the base package created by the Import and Export Wizard.

Then I renamed the two connections. Since the DTS Editor doesn't directly allow renaming of the package, I had to go to the Properties of each connection, select New Connection -> [Enter New Name] -> OK -> OK.

Next, I added three new ActiveX Script Tasks. One set the global variables, one changed the data sources (source sql server, destination text file) and one created the loop. The code for each was as follows:

'********************************************************************** ' Set Globals Script '********************************************************************** Function Main() DTSGlobalVariables("strCities").Value = "SD,SF,LA,DM,IR,RS" 'String because I'm not sure if GlobalVariables can do arrays arrCity = split(DTSGlobalVariables("strCities").Value,",") ' We will use a split to turn string into array DTSGlobalVariables("looptimes").Value = ubound(arrCity) 'How many times to loop?

DTSGlobalVariables("counter").Value = 0 'Since arrays start at 0, start counter at 0

Main = DTSTaskExecResult_Success End Function

'********************************************************************** ' Set City Script '********************************************************************** Function Main() arrCity = split(DTSGlobalVariables("strCities").Value,",") i = DTSGlobalVariables("counter").Value

'Change Source SQL Server Set objSQLConn = DTSGlobalVariables.Parent.Connections("Source SQL Server") objSQLConn.DataSource = arrCity(i) & "-SQL" Set objSQLConn = Nothing

'Change Destination Text File Set objTextConn = DTSGlobalVariables.Parent.Connections("Destination Text File") objTextConn.DataSource = "C:\sqlexports" & arrCity(i) & "-prepdata.txt" Set objTextConn = Nothing

Main = DTSTaskExecResult_Success End Function

'********************************************************************** ' Create Loop (by resetting status of Set City Script) ' Check name of Set City Script by Right Clicking on Set City Script ' -> Workflow -> Workflow Properties -> Options -> Name '********************************************************************** Function Main() strNameOfTask = "DTSStep_DTSActiveScriptTask_2"

If DTSGlobalVariables("counter").Value < DTSGlobalVariables("looptimes").Value Then Set objPackage = DTSGlobalVariables.Parent objPackage.Steps(strNameOfTask).ExecutionStatus = DTSStepExecStat_Waiting End if

DTSGlobalVariables("counter").Value = DTSGlobalVariables("counter").Value + 1

Main = DTSTaskExecResult_Success End Function

It took me forever to figure out the DTS name of the "Set City" ActiveX Script. I included the instructions within the Create Loop script but feel it's worth repeating. To get the name of a DTS ActiveX script for use with DTSGlobalVariables.Parent, right click on ActiveX Script -> Workflow -> Workflow Properties -> Options -> Name. The description (what we see as the name) won't suffice.

So what problem did I spend hours trying to figure out? Well, when the package was executed, it only wrote C:\sqlexports\sd-prepdata.csv. I debugged it a bit and found that it was definintely looping, and the data sources were changing, and the Transform Data Task was executing (as confirmed by a Msgbox successfully invoked in an ActiveX script within the tasks' workflow) yet only one file was being output(ted).

After poking around for awhile, I tried checking the "Close connection on completion" box under the task's workflow option and sure enough, that's what worked.

Yay, now to treat myself to a piping hot lunch.