SSIS File System Task rename

Posted on 2011-10-20
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
        'Outputs the detail record
        'Outputs the footer record

        'Removes the blank lines from the file


        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

    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

    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

    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

    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

Question by:aneilg

    Accepted Solution


    Author Closing Comment


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Shoutout to Emily Plummer ( for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
    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 dynamically set the form action using jQuery.
    The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now