Portable DTS Packages

Question

Portable DTS Packages

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

srini_kris SSCommitted Points: 1738 More actions · Answer 1

Great article as this is something i have a need for. Since i am copying tables from server/db A to server/db B, is there a way to dynamically select all tables that begin with the letter 't' for example.In other words, i want to copy all tables and the list of tables keeps getting increased because of constant development. Any ideas will be appreciated

-sk

naushada Newbie Points: 7 More actions · Answer 2

Vey nice, I was really looking for something and was thinking that SQL server must be having this sort of functionality. Lack of time couldn't give time to explore the things.

This technique will be definitely helpful to me in future....-:)

-Naushad

tcruse Old Hand Points: 318 More actions · Answer 3

Good article, for PB users the pipeline object is an alternate approach, especially for the person that wants to dynamiclly get the table names based on some query, also DTS is not very forgiving of data conversion errors, a nice followon article about handling conversion errors and overflow/underflow during DTS copy from .csv file would be helpful

dwebb SSC Veteran Points: 277 More actions · Answer 4

dwebb

SSC Veteran

Points: 277

May 10, 2004 at 7:02 am

#505780

very informative

Undebtedly SSCrazy Points: 2938 More actions · Answer 5

Good article.

We have hundreds of databases distributed all over the world. Anyone tried using DTS to update the schema of distributed databases like these? Is dtsrun.exe distributable?

Steve

Steve Miller

Bruce Gilpin Mr or Mrs. 500 Points: 558 More actions · Answer 6

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 SSC Enthusiast Points: 141 More actions · Answer 7

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 SSC Enthusiast Points: 170 More actions · Answer 8

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 SSCarpal Tunnel Points: 4368 More actions · Answer 9

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 Old Hand Points: 397 More actions · Answer 10

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 SSCrazy Points: 2813 More actions · Answer 11

Excellent, thanks. No more resetting of transformations, YES!!

Alex-217289 Ten Centuries Points: 1085 More actions · Answer 12

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 SSC Enthusiast Points: 170 More actions · Answer 13

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

Best Regards,

Mark Tierney

jhasbufuidh Ten Centuries Points: 1002 More actions · Answer 14

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=99&messageid=184282

BI Guy

nigel.knowles Grasshopper Points: 11 More actions · Answer 15

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