?
Solved

SSIS File System Task rename

Posted on 2011-10-20
2
Medium Priority
?
556 Views
Last Modified: 2013-11-18
My aim of this task is to produce 3 txt files, then merge the txt files into 1 csv file with the date added to the newly created csv file.

I have a ssis package and part of the package extracts some data and produces 3 txt files. Then I use a file system task to rename the newly created file.

I am then using a script task, which merges the file into 1 file, which is fine.
I then have a file system task, which I am using to rename the file.
As follows.

Isdestinationpathvariable false
Destinationconnection PE.csv
Using a file connection manager editor.
Using an expression to set the date.
Overwritedestination true
Operation rename file
Issourcepathvariable true
Sourcevariable user:src_file – variable with the path and file name.

If I run the script task then the file system task individually it works, but when I run the package as a whole the file system task fails.

I’ve tried setting delay validation but it does not work.

Any ideas on how to fix it or improve on what I’ve done.

Thanks guys.









Dim objFSO
    Dim objFile
    Dim objOutputFile
    Dim objTextFile
    Dim strText
    Dim sFolderHeader '= "\\folderb-001\Data\folderc\folder\Extracts\Header.txt"
    Dim sFolderDetail '= "\\folderb-001\Data\folderc\folder\Extracts\Detail.txt"
    Dim sFolderFooter '= "\\folderb-001\Data\folderc\folder\Extracts\Footer.txt"
    Dim my_File_Name
    Dim strLine
    Dim strNewContents
    Dim strFileName

    Const ForReading = 1

    Public Sub Main()

        objFSO = CreateObject("Scripting.FileSystemObject")
        objOutputFile = objFSO.CreateTextFile("PE.txt")

        'Outputs the header record
        Header_Extract()
        'Outputs the detail record
        Detail_Extract()
        'Outputs the footer record
        Footer_Extract()

        'Removes the blank lines from the file
        RemoveBlankLines()

        objOutputFile.Close()

        Dts.TaskResult = Dts.Results.Success

    End Sub

    Public Sub Header_Extract()

        'Reads the header file
        objTextFile = objFSO.OpenTextFile("\\folderb-001\Data\folderc\folder\Extracts\Header.txt", ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub Detail_Extract()

        'Reads the detail file
        objTextFile = objFSO.OpenTextFile("\\folderb-001\Data\folderc\folder\Extracts\Detail.txt", ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub Footer_Extract()

        'Reads the footer file
        objTextFile = objFSO.OpenTextFile("\\folderb-001\Data\folderc\folder\Extracts\Footer.txt", ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub RemoveBlankLines()

        'Remove blank lines from file
        objFile = objFSO.OpenTextFile("\\folderb-001\Data\folderc\folder\Extracts\PE.txt", ForReading)

        Do Until objFile.AtEndOfStream
            strLine = objFile.Readline
            strLine = Trim(strLine)
            If Len(strLine) > 0 Then
                strNewContents = strNewContents & strLine & vbCrLf
            End If
        Loop

    End Sub
	
	
	
	@[Dest_Dir] + "\\" + @[Dest_File] + "_" 
+ RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) 
+ RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) 
+ (DT_WSTR,4)DatePart("yyyy", GetDate()) 
+ ".csv"

Open in new window

0
Comment
Question by:aneilg
  • 2
2 Comments
 

Accepted Solution

by:
aneilg earned 0 total points
ID: 37000583
resloved
0
 

Author Closing Comment

by:aneilg
ID: 37000587
resloved
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question