How to recieve the SQL Syntax of a DTS Package 2

[AHand]

Hello there, first of all i appollogize for my bad english. Im a native german!

I would be very glad if there is somebody who can help me with my problem.
My job is to migrate a sql 7.0 cluster to a sql 2000 cluster with full funktionalibility.
under

http://www.sqldts.com/

i got a nice tool that transfered all the DTS-Task of
the old system onto the testsystem.
Because I have to be very carefull i need to dokument all the steps taken.
Is there a way to recieve the complete SQL-Syntax of a DTS package?
Or do i simply oversee some button in the DTS-Designer???

 

[sqlturbo]

I am not sure if there is a way to look at the SQL syntax, if there is any, of a DTS package. What you can do, however, is save the package as a Visual Basic File and play around the with the code if you want to. Keep in mind, though, that it becomes much harder to open and edit the package graphically once you save it in .bas format.

 

[grahams]

AHand, believe me your English is a lot better than my German

The only thing I know for sure you can do is to save the package as a VB .bas module. This will convert the functionality of the DTS package into VB code, and should include all the SQL statements that are run.

Of course, depending how complex it is, you might be better off documenting the steps (i.e. scripting the SQL tasks and so on) and including a few screen shots as appropriate.

I might be able to offer something more constructive if you post a URL to the actual package you're using from sqldts.com.

Let me know,

Cheers,

Graham

 

[AHand]

Sorry to bother you again! I immediately did what you suggested, and i found out how to save a testpackage as a *.bas file. Please tell me why it´s now more difficult to edit that special DTS Task??? I though i would just create a file? The package looks the same to me.

@ grahams: i tried that, but then gave up because of the complexity of the DTS packages. Then i search the web, and finally i found the FORUMS!!! *smile*

so i though about what i would like to have and posted my question!

thx again i much appreciate your help!

 

[grahams]

No problem. By the way there is no reason why your package would be any more difficult to edit after doing a Save As VB file. It just saves a copy in VB format is all. I'm not sure what 5791 was getting at, unless they were talking about editing the package in VB itself, which would be harder.

Cheers,
Graham

 

[AHand]

Hi again,
is there a way for me without any programming knowledge except a little sql (but i´m working on it ;-) ) to rebuild the DTS Tasks? if not does somebody with VB knowledge can?

here is the complete url for the dts-task programm if
somebody is interested:

http://www.sqldts.com/default.aspx?220,272,272,1,1

cu!
Andreas

 

[sqlturbo]

In my last input I meant to say that to see the code or SQL syntax of a DTS package, it would have to be saved as a .bas file. I may have caused some confusion there. Sorry about that. However, as the complexity of the DTS package increases, it may be relatively difficult to edit it in VB. Also, a package re-created in SQL Server from a .bas file may fail to execute any child packages it may have. A workaround to that can be found at

http://support.microsoft.com/?kbid=299354.

Andreas, try this to re-create the DTS package.

1. Open the saved .bas file.
2. At the very end of the module, you should something like

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

goPackage.SaveToSQLServer "(local)", "sa", "password"
'goPackage.Execute
'tracePackageError goPackage
'goPackage.Uninitialize
Set goPackage = Nothing
Set goPackageOld = Nothing

End Sub
----------------------------------------------------------


To save the package instead of executing it, comment out the executing package lines and uncomment the saving package line (as shown above). gopackage is the instance of the Package2 object. You should find the "name" of the Package2 object at the very top of the module under Declarations. In your case, it would be 'thatname.SaveToSQLServer'. Instead of "local", type the name of the machine that you would be saving the package to as well as the password for sa. You may have to change the "Data Source" somewhere in the VB code depending on where your source is. Run the code and it should create the DTS package. One more thing, I believe if you run the code from the same machine as that on which SQL Server 2000 is, you'll be fine if you keep it as "local".

Hope this helps.