clone a DTS package for a second database

[r937]

i've done the keyword search in this forum, and i've gone all through BOL...

i have a DTS package that i created with the wizard (i hate that stupid wizard), which copies 15 tables from my production database to my test database

this is an elaborate, painstaking procedure (i hate that stupid wizard) because for each of the 15 tables i'm copying, i have to
- drag the side of the column beside the source table to expand it
- click the checkbox beside the source table
- drag the side of the column beside the destination table to expand it
- click inside the destination table name and add a prefix (in effect changing the table name)
- click inside the little [...] button beside each destination table name
- in the window which pops up,
- click the radio button for Create destination table
- click the checkbox for Drop and recreate destination table
- click OK

make one mistake and you might as well start over (i hate that stupid wizard)

that's a lot of clicking, made all the more cumbersome because the stupid window where you select tables is not resizable (hence you have to drag the columns apart just to read them)

okay, after much trial, and yes, much error, i finally have a package that runs, and copies all 15 tables off production

now i want to take that package and run it not into my test database, but my integration database

souce database and tables identical, destination database different, but tables named the same (all owned by dbo)

you would think that this would be a simple, search n replace type of change, right?

any suggestions? because even when i open the package in Design mode, with all those stupid diagrams, how do i make a wholesale change like that? seems i have to right-click on every piece, and there's 4 pieces to every step -- drop, create, connection 1, connection 2

editing in Design mode seems even more cumbersome than just starting the wizard and defining it from scratch

what sort of GUI-crazy person designed these interfaces anyway? why make the assumption everybody uses a mouse and loves to click, click, click? and while i'm ranting, why can't i change the stupid 8-point fonts so that i can read them?

the packages themselves, once they're set up, they run really well, i'm very pleased with that part

but defining them is a monstrous pain in the ass

aren't there any third party tools or something to make this a bit easier, especially cloning a package which is identical in all aspects except the target database name?

rudy

http://r937.com/

 

[dky1e]

Take a look at the 'Copy SQL Server Objects Task' in the DTS designer.

It will allow you to easily change the source/dest db's. It also drops and creates destination objects if they alrady exist (indexes, tables, etc...)

 

[r937]

thanks, dky1e

the task is called Transer (not Copy) SQL Server Objects

it leaves a lot to be desired, because i cannot change the name of the table being copied, and when i tested it, it does not show which tables are being copied, there's just one step ("undefined&quot and when it's finished, it says what caused the error, and all 15 tables are empty!!

so i'm back to square one, redefining a new Transfer task?

how do i know what my error was and that i won't do it again?


any other suggestions?

 

[r937]

okay, the Transfer task (single step: "undefined&quot ran successfully, but it took approximately ten times -- not ten percent, ten times -- as long as the package i created with the DTS wizard, which had individual steps for drop, create, and copy...

now i'm torn between a painstaking, error-prone re-definition task (that stupid DTS wizard that i hate so much) which runs really well, or that easy-to-define Transfer package that runs like a turtle in molasses in january... and i still have to go in afterwards and rename the tables...

anybody have any other ideas?

bueller?

 

[checkai]

can't you simply do a save as and choose the other server to save it to?

dlc

 

[dky1e]

I'm not sure why it's taking longer than the standard data pumps you've created with the wizard.

Try unchecking the 'include all dependent objects' and
then uncheck 'use default options'.
Click on the options button and select the objects you want to copy (you might not need indexes, logins, etc...)

 

[DLSQL]

Are you trying to copy the table definitions from your production database to your integration database or are you trying to copy the data? Or Both?

Is this something that will be run over and over again or is this a one time shot?

If you are just interested in copying the table definitions, script them out using Enter. Mgr. and then run the scripts in QA against your integration db. If this is a one time shot and you want to move both the definitions and data then the copy (transfer) objects task is appropriate. If you want to copy data over and over again use DTS Pump tasks.

In order to make the Package go against the Integration package do the following (or save to a VB file and search and replace -- but you can never go back to the designer with those changes in place):
1) Save the package AS a different name.
2) Change your Connection Object to point to the Integration DB.
3) Use Disconnected Edit (right click in the white space of the package and select Disconnected Edit from the Context Menu)
3a) Goto the Pump Tasks and change the Destination Object Name to just have owner and the table name like so from db1.dbo.tablename to dbo.tablename.
4) If you need the package to be flexible and portable you can then use a Dynamic property task to read from an ini file and then change the Catalog property of the connection object.


DL
MCDBA, MCSD, MCT, etc.

 

[r937]

thanks, dky1e, i'll try that, assuming i continue with the one "undefined" Transfer task rather than the 45 individual steps, which i probably will not

checkai, that's the problem, if i save the package into the other server, all the steps still refer to the old server!! i have to change the database name wherever it occurs -- so easy if there were a text version of the package and i could use search-n-replace -- and editing each piece in Design is as hard or harder than creating a new DTS using the wizard (that i hate so much)

still hoping for a miracle...

 

[r937]

thanks, DL

yes, this has to be repeatable, it has to copy 15 production tables every night

perhaps i could have used TRUNCATE instead of DROP/reCREATE? would that be more efficient?

in any case i still have to name the database i'm operating in, right?

you said "you can then use a Dynamic property task to read from an ini file and then change the Catalog property of the connection object" but i'm afraid i do not understand that at all (i'm not a microsoft person)


> 1) Save the package AS a different name.
check

> 2) Change your Connection Object to point to the Integration DB.
check (there were 2 objects for the destination, and 2 for the source)

> 3) Use Disconnected Edit (right click in the white space of the package and select Disconnected Edit from the Context Menu)

no such option on my context menu

but thanks anyway, DL

i guess i have no choice but to use that friggin wizard

who designed this stuff anyway? somebody with a serious dislike of homo sapiens, obviously...

 

[DLSQL]

One more question: are you using SQL 2000? or SQL 7?

The Disconnected Edit feature is only available in SQL 2000.

Allow me to elaborate upon my instructions:
Open the Package in the DTS Designer and then follow my steps. Then you should be able to find the option on the Context Menu. If not try using the Pacakge Menu.

I'll agree with you the DTS is complex and has some limitations, but it is still one of the better items out there to do this sort of work.

DL
MCDBA, MCSD, MCT, etc.

 

[r937]

sql 7

and i totally agree, using that effing wizard is still better than coding all those statements by hand


thanks again for your help


rudy

 

[DLSQL]

r937

I have a way to make this easier in the future.

http://support.microsoft.com/default.aspx?kbid=239454

This article describes a tool on the SQL 7 CD that allows you to export the SQL package to a VB file. The VB file uses the SQL 7 DTS Object model to build a package.

Inside the code you can search and replace.

Then you can comment out the code to execute the package (oPackage.Execute) and instead use the oPackage.SaveSQLServer method to save it back to the server.

DL
MCDBA, MCSD, MCT, etc.

 

[r937]

thanks DL, i'll have to ask our DBA to give that a shot when i'm back in the office on tuesday

what's VB?

just kidding, i know what it is, but working with VB code might be tricky since i've never seen VB code before, and wouldn't know a method from a property from an object from a variable or whatever the heck is in there

thank &deity i don't have to work with DTS any more -- i've convinced the DBA that it's in his job description and not mine (database designer)

;o)