Automating the recreatation of a DTS Routine from Visual Basic

  • I've been looking into this for 2 days now, and no help.  Can it be done?  I've saved the DTS as a VB file, easy, but do not know how to get it back into DTS once I've edited it...  thanx, JC

  • First of all, how do you run the VB code?  I usually use MS Access.  I create a module, then I paste the code in.  You may need to fix some issues in the head.  Also, set a reference to the Microsoft DTS Package library.

    Next at the top change the code to put the name of the package:

        goPackage.Name = "New Package"

    Look for code like:

    '---------------------------------------------------------------------------

    ' Save or execute package

    '---------------------------------------------------------------------------

    'goPackage.SaveToSQLServer "(local)", "sa", ""

    goPackage.Execute

    tracePackageError goPackage

    goPackage.UnInitialize

    'to save a package instead of executing it, comment out the executing package lines above and uncomment the saving package line

    Set goPackage = Nothing

    Comment out the goPackage.Execute line.  Uncomment the previous line (SaveToSQLServer).

    goPackage.SaveToSQLServer "MyServer", "sa", ""

    You can use the following flag in the fourth parameter for trusted connection in your save:

    DTSSQLStgFlag_UseTrustedConnection

     

    Run the code and it should save (unless there are problems with the VB code).

    Russel Loski, MCSE Business Intelligence, Data Platform

  • Thanx for the reply,  REALLY appreciate it.  When I set this up and execute it, will it save the VB code back to SQL Server as a DTS routine?  I do not want it to actually execute the DTS, only save it back, as DTS, once I modify the VB code.  than again...  JC

  • You absolutely have to comment out or delete the following line:

    goPackage.Execute

    This is what executes the package and you don't want this to apply.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • One more...  is this how the line should look for using Trusted SQL Server?  Thanx again...

    goPackage.SaveToSQLServer "(local)", "sa", "", DTSSQLStgFlag_UseTrustedConnection

  • goPackage.SaveToSQLServer "(local)",,, DTSSQLStgFlag_UseTrustedConnection

    Russel Loski, MCSE Business Intelligence, Data Platform

  • I received an error when saving it back to DTS, but the routine saved there (in DSTS) and it worked when I executed.  So, I'm good to go!!  THANKS MUCH for your help. 

    P.S.  Please let me know how I can repay or help another user via sqlservercentral.com.  I'm new to the forum.   Carl

Viewing 7 posts - 1 through 6 (of 6 total)

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