Copy and edit DTS

[gyfu]

Hi guys,
I need an easier way to copy and edit a DTS. Currently I have a DTS with probably about 50 SQL task. Each task of course has SQL statments.

Every time, I have a new customer, I have to go into every of this single task and change the name of the DB that I associate them with. this is because some of the table name and db name has the customer's name and I need to change it.

Currently, I will import all the 50 task into each individual text file and do a find and replace in ultraedit. Afterwhich, i will open each task and open each text file for each task.

I need an easier way to do this. I thought of saving the DTS as a VB file, do a find and replace to change the name. but then I don't know if there is a way to import the VB file back into DTS format.

Please advise.

btw, am using SQL Server 2000


thanks.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[AlexCuse]

It might be labor intensive now, but have you thought about modifying your DTS so you can pass it Global Variables, and then set data source names, etc based on these?

It would probably save you a lot of time down the road.

I'm not sure about the VB file idea, let me know if that works out for you.

Good Luck,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[gyfu]

Hey Alex,
I am not sure of how to put use a global variable for my case here. An example of a sequel statment in my task would be:

Select * NameofDB..Account

As You can see the NameofDB is my variable. How can I use global variable for these?

Let me know if there is a solution to this.

thanks again.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[AlexCuse]

What you would do is build a SQL string and set it as the value in your task, or set up the query to use a global variable as a parameter.

Take a look at this, it should help get you started:

http://www.sqldts.com/default.aspx?205

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".