SSIS Script to move/rename/archive source files

Use the following vb.net code to add a script task to a SSIS package. The script task reads the files in a specified folder, then archives those files to a different folder and finally writes the files to a generically named file in the Integration Source folder. You must add Imports.System.IO to the declaration section of your script.

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

' This task iterates through the files in the specified directory (sSourcePath)

' It copies each file to an appropriate archive folder and concatenates the creation date

' and creation time to the beginning of the file name.

' It then writes the contents to a generic named file based on the prefix of the source file.

' The new generic file is in the "E:\UploadArchives" target folder.

' Multiple source files with identical prefixes are appended to the same genric target file

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

Dim sSourcePath As String = "C:\Inetpub\ftproot\Data"

Dim di As DirectoryInfo = New DirectoryInfo(sSourcePath)

Dim fi As FileInfo

Dim sFileName As String = ""

Dim sFilePathName As String = ""

Dim sNewFName As String = ""

Dim sDestinationFile As String = ""

Dim dtFileDate As Date = CDate("1/1/1900")

Dim dtFileTime As Date = CDate("12:00:00")

Dim strFileDate As String = ""

Dim strText As String = ""

'If the Generic file exists, delete it

Dim sDestFile1 As String = "E:\UploadArchives\WPTS.txt"

If (File.Exists(sDestFile1)) Then

File.Delete(sDestFile1)

End If

' Create a data writer to the generic file

Dim objWriter1 As New IO.StreamWriter(sDestFile1, True)

Dim sDestFile2 As String = "E:\UploadArchives\err_WPTS.txt"

If (File.Exists(sDestFile2)) Then

File.Delete(sDestFile2)

End If

Dim objWriter2 As New IO.StreamWriter(sDestFile2, True)

'Iterate through the files in the source folder getting the creation date/time for each file

For Each fi In di.GetFiles("*.txt")

strText = ""

sFileName = fi.Name

sFilePathName = sSourcePath & "\" & fi.Name

dtFileDate = fi.CreationTime.Date

dtFileTime = fi.CreationTime

strFileDate = Format(dtFileDate, "MMddyyyy") & "_" & Format(dtFileTime, "HHmmss")

'Create a new file in the archive folder

sNewFName = strFileDate & "_" & sFileName

'Left(sFileName, Len(sFileName) - 4) & "_" & strFileDAte & ".dat"

sDestinationFile = "E:\UploadArchives\WPTS\" & sNewFName

'Read the data from the source file and remove the connection

Dim objreader As New System.IO.StreamReader(sFilePathName)

strText = objreader.ReadToEnd()

objreader.Close()

objreader.Dispose()

'Write the read data to the appropriate Generic file based on the source file prefix

Select Case Left(sFileName, 3)

Case "err"

objWriter2.Write(strText)

objWriter2.Close()

System.IO.File.Move(sFilePathName, sDestinationFile)

Case "not"

' Do Nothing

Case "SAR"

'Do Nothing

Case "WTS"

objWriter1.Write(strText)

objWriter1.Close()

System.IO.File.Move(sFilePathName, sDestinationFile)

Case "UPL"

'Do Nothing

Case Else

'Do Nothing

End Select

Next

objWriter1.Dispose()

objWriter2.Dispose()

Dts.TaskResult = ScriptResults.Success