Post reply

Dynamically Get Path/FileName for DTS Connection

  • April 25, 2003 at 9:33 am

    #160096

    I have a DTS package that I created that takes an Excel spreadsheet file and imports it into a SQL Server 2000 database, as a table.

    First, it drops the existing table, then creates the table based on a T-SQL script, then it imports the Excel spreadsheet fields and data into the newly created table.

    I used the DTS Import/Export Wizard to create the import tasks from Connection 1 (Excel spreadsheet) to Connection 2 (specified SQL Server database). Then, I used the DTS Designer in Enterprise Manager to add the Execute SQL Tasks for drop table and create table, and applied the workflows.

    It all works fine. My issue is that for Connection 1’s File Name property (specifying the database path and file name holding the data for import), I hard coded a path/file name, but what I would like to do is take the File Name property and add end-user flexibility by allowing the File Name to be based on a path and file specified during run-time by the end user via a common dialog box (like in VBA/VB). Is there any way to accomplish this using a DTS package? Using an ActiveX Script?

    What I want is dynamic configuration of a DTS package object, in this case a Connection object. Can I pass a variable to a Connection object? Any ideas?

    Any assistance would be greatly appreciated.

    Thanks,

    CherylDixon

  • April 25, 2003 at 9:36 am

    #455401

    You can do it via an ActiveX task and global variables. Also, check out sqldts.com for an article there on how to do this with a common dialog box.

    hth,

    Michael

    Michael Weiss


    Michael Weiss

  • April 25, 2003 at 10:35 am

    #455409

    Hi Michael Weiss:

    Can you give a specific reference (via URL) to the specific article on how to use a common dialog box in combination with ActiveX task and global variables?

    I went to the site (sqldts.com), but I want to make sure I know the exact article that you are referring to on that web site.

    Thanks,

    Cheryl Dixon

  • April 25, 2003 at 10:48 am

    #455411

    Hi Cheryl,

    Here is the url for the article...hope this does the trick for you. Let us know if you deploy it and how it works, okay?

    http://www.sqldts.com/default.aspx?6,101,226,0,1

    Regards,

    Michael

    Michael Weiss


    Michael Weiss

Viewing 4 posts - 1 through 3 (of 3 total)

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