Portable DTS Packages

  • 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


  • 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....-:)


  • 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

  • very informative

  • 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 Miller

  • 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.

  • 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.


  • 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

  • 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.

  • 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.



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

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




    Rogue DBA

  • 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                                                                            


    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

  • 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

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

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