April 18, 2002 at 4:34 pm
We have an old dev database with an associated DTS script. The developers want to copy it to production but using a new name (they need the DTS script also). They have also changed their coding standards over time so the original TblModel will now be called tblVehicleModel. I can handle the pure DB end but the DTS scripts use the old tablenames all over the place (in transformations, in Connection names, etc). Short of rewriting the script is there anything I can do to "convert" the DTS script to the newer names?
TIA,
Bill Salkin
April 19, 2002 at 5:06 am
You will end up going thru the package to make the changes as I don't know of a search and replace app available. You may want to save the package to the production server then open and make the changes (this is as short of rewrite as you can get as far as I am aware).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 19, 2002 at 4:22 pm
If you change the SQL query part of the DTS package you will find that your transformations have been remapped, not nice!
If you are using SQL 2000 you could take advantage of the new Save AS VB option(this is not available in 7) You can then edit the DTS package directly in VB using the object model, then execute to recreate your package.
You should also be able to search and replace this way too from within VB.
April 19, 2002 at 5:35 pm
I can dump the package to VB (under Sql 2K) but please elaborate on your comment "...then execute to recreate your package." How do I do that?
April 20, 2002 at 7:53 pm
OK, Step 1: Save out your package to vb
step 2: backup your original package and put it somewhere safe(just a precaution).
Step 3: Create a new standard Visual Basic project in VB.
Step 4: open the .bas file in notepad select all and copy into the main form of the project.
step 5: add a reference to the DTS objects to your project.
step 6: put a button on the form and in the onclick event write Call Main
step 7: find the line goPackage.Execute and comment it out, then write goPackage.SaveToSQLServerAs "packageName", "(local)", , , DTSSQLStgFlag_UseTrustedConnection instead.
press F5 to run the project. Click the button to create the package in your local database.
What the above has done is to create an identical copy of your package with a name of your choosing. In order to alter your package you will need to alter the relevant package properties in the code before running.
Clear as mud:)
April 20, 2002 at 9:39 pm
You might find parts of this article helpful:
http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp
Andy
April 22, 2002 at 1:58 am
When you say DTS Script are you talking about VB Script in in one or more steps of your package - are they content of a text property of the TransformServerProperties of the Transformation object (of let's DataDrivenQuery task or Transform Data Task)
I have developed a script ( which is a part of the DataDriven Query Task) which read a record from ( it happens to be an Excel Workbook sheet) - which have several columns describing what package to open and what to change - then it opens the package , I traverse in the script as many different collections (eg connections, global variables etc) and properties of the package itself and all it's tasks - I have not done it for all type of tasks - only those I was using at the time - but this include the DDQ task, Execute SQL , Transform Data Task, Send Mail task, ActiveX Script task, Execute Package Task. And changes some specified properties (driven by the Worksheet columns with the new values as well as changes any properties (sort of search and replace) if the content matches search value(s) set in the excel file. The resulting package can be saved (overwritten - actually it will created a new version) to a different name or *.DTS file ( existing or not) depending on settings in my source Excel file. As long as the property ( in your case name of the table) that I detect ( I do not claim I check all of them) contains your name "TblModel" it will be changed to a new text.
So it will change all occurrences of "TblModel"to "TblVehicleModel" all over the places in the package in one go.
At the end the record in the excel file is updated to rteflect that the processing took place. I have used this process now already hundreds of times - it works - although as I said before it won't change everything - but it works for me.
In real life my worksheet contains many records ( read package information) which are processed in succession. I just run the package which then does the editing for each package in a time I need to fetch a cup of tea. ( Most of the time takes to actually open the package and then write it back)
Let me know if you or anybody else in that matter is intersted in seeing more details.
Cheers
Tom
April 23, 2002 at 5:00 pm
Tom,
I am talking about the VB Scripts that are inside a DTS connection (to massage Source into Destination. Also, I made the mistake (?) of using table names in my step/connection textual descriptions. I'd be interested in seeing your "script" -- maybe some good ideas I can steal!
Bill
April 23, 2002 at 8:14 pm
Bill
It looks like I cannot put too lengthy text into the message.
( or I do not know how)
I'll send you an e-mail with the code.
Tom
Edited by - ipTomN on 04/25/2002 6:39:11 PM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply