March 7, 2007 at 5:58 pm
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
March 8, 2007 at 9:16 am
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
March 8, 2007 at 9:47 am
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
March 8, 2007 at 10:05 am
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
March 8, 2007 at 11:25 am
One more... is this how the line should look for using Trusted SQL Server? Thanx again...
goPackage.SaveToSQLServer "(local)", "sa", "", DTSSQLStgFlag_UseTrustedConnection
March 8, 2007 at 11:32 am
goPackage.SaveToSQLServer "(local)",,, DTSSQLStgFlag_UseTrustedConnection
Russel Loski, MCSE Business Intelligence, Data Platform
March 8, 2007 at 12:51 pm
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