Automating DTS ??
-
I have situation where i have to every month bring in new data from Text files which are Vertical tab separated in columns and carriage return for every row.
The are many such files which are entered into many tables
For this i will have to manually do the DTS every month.
Can we automate the DTS, so that every time i just have to do minimum effort and all the DTS run for the respective files?
-
Hey,
Sure, use the dtsrun utility to kick off a package (see books online), or use the SQL Server objects (DTS library). The library needs the client installed.
Brian
-
You can also schedule dts-packages in enterprise manager. Scheduling creates jobs for them which you can name and change their scheduling parameters logically.
When you have different jobs you can collect them as steps of one job as well (each dts-package forms its own job by default), this means little tabbing on keyboard to be done or clicking with mouse.
Other ways of doing this do exist too...
-j-
-
Another thing to keep in mind here is: "Do the names of the files change?" If so, you will have to account for this in the DTS, probably using an ActiveX Script to determine the name and then dynamically set the connection object for the text files.
-
Hey,
If using dtsrun or the DTS.library approach, you can store the file name in a global parameter. Then use a dynamic task property to change the file name, and lastly, dtsrun or the DTS.library can pass in the file name you provide.
Brian
-
Thank you everyone. This has been a great help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply