Pass parameter to SQL DTS package

Question

Hi,

I have one source database, which I want to copy (table structures) to new database.

I need to execute this package at least 50 times.

Each time my source database is same, but destination database varies.

So I decided to create a DTS package that will receive a destination database name as a parameter.

Que: Is is possible to pass a parameter in DTS Package? If so then how?

If it is possible that My package will create a database with the name passed in database.

Please provide me a guide to achieve my task.

Thanks.

Answer 1

DTS packages can be parameterised using Global Variables.

Once a global variable has been added to a package, its value can be set at runtime using the /A switch of the dtsrun command line tool.

It will be possible to create new databases within DTS using a CREATE TABLE statement in an Execute SQL task, but developing the package will be somewhat difficult. As the DTS designer carries out early validation on your connections, the target database must exist before you refer to it in a connection. You would need to create the database, then create or amend a connection referring to the new database so you can carry out operations on it (rather than setting the connection at the start of the process).

For this reason it might be simpler to create a single empty copy of your database, then use backup/restore to create copies. This process can also be scripted (backup/restore).