Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 23, 2000

DTS Execution Methods

By Darren Green


| 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

The DTSRUN command line utility is supplied with SQL Server (\mssql7\binn\dtsrun.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.

 

Enterprise Manager - Manual

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.

 

OLE Stored Procedures

See the newer DTS Packages & OLE Stored Procedures article.

 

SQL Namespace (SQL-NS)

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 (\mssql7\devtools\samples\sqlns\vb\browse\).

 

SQLServerAgent

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.

 

Visual Studio Tools

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, \mssql7\devtools\samples\dts\.

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 |




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date