Execute DTS with Parameters from Stored Procedure

Question

Post reply

Execute DTS with Parameters from Stored Procedure

coders4hire

SSC Enthusiast

Points: 171
More actions
November 20, 2002 at 10:50 pm

#105300

We have an ASP application in which our clients upload files (txt, csv, mdb, dbf, etc.) All of the data is relatively small (1,000 records or so) but needs to be converted to csv for some processing we do.

I've been doing a lot of reading and my thought is that we could write a stored procedure that calls a DTS package (depending on file type) to convert the file. HOWEVER, since multiple users are in the system at any one time, we timestamp the file that is to be processed.

Is there a means of passing the source and destination file names through a SQL procedure to a DTS package and executing it via ASP? I've read that we can use ActiveX or Shell commands to execute the package but our application is strong on utilizing Stored Procedures.

Thoughts? Recommendations? Is this a good idea? How can it be done???

Thanks,

Doug

doug@coders4hire.com

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 1

Hi,

You can create global variables within the DTS package for source and destination. Then you call the package from a stored procedure using the dtsrun command line utility (via xp_cmdshell). Dtsrun allows you to call the package assigning the global variables via a switch (type dtsrun /? at the command line for the switch).

Regards,

Andy Jones

.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

The best way is of course using Global variables as long as is supported by your version of SQL (SQL 2000). If so then look at this thread on how to call a DTS package from ASP directly http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4951&FORUM_ID=19&CAT_ID=3&Topic_Title=Passing+Parameters+to+DTS&Forum_Title=Data+Transformation+Services+%28DTS%29

If it is not then try this

1) Create a control table with the information you need.

2) Create a job to kick of the DTS package which loops thru until no more files in control table.

3) First step in DTS is xp_cmdshell built dynamically from the control table info to copy the file to a controled location (another folder and name that matches path in DTS).

4) Next pieces determines path for compatible file name.

5) Path for comatible file name processes the file.

6) On Success delete the file and remove item from control table.

7) Move to next item or end package.

This is one possible way with SQL 7.

coders4hire SSC Enthusiast Points: 171 More actions · Answer 3

We're really struggling with this. We set the global variables in the DTS package, but we don't know how to actually call them within the DTS. If you could show me an example, I would be eternally grateful. ie. XLS to CSV...

1) what would the DTS Package look like where we want to pass a source and destination file?

2) what would the Stored Procedure look like?

3) what would the ASP look like?

I know that's a lot. We're in a real bind and would appreciate the help! Maybe I could do some graphics and layout work for sqlservercentral.com

Thanks!

Doug

coders4hire SSC Enthusiast Points: 171 More actions · Answer 4

We're still working on this (and hope we'll get some help soon)... we'll be ordering the DTS book today but it won't be here soon enough.

I guess the confusion in all that I read is:

a. DTS: How to build the Package... I see that I can use ActiveX Stript Task... but then I also read examples where folks have used Dynamic Properties Task.

b. ASP: How to execute the package. The server is on another machine so we don't know whether to use a COM object, SQL Procedure, Shell execution of DTSRun (doesn't sound good), etc.

We've got a good DBA, ASP Programmer, and me (jack of all trades, master of none) but none of us have DTS experience.

I appreciate the help!

jfb SSCrazy Points: 2469 More actions · Answer 5

jfb

SSCrazy

Points: 2469

November 21, 2002 at 2:16 pm

#442023

Here you have more resource in how to execute a pkg from ASP

http://www.sqldts.com/default.aspx?6,104,207,7,1

Have fun!!

Johnny

coders4hire SSC Enthusiast Points: 171 More actions · Answer 6

coders4hire

SSC Enthusiast

Points: 171

November 21, 2002 at 2:25 pm

#442024

I've seen this page a couple of times already, but it doesn't help. It doesn't show how to pass the parameters (source and destination file names).

philcart SSC-Forever Points: 47794 More actions · Answer 7

philcart

SSC-Forever

Points: 47794

November 21, 2002 at 3:38 pm

#442034

This article is probably more what you're looking for.

http://www.databasejournal.com/features/mssql/article.php/1459181

It shows how to use the sp_OA procedures to set global variables on page 2.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

coders4hire SSC Enthusiast Points: 171 More actions · Answer 8

One down... one more problem to go!

We had a programmer develop a COM wrapper in VB6 to call the DTS functionality with passed parameters. It's working fine with one exception. Since the IIS server is on one server and the SQL server is on another - we had to install SQL on the IIS server in order to load the proper DLL's to run.

Off-hand, is there a DTS 'run-time' install? Are there particular libraries and dll's I can add in VB's project manager? Can I make an Installation package using package and deployment wizard which will install the necessary files?

I realize these are fairly basic questions. We just don't have the programming resources most companies do. The programmer we brought in built the COM wrapper - but didn't really understand the DTS object model, etc. to make these decisions.

Thanks,

Doug