|
|
|
|
|
As everyone who has used it knows, the DTS
Package Designer in 7.0 needs a lot of work. In order to get something that is
flexible to use in a production environment, you have to write everything as code.
The Package Designer will probably leave just as bald as me if you try to do any
production work with it. I don't use VB very much and prefer not to write code.
However I have a need to do this extensively, because the Package Designer just
doesn't cut it. Paul Shapiro was kind enough to send me a VB module that will
reverse engineer an existing package and turn it into VB code and another one that gives
you a template of the things that need to be modified to make it work. This came
from either the SQL Server CD or off Microsoft's website as far as we know. No
warranty is made for the code or its usefulness to your environment. Below is the
text of the e-mail that Paul sent me along with the modules. The attached zip file includes 2 VB programs. ScriptPkg.bas is the one that reads an existing DTS package and creates a VB program to re-create the package. You need to add your SA password in the routine at the end of the file, and then I think the rest of it should run "as-is". VBScriptToDTSPackage.bas is the program it generated for one of my DTS scripts. I included it as a sample of the changes you'll probably have to make in your generated program. There were two major issues to fix: 1. The program as generated was all one large subroutine. VB has a limit on subroutine size. The program was too big so I had to break it up into a number of smaller subroutines. You probably will too. So if you're not used to VB maybe you can use this one as an example. 2. The created program includes property statements for many read-only db connection properties. If you look at the code in my sample, I've left those in but commented them out. That took a while, because I had to run in the debugger, and go one by one. You can use the commented property statements in the sample as a guide for fixing up yours. I think I also went through the generated code and cleaned up the references to table names so they would work for any connection I chose to use. I don't remember what I changed anymore. But now I can change the connection and the package still runs correctly. It didn't as it was generated by the DTS designer. Good luck, Paul |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.