reuse a DTS job
-
I have a job to extract data from a table, say TABLE_1, and the data is loaded with no transformation into TABLE_2.
Similar jobs are needed for hundreds of other tables. I want to reuse this job for other source and traget tables.
How can I make the source and target names as parameters that can be passed to the job at runtime? All sources are on one database, and all targets on another database.
What happens if databases are also different? Can the database connections also be decided at runtime?
Thanks!
-
As long as the structure of the tables is the same, you can replace TABLE_1 and TABLE_2 with your new tables names dynamically and everything will work great. If however you have different table structures, which I presume is most likely the case, then you'll need to re-do the transformations each time you transfer a new table.
Personally, I'd go for the approach of having a seperate DTS package, or datapump task, for each table. Seperate packages gives you a lot more flexibility in what is run when, and also you don't need to spend so much time creating all the necessary ActiveXScripting to set the transformations. Not to mention the obvious maintenance that would be needed when tables are changed/removed/added.
--------------------
Colt 45 - the original point and click interface
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply