Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Cupertino, CA
    Posts
    10

    Unanswered: Selecting a File in DTS using ActiveX Script

    Hi Everyone,

    I have a question regarding transforming a text file from a folder into a table using DTS. The text file is on a different server and the name is in the format --- Orders040105.txt I am able to select the file using 'FileSystemObject' as the file required changes everyday, but I do not know how to use the file for the transformation using ActiveX Scripting. I have 2 'Activex Script Tasks' in the DTS Package--- one for selecting the text file and the other for the Transformation.

    Function Main()
    ??????????????????????

    DTSDestination("Order_Date") = DTSSource("Order Date")
    .
    .
    .
    End Function


    Thanks in Advance

  2. #2
    Join Date
    Aug 2003
    Posts
    39
    Add a text file source connection to the package, then in your activex script add the following given you have the path you can repalce the folderName & fileName variables with your path.

    Set oPKG = DTSGlobalVariables.Parent
    Set oConnectionSource = oPKG.Connections("Text File (Source)")
    oConnectionSource.DataSource = (folderName & fileName)
    Set oConnectionSource = Nothing
    Set oPKG = Nothing


    hope this helps

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    you can have 3 activex tasks

    1st one to rename Orders040105.txt to some temp_file.txt
    2nd one to use this temp_file.txt and transform the data to a table and
    3rd one to rename temp_file.txt to Orders040105.txt (if you want to)

  4. #4
    Join Date
    Jan 2004
    Location
    Cupertino, CA
    Posts
    10
    Thanks SHICKS and ROHIT for the prompt reply. I am working on it right now and shall update you with the result.

    Thanks a LOT!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •