July 29, 2002 at 2:18 pm
We are developing a new application and recently changed all the column names in the database to remove a suffix. Is there an easy way to update the current (numerous) DTS packages to specify the new names?
July 29, 2002 at 2:44 pm
Not that I know of. I think you will have to go through each one.
Steve Jones
July 29, 2002 at 3:36 pm
Possibly you could save all of them out as VB code, do a search/replace to fix, then execute each to create a new version. You'll lose text annotations doing it this way though.
Andy
August 1, 2002 at 6:36 pm
Hi Dcurtis
Depends how deeply you use names of the columns in the packages.
eg imagine you have all the text you use in your tasks (eg when using disconnected edit)
when you work in including the properties etc in at text file -
would you be able to do a global wildcard type of search and replace
(eg replace "invDOB" with "DOB") and it won't affect other parts of code.
Then you can write a ActiveX script.
I have been using a package with DDQ task with such script (inside DDQ task that reads a
prepared source table with the package names to open - and other stuff needed to open them
- and have somewhere else the replacement list) - destination in such task has
to be set up but you do not have to write anything to it).
I needed only to do very limited number of replaces and sometimes different for
each package - so I placed the replacement list in the same source table with the
package names.
You can do it of course in VB.
Your code would open each package - dig into all properties of the tasks, connections,
global variables that you know that you need to change -
eg if you use SQL Task and in the SQLStatement you refer to the columns which names you
need to change then you will have to (In Script or VB program when package is
open get to this property for each such task and do your replacement saving the new value back.
Once you are done with all your changes - all your tasks, global variables, connections etc
then you save back the package (if in MSDB database - it will create a new version of the package)
or save somewhere else (to different server or to DTS file of your choice - or with different name.
whatever your prefernce is.
For each different type of TASK you need to perform different checking.
This can be tricky if you have ExecutePackage tasks. But it worked for me.
If you miss something you still have the chance to edit things in Disconnected Edit.
This gives you an idea - I CAN PROVIDE SAMPLES of (WORKING) the code.
and more description if you need it.
My DDQ task did global change for 30 packages in about 2 minutes
Most of the time taken by opening the packages and then saving them back.
Many packages contained more than 10 tasks.
Cheers
Tom
P.S. Sorry if the text a bit chaotic I want to write it as quickly as possible.
Hopefully it will help you.
August 1, 2002 at 6:58 pm
Good reply. Im not a DTS Wizard (that would be Brian K!), what I've to be pretty effective is to create the package, save as VB code, then just add the whole module to my app. Essentially using the DTS Designer as code generator. Then I can very easily handle changes, add extra functionality, etc.
Anyone have comments on this approach?
Andy
August 5, 2002 at 1:15 pm
Thank you all for your help!
August 6, 2002 at 12:35 pm
Andy,
I've never worked with the DTS designer in VB, but if you can open a DTS package in VB, then save it back to the server, that would be the way to go. Search and replace would easily dig into every nook and cranny of the package. Heck, for that matter, you could open the VB code file in notepad and use the replace function. Cool!
John
August 22, 2002 at 1:41 pm
I'm a VB novice. The original posting was submitted on my behalf. I can get the DTS packages to a VB format and do the edits. Specifically, what would have to be done next in order to Recreate a DTS package with the changes?
quote:
Possibly you could save all of them out as VB code, do a search/replace to fix, then execute each to create a new version. You'll lose text annotations doing it this way though.Andy
http://www.sqlservercentral.com/columnists/awarren/
August 22, 2002 at 3:10 pm
Found the answer! For anyone else who may find themselves with similar dilemma . . . on the SQL server help screens, the following document specifically spells out the procedure: "Running a DTS Package Saved as a Visual Basic File". Unfortunately, the process really massacred my DTS package. It is almost unrecognizable.
Time to explore another alternative.
quote:
I'm a VB novice. The original posting was submitted on my behalf. I can get the DTS packages to a VB format and do the edits. Specifically, what would have to be done next in order to Recreate a DTS package with the changes?quote:
Possibly you could save all of them out as VB code, do a search/replace to fix, then execute each to create a new version. You'll lose text annotations doing it this way though.Andy
http://www.sqlservercentral.com/columnists/awarren/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply