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

'**********************************************************************
'  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

'**********************************************************************
'  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 (back to City Script)

'**********************************************************************
'  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.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SQL Server
26 comments on “SQL Server DTS: Dynamically Change Source and Destination in Transform Data Task
  1. Pradesh says:

    very useful…u saved my life..

  2. Ignasi says:

    Hello, I’m Ignasi from Barcelona.

    This post was very useful for me.
    I only want to congratulate you for this post.

    I had this problem or necessity. I looked for in Internet but I didn’t find a solution until I visited your blog.

    Thank you and congratulation from Barcelona.

  3. Phil B says:

    Very nice. Thanks for the post

  4. Chrissy says:

    Thanks you guys! Glad I could help.

  5. Eric says:

    You guys just saved my job and my project with this article. Talk about a needle in the hay stack. Thanks!!!!

  6. Phil W says:

    I am quite new to DTS and I can see some light from your example.
    But my case is a bit different. I am reading a table which has 2 columns, customer # and Customer Data. I would like to separate each customer’s data and write it to a individual text file (at customer# break.)
    For Example:

    Cust# Data
    11111 aaaaaaa
    11111 bbbbbbb
    22222 aaaaaaa
    22222 bbbbbbb

    How can I create 2 files (11111.txt and 22222.txt)?
    Anyone can help? Deeply appreciated!

  7. Rajeshwaran says:

    I follow the same procedure what you have mentioned above, but I getting a Message Box stating “Step Ordinal ‘0’(base 1) is out of range”. Can you Please advice me on this.

  8. Art Cordova says:

    Hi! How I will change this DTS to change between DB?? Thanks.

  9. Dan says:

    Thank you! This is EXACTLY what I was looking for.

  10. ram says:

    I want to create destination table at runtime in my DTS, Can u help me in this?

  11. Michael Vick says:

    Thanks for this artical. This helped me make my DTS MORE dynamic.

    Thanks, Michael from Dallas,Tx

  12. Chris Combs says:

    This helped me solve a problem I had been struggling with for 2 days …. Thanks so very much!!!

  13. Awal says:

    Sir,
    Thank you very much for the post!!
    You just saved my job. I had the same problem.
    Thanks and God Bless!!

  14. desvelado says:

    Muchiiiiiiiiiiiisimas gracias!!! (Thanks a lot!!!)

    That was great!!

  15. J C says:

    Perfect!! Been struggling with this for hours trying to figure out why it kept going to the same server after I changed the datasource.

    Thanks!!

  16. Giles says:

    Oh my god – I have had this one come up intermittently for years and never found the solution. Why have I not come across this before?

    Thank you so very, very much, this will make my life so much easier!

  17. Scott says:

    Thank you for the article. I just spent an hour trying to rename my connections after migrating my DTS and your article ended my frustration.

  18. Sushil says:

    Thank you so much for this post. This is such a useful post. I am so lucky to find your posting. I could be wandering for days without this!!!

  19. Adrian says:

    Thank you.
    I had the same problem.
    I looked for in Internet four hours until I find this site.
    Thank you very much, from Colombia.

  20. Patrick says:

    How can i change the Datasource from a Masterpackage?
    It ignories still the change…

  21. Rick says:

    This is awesome thanks

  22. Trevor says:

    Well done! Really, really nice work; it helped me out a lot. I have data in one file going to what will be 50 different databases, based on a field in each line. Creating connections on the fly and looping the package is WAY better than maintaining 50 different packages.

    A point of interest: I couldn’t reference the steps in my package by name like this:
    objPackage.Steps(DTSStep_DTSDataDrivenQueryTask_1).ExecutionStatus = …

    It gave the error “Step ordinal ‘0’ (base 1) is out of range.”

    But, when I instead used this it was fine:
    objPackage.Steps(1).ExecutionStatus = …

    Everywhere I looked it’s written the first way, but that just didn’t go for me. Weird.

    Much respect, from this Acadian!

  23. Really really a helpful and explained article. Thank you very much.

  24. Jesse says:

    @Trevor
    I think you are missing the quotes around the value of steps. Without the quotes, DTSStep_DTSDataDrivenQueryTask_1 is a variable and would initialized to zero. With the quotes, “DTSStep_DTSDataDrivenQueryTask_1”, is a text string and will reference the step.

    Hope that helps.

  25. PaulTinNZ says:

    This sounds so close to what I want to do but not quite there, I think.
    I have +/- 230 DTS packages that were created by application admins. Each package can have many different connections.
    I need to move these packages to a new 2005 server but change the server name details in each of the connection objects – at this stage we don’t want to migrate to SSIS; this will happen later.
    How do I programatically scan and edit the packages and change the connection strings?

  26. sachin says:

    Hi,____I need to split the customer fullname in dts to destination columns called firstname and lastname column respectively. how do i write the split function in sql server dts?

Leave a Reply

Your email address will not be published. Required fields are marked *

*