SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Portable DTS Packages


Portable DTS Packages

Author
Message
Bruce Gilpin
Bruce Gilpin
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 11
This article came along at an opportune time as I was working on a DTS package that will be moved to production. My only problem was with the diagram that showed the dynamic properties box directly connected to a connection object. My version of DTS would not allow this. I got an "Invalid precedence" error message. I was able to make it work when I connected the task to the the first "Execute SQL" task. I belive this has the same affect as the steps outlined in the article.
Phu Truong
Phu Truong
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 212
Great article!

Very easy to follow. I was looking for something like this several months ago and could not find it on the web. I end up writing a VB app to run it. Now I am going to change my DTS.

Thanks
Mark Tierney
Mark Tierney
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 208

Good Article. I wish there was something like it when I first started doing portable DTS work.

Chalk me up as another fan of UDL and INI files.

To add to andyj93's comments, you can write a failirly simple vbscript to chop out the 1st and 2nd parts of the three part name and stick it into an ActiveX script in your package (runs early). That way you do not need to do the editting.




Best Regards,

Mark Tierney

Peter Kryszak
Peter Kryszak
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1756 Visits: 3
This is exactly what we do where I work. For those that don't like the idea of running DTS packages from the command line, try scheduling a DTS package as a job. You'll find that the job itself uses DTSRun.exe, so there isn't much difference.

We wrap a substantial but not complicated DOS command scripts around the DTSRun call too. We get a lot of flexibility and our staff who don't know SQL can make script adjustments and updates without much problem.



LoveSQL
LoveSQL
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 156

Hey andyj93

How did you get rid of the three part naming in the tranformation?

I have the source from ODBC AS400 and destination is the SQL server. I am able to set the global varible for the connections but not the transformation. Any inmput is appreciated.

THanks.




rb
einman33
einman33
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1039 Visits: 512
Excellent, thanks. No more resetting of transformations, YES!!
Alex-217289
Alex-217289
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 3

Once again, the submission is SPOT ON. Good job and thanks for the very well written and easy to understand style!




Cheers,

Alex


Rogue DBA
Mark Tierney
Mark Tierney
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 208

Hi BankaR,

I added this code in an ActiveX script at the top of my package:

For i = 1 to oPackage.Tasks.Count

' Verify that this task is a transformation task by interrogating for default name

If Left(oPackage.Tasks(i).Name, 19) = "DTSTask_DTSDataPump" Then
PumpID = i
set oTrans = oPackage.Tasks(PumpID)

' Get the Destination tablename and find the 2nd qualifier location

oTask = oTrans.CustomTask.DestinationObjectName
tst = InStr(1 ,oTask,"dbo")
tst2 = tst - 2

' Use calculated starting point to strip off the table creator and name
' from the server name

tempname = Right(oTask,Len(oTask) - tst2)

' Apply stripped name back to destination name

oTrans.CustomTask.DestinationObjectName = tempname

set oTrans = nothing

End If
Next

I use this loop to also change other aspects of the Tasks (just add new code to loop) when I have many of them in a single package. It allows you to avoid opening each Task.




Best Regards,

Mark Tierney

Philippe Cand
Philippe Cand
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 294
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=99&messageid=184282

BI Guy
nigel.knowles
nigel.knowles
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Very useful article.

Initially I had problems with saving the dts package as the article quotes

dtsrun /Fmydtspkg /Nmydtspkg

but /F is actually the full filename e.g c:\dtspackages\mypkg.dts
and /N is the name of the package e.g. mypkg

I also initially had problems realising that 'structured storage' simply means a disk file and that is what we are refering to.

Note that your structured storage holds a new version of the package each time it is saved so you need to delete the file before each save or dtsrun will tell you there are multiple packages to choose from
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search