SSIS Child Errors in Parent Package

If you have a parent package that is looping through a set of files and calling child packages and want the parent package to continue even if the child package fails, there is a little work involved in making this happen.
In this example I have a parent package that loops through a collection of files & calls the appropriate child package (out of 2 child packages). I want to abort/exit the child package without aborting the parent package also. As soon as 1 error is found, I need to exit the child package and move the file to an error directory so the parent loop won’t be interrupted.

I created a master package and two child packages to demonstrate how I would do this. I will walk through the master set up and one of the child packages. Each child package is identical. I am going to explain how I would call each child package also. I will also cover the error issue.



      Above is the Master or Parent package. I have a For Each Loop loping through a set of files. The first item in the For Each Loop is a Sequence Container. This is used as an expression anchor. This allows you to decide which child package to run based on the value of the expressions. The expressions just check the file name. Based on the file name it will run the appropriate child package. After the child packages there is a script task that checks for the error file that may have been create by the child packages. It also deletes the error file and moves the data file that had the error to a backup folder.


      Above are the variables in the master package. Notice we are trying to make the package as dynamic as possible. Nothing is hard coded. If any of these items change it is a simple change to a variable to update the package. This can be done from configuration files also. The variable strFileName is going to be filled with the For Each Loop. Everything else is as shown.



      The For Each Loop in the master package is set up to use the variables in the expressions of the collection. Notice it is only getting the name of the file. The folder and extension are in the variables. The file name is mapped to the variable strFileName


      Above is the child package configuration. I have placed all the same variables in the child package and used parent configurations to pass all of the values to the child package.


      The child package consists of only one data flow. The input file name is set dynamically with an expression on the connection string mapped to the variables. The expression is:

@[User::strInputFolder] +  @[User::strFileName] +  @[User::strFileExt]

This combines all of the proper variables to give the package the location of the current file found by the for each loop in the master package.

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ‘
        ‘Create script variables to hold package variable values
        Dim strFileExt As String = Me.Variables.strFileExt
        Dim strChildErrorFile As String = Me.Variables.strChildErrorFile
        Dim strInputFolder As String = Me.Variables.strInputFolder
        Dim badfile As String = strInputFolder + strChildErrorFile + strFileExt

        ‘create file to indicate an error I
        Dim fs As FileStream = File.Create(badfile)
        fs.Close()

        ‘
    End Sub

    On the source in the data flow I have set all of the errors and truncation to redirect to the error output. This will cause the script tasks to run. The code in the script task is above. I also set the read only variables to strChildErrorFile, strFileExt, and strInputFolder.

This code will create a file that the master package will be able to see to tell there was an error. Notice I am using a file stream to create the file and I close the FileStream. If you do not do this you will get an error in the master package saying the file is in use in another process.

If you want the file to be an all or none load, set up a transaction on the Child package.

    Public Sub Main()

        ‘create script variable to hold package variable values
        Dim strBackupFolder As String = Dts.Variables(“strBackupFolder”).Value
        Dim strChildErrorFile As String = Dts.Variables(“strChildErrorFile”).Value
        Dim strFileExt As String = Dts.Variables(“strFileExt”).Value
        Dim strFileName As String = Dts.Variables(“strFileName”).Value
        Dim strInputFolder As String = Dts.Variables(“strInputFolder”).Value

        ‘create error file and backup file full file names
        Dim strErrorFile As String = strInputFolder + strChildErrorFile + strFileExt
        Dim strInputFile As String = strInputFolder + strFileName + strFileExt
        Dim strBackupFile As String = strBackupFolder + strFileName + strFileExt

        ‘check for the child error file, move bad file if it exist
        If File.Exists(strErrorFile) Then
            File.Move(strInputFile, strBackupFile)
            File.Delete(strErrorFile)
        End If

        Dts.TaskResult = ScriptResults.Success
    End Sub

    Back in the master package there is a script task after the child package that will check for the existence of the bad file. This code is in the left pane.
The top section is just saving all of the package variables in to VB script varables.

The second section is combining some of these variables to get the file names.
The If Then section is checking for the existence of the error file that might have been created by the child package. If the error file does exist it moves the input file to the bad files folder and deletes the error file.
This allows the master package to go to the next file in the loop and allows the next child package to create a new error file if needed.