Post reply

DTS - Change File Name

  • March 19, 2008 at 10:50 am

    #154212

    Hi,

    I need to pick up a csv file transfer the data to SQL Server and then rename the csv file - adding a date to the end of the file name.

    Anyone got any ideas? Thank you in advance.

    Regards

    Colin

  • March 19, 2008 at 7:15 pm

    #791831

    Colin Betteley (3/19/2008)

    Hi,

    I need to pick up a csv file transfer the data to SQL Server and then rename the csv file - adding a date to the end of the file name.

    Anyone got any ideas? Thank you in advance.

    Regards

    Colin

    There are several methods...

    SSIS can import the data, calls to xp_cmdshell can copy and rename the file using OS commands. YOu ocan find more about these in BOL

    DAB

  • March 20, 2008 at 5:11 am

    #791919

    You could also use an ActiveX task within DTS to access the File System Object to rename the file.

    Jez

  • March 20, 2008 at 7:11 am

    #791971

    Thanks folks. Have tried xp_cmdshell without success and I am now trying ActiveX. My script below fails with "Expected end of statement" but I cannot see any error. Thoughts? Thank you.

    Colin

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

    ' Visual Basic ActiveX Script

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

    Function Main()

    Dim sSourceFile As String

    Dim sDestinationFile As String

    sSourceFile = "\\ServerName\FilePath\DTS_CSV_Name_Change_Test.csv"

    sDestinationFile = "\\ServerName\FilePath\DTS_CSV_Name_Change_Test" + Date.Now.ToString("yyyyMMddhhmmss") + ".csv"

    System.IO.File.Copy(sSourceFile, sDestinationFile)

    Dts.TaskResult = Dts.Results.Success

    End Function

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

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