| How do you want to start your DTS Package today? |
| ActiveX
| DTSRUN.EXE
| Enterprise Manager
| OLE Stored Procedures
| SQL Namespace
| SQLServerAgent
| Visual Studio |
ActiveX Script
In this instance I am referring to ActiveX script as used in the Active Script tasks within
a DTS package. The examples only cover VBScript as I don’t know JScript.
Basically you need to create the package object, and load the relevent package into that object.
You can then use the Execute method on that object.
Set oPKG = CreateObject(“DTS.Package”)
oPKG.LoadFromSQLServer “.”, , , 256, , , , “Package Name”
oPKG.Execute
oPKG.Uninitialize()
Set oPKG = Nothing
The example above simply loads a package from the local server, using trusted security, called Package Name.
You can also load File or Repository packages using oPKG.LoadFromStorageFile and oPKG.LoadFromRepository respectively. These
are fully documented in SQL Server Books Online, as is the full syntax of LoadFromSQLServer.
For a more useful application of this method see my Import all files in a directory article and sample.
DTSRUN.EXE Utility Enterprise Manager – Manual OLE Stored Procedures SQL Namespace (SQL-NS) SQLServerAgent Visual Studio Tools
The DTSRUN command line utility is supplied with SQL Server (mssql7binndtsrun.exe) and is filly documented in
SQL Server Books Online. I will not duplicate that information here but concentrate more on the uses that can be made of it.
You can run any package type by supplying the relevent parameters in the command line string.
Thus any method of executing an operating-system command shell can be used to execute DTSRUN and subsequently any package.
By using the xp_cmdshell extended stored procedure you can execute a DTS package from within a SQL script or stored procedure.
You can also call it from a batch file or even another program via the relevent command shell syntax.
This is perhaps the most useful and flexible method of executing DTS Packages, but it does unfortunately have one disadvantage.
There is no easy way to debug errors. If the error is in the package then you can use the package Error File
(Set under the Package Properties General tab), but the utility itself returns no error information except
from that visible when run from a command prompt. This is of course limited to the development stage.
This is obviously the simplest method, and also the least flexible.
Within Enterprise Manager, navigate to the relevent package type folder, under Data Transformation Services, right-click the package, then click Execute Package.
See the newer DTS Packages & OLE Stored Procedures article.
There are two relevent methods, obj.ExecuteCommandByName and obj.ExecuteCommandByID.
Either can be used to execute packages when the SQL-NS object is a DTS package.
The main advantage is that this invokes the same execution monitor control as Enterprise Manager.
This shows the steps as they are executed with any error information only a click away.
The disadvantage of this is that you either need to distribute Enterprise Manager or else carefully
identify the DLLs & RLLs required and distribute/register them yourself.
There is a very good VB SQL Namespace Browser sample on the SQL7 CD-ROM (mssql7devtoolssamplessqlnsvbbrowse).
Using the SQL Server Agent you can schedule a DTS Package to run later or on a recurring schedule.
Within Enterprise Manager, navigate to the relevent package type folder, under
Data Transformation Services, right-click the package, then click Schedule Package….
You are then prompted to enter your schedule information.
What this process does is to create a SQL Server Agent Job of the same name as the DTS Package.
There will be a single Operating System Command step, containing the hexadecimal encrypted
command line string for DTSRUN.EXE to execute the package. The string is encrypted
to protect the package password.
When using this method you must ensure that the SQL Server Agent Service is
running under an account with adequate privileges to execute all package
commands and access all files and objects required. This is particularly important if
you access files on another machine, as the system account will have no authentication for that machine.
Whilst this is not a method in it’s own right it is one of the best ways to encapsulate the methods listed above into
a useable solution.
By using the DTS Package Object Library reference, the same objects and methods as used in the
ActiveX script section above are available to you.
This means that you can totally integrate the development and execution of DTS Packages into your
Visual Studio project. For guidance and an example of executing packages from Visual Basic see the DTS How to… article How to Execute a DTS Package from… Also check out the DTS samples, mssql7devtoolssamplesdts.
For information, my tools DTSBrowse and DTSBackup
use a combination of the DTS Package Object interface, SQL Namespace interface and ActiveX Data Objects
(ADO). Both were written in Visual Basic 5 SP3.
| How do you want to start your DTS Package today? |
| ActiveX
| DTSRUN.EXE
| Enterprise Manager
| OLE Stored Procedures
| SQL Namespace
| SQLServerAgent
| Visual Studio |
