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 FunctionCreate 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.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
September 9th, 2007 - 10:42
very useful…u saved my life..
September 17th, 2007 - 22:24
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.
September 27th, 2007 - 03:16
Very nice. Thanks for the post
October 10th, 2007 - 21:43
Thanks you guys! Glad I could help.
October 15th, 2007 - 19:59
You guys just saved my job and my project with this article. Talk about a needle in the hay stack. Thanks!!!!
January 23rd, 2008 - 18:29
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!
March 10th, 2008 - 05:53
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.
June 7th, 2008 - 08:02
Hi! How I will change this DTS to change between DB?? Thanks.
July 21st, 2008 - 09:03
Thank you! This is EXACTLY what I was looking for.
August 6th, 2008 - 01:37
I want to create destination table at runtime in my DTS, Can u help me in this?
August 29th, 2008 - 06:39
Thanks for this artical. This helped me make my DTS MORE dynamic.
Thanks, Michael from Dallas,Tx
September 28th, 2008 - 18:46
This helped me solve a problem I had been struggling with for 2 days …. Thanks so very much!!!
October 29th, 2008 - 08:13
Sir,
Thank you very much for the post!!
You just saved my job. I had the same problem.
Thanks and God Bless!!
November 25th, 2008 - 17:52
Muchiiiiiiiiiiiisimas gracias!!! (Thanks a lot!!!)
That was great!!
February 6th, 2009 - 11:51
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!!
February 17th, 2009 - 06:18
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!
March 2nd, 2009 - 09:35
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.
March 30th, 2009 - 20:10
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!!!
April 7th, 2009 - 17:40
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.
April 22nd, 2009 - 05:16
How can i change the Datasource from a Masterpackage?
It ignories still the change…
July 9th, 2009 - 14:36
This is awesome thanks
November 4th, 2009 - 20:59
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!
November 9th, 2009 - 22:04
Really really a helpful and explained article. Thank you very much.
November 25th, 2009 - 11:58
@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.
August 10th, 2010 - 06:25
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?