March 2, 2006 at 3:25 pm
After processing several text files in my SSIS 2005 package which is scheduled as a job, I need to rename the files appending a timestamp to the filename and then move them to another directory on the file system. The number of input files varies for each run. Does anyone know how to automate this process through a SP or BIDS?
example of input file names:
1111_data.dat
2222_data.dat
3333_data.dat
I need the renamed files to look like
1111_data_timestamp.dat
Thanks
March 3, 2006 at 11:06 am
You can add a new containter to the end of your package, and place a Script Task in it to do the work. Below is some basic code (sans error-handling, etc.) you can modify to your needs.
Change the values of SourceDirectory, TargetDirectory and the format string used with the Date.ToString
Imports
System
Imports
System.Data
Imports
System.Math
Imports
Microsoft.SqlServer.Dts.Runtime
Imports
System.IO
Public
Class ScriptMain
Public Sub Main()
Dim SourceDirectory As String = "C:\sql\SSIS\Target"
Dim TargetDirectory As String = "C:\sql\SSIS\Target\test2"
Dim FileList As String() = Directory.GetFiles(SourceDirectory)
Dim CurrentFileName As String, NewFileName As String, i As Integer
Dim DateString As String = Date.Now.ToString("yyyyMMdd-HHmm")
If FileList.Length > 0 Then
For i = 0 To FileList.Length - 1
CurrentFileName = FileList(i)
NewFileName = Path.Combine(TargetDirectory, _
Path.GetFileNameWithoutExtension(CurrentFileName) & _
DateString & Path.GetExtension(CurrentFileName))
If Not File.Exists(NewFileName) Then
File.Move(Path.Combine(SourceDirectory, CurrentFileName), NewFileName)
End If
Next
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End
Class
Eddie Wuerch
MCM: SQL
March 3, 2006 at 4:53 pm
Thanks for your reply Eddie. I wound up doing it in a job step using T-SQL script which calls extended procedure xp_cmdshell. Its seems to work pretty good.
declare @rt int
declare @cmd varchar(1000)
declare @NewFile varchar(50)
set @NewFile = ' *data' + '_' + replace(replace(replace(convert(varchar(30), getdate(), 20), '-', ''), ' ', ''), ':', '') + '.dat'
SET @Cmd = 'C:&&CD\&&CD C:\directoryname&&ren *data.dat' + @NewFile
EXEC MASTER..xp_cmdshell @Cmd, NO_OUTPUT
if (select @rt) = 0
RAISERROR ('step failed - *data.dat files not found for rename',50001,1) with log
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply