Post reply

Passing the DataSource as a parameter to DTS

  • March 11, 2003 at 3:42 am

    #159694

    Please advise: I have created a DTS package to transform an excel file to an SQL Server table. It works fine. The problem is I am going to run this through a website where the client can specify the name of the Excel file to upload and transform. How do I get this filename into my package dynamically?

    I am running the webpage in ASP VBscript and have the following code to execute the package... The GlobalVariable syntax I am using is changing the DataSource variable fine, and the package is running, but nothing gets into my database table.

    Any help appreciated.

    Function RunPackage(completename)

    'completename is path and filename for source Excel file

    'Set up DTS connection

    Set oPackage = CreateObject("DTS.Package")

    'Load DTS package

    oPackage.LoadFromSQLServer "MYSERVER","sa",,,,,,"myDTSpackage"

    'Set Global Variable

    oPackage.GlobalVariables("DTS_completename").Value = completename

    'Execute DTS package

    oPackage.Execute

    'Garbage collection

    oPackage.Uninitialize()

    Set oPackage = Nothing

    End Function

  • March 11, 2003 at 2:35 pm

    #450835

    Does the package execute successfully in Enterprise Manager? What version of SQL are you using?

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

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

  • March 11, 2003 at 3:21 pm

    #450840

    Hi Phill,

    Yes package runs fine in SQL Server 2000 when I can specify the Excel file to upload (the full path ie C:\Excelfiles\excel25.xls)

    When I try and create a dynamic global variable to hold this filepath and use an ASP page to then set it remotely, although my code at "DTS_completename" is set correctly to the dynamic path and filename, after the Execute statement and the package has run, there is nothing in my database table...

  • March 12, 2003 at 2:43 am

    #450853

    So at the moment I have compiled the SQL package as a VB executable which I call in ASP using Wscript.Shell and give the pathfile as a command line parameter...

    Would like to use a package though.

  • March 12, 2003 at 2:48 pm

    #450915

    Put a ActiveXScript task in your package and add a MsgBox to echo what the connection settings are for the Excel file. That way you can be sure that the global variable is being assigned properly.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

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

Viewing 5 posts - 1 through 5 (of 5 total)

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