DTS - Change File Name

Question

DTS - Change File Name

Colin Betteley

Hall of Fame

Points: 3095
More actions
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

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

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

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 1

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

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 2

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

Jez

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 3

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