There are several methods that can be used to start your DTS Packages, depending on what you want to do.
Each method has advantages and disadvantages.
I will illustrate several methods that I use, and offer my views on each.
If the examples here don’t fit your application then pick the one you like best, and change it until it does.
Methods
Listed below in no particular order are the key technologies/methods that can be used –
When to Use What?
How you use the above methods, or rather what method you use in a given solution is often a
case of personal preference.
SQL Script
The obvious choice is to use DTSRUN via xp_cmdshell. The OLE stored procdeure method is
also a good option, with the benefit of error checking, but is more complicated to use.
You could also you sp_start_job on a suitable SQL Server Agent Job,
although this is really just another way of using DTSRUN.
Stored Procedure
See SQL Script see above.
DTS Package
Calling a DTS package from within another package is an often asked question on newsgroups.
Most often people use one package to perform transformations of a text file into SQL server.
A primary package is often used to loop through the files in a directory, calling a
second package to import the files. (See Import all files in a directory (Multi Pkg) sample).
The obvious choice is to use an ActiveX Script within an Active Script task, but you could also use
OLE store procedures via an Execute SQL task.
DTSRUN could be used via an execute Process Task, but as connection properties or global variables
are often changed to reflect a new file name, an Active Script Task would still be required.
Command Shell or Batch file
DTSRUN is obviously designed for the job, but you could use isql to call a SQL Script or Stored Procedure
that uses one of the SQL Script methods above. This might be appropriate if you needed to alter a
parameter in the package first. Another similar method would be to use Windows Scripting Host.
Custom Application
Using the DTS Package Object Library inteface is the most flexible method but any of the others could also be used.
In this article I have often mentioned several methods of doing the same thing.
Usually the first method is the simplest and best. Some may ask why I bothered to
mention the others, just to try and confuse you perhaps?
Often the simplest and most obvious method does not fit when taken in context with the rest of your solution.
One reason for this is that you can often re-use code or objects in several places within a solution
yet in one instance the
method is not the most obvious. It is up to you to decide if re-use and thus less code to maintain
outweighs the ease of choosing the simple method. I have also tried to promote a bit of
lateral thinking, as I’m sure you know, the answer is always out there (..well nearly always),
it’s just a case of finding the damn thing!
