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 |
SQL Server >