SSIS Master or Parent Package Handling Child Errors

When you are using a master package to load files into the appropriate child packages, how can you allow the master package to continue even if the child has an error like a bad input file? You can increase the Maximum error count on the master package, but this opens you to other errors slipping by. To accomplish this I created a master package and let the child packages create an error file. Then the master package will check for the existance of the error file. If you have any questions about this article please let me know.

Here 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. There are no task in the sequecne container.

The expressions check the file name variable. Based on the file name variable 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 created by the child packages. It also deletes the error file and moves the input data file that had the error to a backup folder.

Here 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.

Here 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. This makes it easy to update any variable. If you update the master package variables, the variables in the child package will use the new values automatically.

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.

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 if the file has a bad row. The code in the script task is in the pane to the left. I also set the read only variables to strChildErrorFile, strFileExt, and strInputFolder.

This code will create a file that the master package will check for to see if there was an error. Notice I am using a FileStream 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 transactions on the Child packages.

    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 occured
        Dim fs As FileStream = File.Create(badfile)
        fs.Close()

        '
    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 into VB script variables.

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.

    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