SQL Server‎ > ‎

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
        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
        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()

            'Write the read data to the appropriate Generic file based on the source file prefix
            Select Case Left(sFileName, 3)
                Case "err"
                     System.IO.File.Move(sFilePathName, sDestinationFile)                  
                Case "not"
                    ' Do Nothing
                Case "SAR"
                    'Do Nothing
                Case "WTS"
                    System.IO.File.Move(sFilePathName, sDestinationFile)
                Case "UPL"
                    'Do Nothing
                Case Else
                    'Do Nothing
            End Select


        Dts.TaskResult = ScriptResults.Success