Post reply

Execute DTS with Parameters from Stored Procedure

  • 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

  • November 21, 2002 at 1:55 am

    #441943

    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

    .

  • November 21, 2002 at 4:19 am

    #441956

    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://www.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.

  • November 21, 2002 at 10:30 am

    #442001

    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

  • November 21, 2002 at 11:46 am

    #442005

    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!

  • 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

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

  • 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

  • December 2, 2002 at 11:27 am

    #442818

    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

  • December 2, 2002 at 3:51 pm

    #442842

    Check out the redist.txt file on the SQL CD-ROM.

    It'll show what files need to be copied and which files must be registered.

    Thanks

    Phill Carter

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

  • December 2, 2002 at 8:31 pm

    #442867

    Here is a complete library

    http://www.sqldts.com/default.aspx?6,105,225,0,1

    Johnny

  • December 3, 2002 at 12:40 am

    #442871

    THANKS!

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

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