Post reply

How to import file .txt having a changing name ?

  • May 18, 2004 at 7:53 am

    #59831

    Hi,

    I would like to import a text file with DTS. But this text file has a name which changes everytime : CLI0001.txt, CLI0002.txt, CLI0003.txt .... The last 4 numbers are in a table.

    I have created a connection to the text file, a connection to SQL Server and a Data Driven Query Task which inserts the lines in the .txt file in SQL Server.

    Is it possible to change dynamically the name of the .txt file ?

    THANKS 

  • May 18, 2004 at 10:11 pm

    #506891

    Hi,

    You can use the dynamic properties task to define the txt file dynamically?


    Balaji

  • May 19, 2004 at 5:32 am

    #506920

    ...or identify the newest file in the folder (by file timestamp)...?

  • May 19, 2004 at 6:52 am

    #506934

    I always force imported files to have the same name everytime.

  • May 19, 2004 at 8:16 am

    #506948

    You can save the DTS as a VB package, then change it so the new filename is passed into it or it or it calc's the correct filename to import.

  • May 19, 2004 at 7:17 pm

    #507050

    You can dynamically change the name of a text file.  One option is to use the ActiveX Script Task in your DTS to perform this name change.  Example:

     

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

     Dim FSO

     Set FSO = CreateObject("Scripting.FileSystemObject")

     Dim objFile

     Set objFile = FSO.GetFile("C:\TEMP\MyFile.txt")

     objFile.Name = "MyNewFile.txt"

     Set FSO = Nothing

     Set objFile = Nothing

     Main = DTSTaskExecResult_Success

    End Function

  • May 20, 2004 at 8:54 pm

    #507214

    This is one of the many questions covered at http://www.sqldts.com

    http://www.sqldts.com/default.aspx?200

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

  • May 21, 2004 at 5:30 am

    #507254

    Thanks a lot for the replies ! There is what I needed.

    For my last project, I have used only linked server and T-SQL to program my imports for each day.

    So this time I'm trying the DTS.

    But I'm asking how to choose between DTS and linked server/T-SQL for a replication ?

     

     

  • June 17, 2004 at 2:52 pm

    #510932

    The suggested method works well when you can determine the name.  When you can't, use the following method:

    Dim fso, folder, file, filecollection

    Set fso=CreateObject("Scripting.FileSystemObject")

    Set folder=fso.GetFolder("<<Path>>")

    Set filecollection=folder.Files

    For each file in filecollection

     DTSGlobalVariables("ImportFileName").Value=file.Name

     Exit For

     

    Next

    DTSGlobalVariables("ImportFilePathName").Value="<<Path>>\" & DTSGlobalVariables("ImportFileName").Value

Viewing 9 posts - 1 through 8 (of 8 total)

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