Executing a DTS Package from CF/ASP/PHP/SQL
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.
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.
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:
Here's the code for the spExecuteDTS stored procedure.
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:
To invoke the DTS package using Macromedia ColdFusion, you could use the following syntax:
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 firstname.lastname@example.org.
|Copyright © 2013, PengoWorks.com. All Rights Reserved.|