Home
Tools/Projects
ColdFusion
JavaScript
Articles/Tutorials
Creating Dynamic "onload" Events Using JavaScript
Client-to-Server Communication Using DHTML
Executing a DTS Package from CF/ASP/PHP/SQL
Creating a Custom ColdFusion Tag (101)
Structures
Downloads
Custom Tags
qForms JSAPI
Clients
About

Executing a DTS Package from CF/ASP/PHP/SQL
by Dan G. Switzer, II | 02/18/2003

Introduction

So, you're trying to execute a DTS package from your favorite scripting language, and you just haven't found an easy way to do it. Althought there are several ways to tackle the problem, I wanted to solve the problem without having to use command line tools. Seems like Microsoft would have provided an easy way to do this via a stored procedure, but they didn't. Fortunately some kind soul was nice enough to release a couple of stored procedures which provide the functionality Microsoft should have included with SQL 7/2000.

The following code uses two stored procedures that work in conjunction with each other to execute a saved DTS package. The first stored procedure is used to trap and display any errors that occur while excuting the stored procedure. The second stored procedure actually handles the execution of DTS package.

NOTE: Thanks to a heads up from Richard Kyllo, it appears the source code for these stored procedure originated from an article by Darren Green, published in the September 2000 Database Journal, in an article title Data Transformation Services (DTS). I know I've made a few changes to the stored procedures, but this does look like the code I first saw posted on Swynk.com.
WARNING: It appears you may need to have the Client Tools installed on the server in order for these Stored Procedures to work. If you're getting errors, please check to ensure the Client Tools are installed on the database server.
First things first: Error Trapping

Although this may seem backwards, the first step is to create a stored procedure which will be executed if any errors occurred while executing the DTS package. The following code should be added to the database before creating the spExecuteDTS stored procedure.

Source Code
Source Code
NOTE: Cut-n-paste the above code and save the stored procedures in any databases you plan to use the code with.
Creating the spExecuteDTS

The next thing you need to do is to create the "spExecuteDTS" stored procedure. This is the actual stored proc that you'll use to invoke the DTS package. Make sure to pay attention to the stored procedure's parameters:

@Server
The name or ip address of the server in which the DTS package is located.
@PkgName
The name of the DTS package as it appears in the DTS list.
@ServerPWD
The password to the server.
@IntSecurity
The type of SQL Security on the server. Use "0" for SQL Server Security (the default) or use "1" for the Integrated Windows Security.
@PkgPWD
The password for the DTS package. If the DTS package does not have a password, leave this parameter out (or assign it an empty string.)

Here's the code for the spExecuteDTS stored procedure.

Source Code
NOTE: Cut-n-paste the above code and save the stored procedure in any databases you plan to use the code with.
Usage

Usage is pretty straight forward and will vary depending on how you choose to invoke the stored procedure. For example, to invoke the stored procedure in SQL, you can use the following syntax:

Source Code
NOTE: If you recieve an error while running the stored procedure, make sure to specify the type of security you're running. By default, the stored procedure assumes you're running SQL Server security and not the Windows Integrated security.

To invoke the DTS package using Macromedia ColdFusion, you could use the following syntax:

Source Code
Conclusion

I hope you find this information useful, as I know I've found it quite useful over the years. I really wish I could give credit to the original author, so if you know who originally wrote these series of stored procedures, please e-mail so I can give rightful credit to the original author.

If you have any questions about this article, please feel free to contact the author at dswitzer@pengoworks.com.

Copyright © 2024, PengoWorks.com. All Rights Reserved.