|
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 |
|
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.
|