SSIS File System Task rename

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

aneilgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aneilgAuthor Commented:
resloved
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aneilgAuthor Commented:
resloved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.