A Polished Way to Run SSIS Child Packages With a Script Task

Execute package script task

The SSIS Execute Package task works well for running child packages from a master package, and in many cases using it will fit your needs. But what if you are looking for something more powerful? Maybe you want to dynamically populate variables in the child package without dealing with master/child package configurations and their restrictions. Maybe you want to check the states of some of the variables in the child package after it has been run to decide if the master package should consider it a passed or failed load. There are many reasons why you might choose to use a script task to execute a child package programmatically. Read on to learn a polished way to use a script task to execute a child SSIS package and an example of a good use case for choosing to do so.

Use Case

Let’s say you have a process where you need to get a file from a FTP server and load it into your data warehouse. You want to make your process fault-tolerant, so that if it fails for some reason in most cases you will simply need to rerun it. To accomplish this, you want to pull the file from the FTP server at the beginning, do all of your file loading and processing to get the data into your data warehouse, and then when you’re sure everything has loaded without errors you want to go delete the file off of the FTP server.

You’ve decided to use a child package to perform the FTP operation and to load the data from the file into a staging table. From there, your master package will use the staging table to load the data into the appropriate locations in your data warehouse. You could then go and call another separate package or something to go back and delete the file off of the FTP server. But your first child package already contains all of the FTP configuration information you need, so why not try to find a way to re-use it?

Here’s the idea: Set up your FTP package to run differently depending on the value of a variable, let’s call it RunMode. Do whatever initial setup and logging you normally do at the beginning of your packages, and then conditionally split based on the value of the variable. In this example, one “Main” branch will pull the file off of the FTP server and load it into a staging table. Another “Cleanup” branch will delete the file off of the server and maybe archive your local copy of the file and perform some other cleanup operations.

Execute package script task child example

Controlling the two branches of this FTP child package is simple using the script task to execute our package. When you run the child package the first way, you add a line of code to set the RunMode variable to “Main”. The next time you run the child package, you pass the value “Cleanup” instead.

Execute package script task master example


The Main Script Task Method

Let’s start by investigating the ‘Main’ method of the script task for executing a child SSIS package.

	///////////////////////////////////////////////////////////////////////////////////////////////////////////////
	// Main
	//
	public void Main()
	{
		// Assume success unless something later on says otherwise.
		Dts.TaskResult = (int)ScriptResults.Success;

		// Set up some vars to identify what child package we want to run.
		String packageCM = "MyChildSSISPackage.dtsx";

		String packageLocation = Dts.Connections[packageCM].ConnectionString;
		Package oPkg;
		Microsoft.SqlServer.Dts.Runtime.Application oApp = new Microsoft.SqlServer.Dts.Runtime.Application();
		ChildEventListener eventListener = new ChildEventListener(Dts);

		// Load child package.
		// LoadPackage = file, LoadFromDtsServer = package store, or LoadFromSqlServer = SQL package sources.
		oPkg = oApp.LoadPackage(packageLocation, eventListener);

		// Set child package variables.
		// NOTE: Package configuration variables are set at package execution time, so make sure the variables
		//       you're setting here aren't going to be overwritten by a package configuration.
		WriteVariable("User::RunMode", "Main", oPkg);
		PkgVarsSet(oPkg, "User::loadYear,User::loadMonth");

		// Execute child package.
		Dts.TaskResult = (int)oPkg.Execute(null, null, eventListener, null, null);

		if (Dts.TaskResult != (int)DTSExecResult.Failure) {
			// Maybe you want to pull some result variables from the child package back into the master here.
			PkgVarsGet(oPkg, "User::rowCountInserted,User::statusType,User::statusMessage");
		}
		else {
			// Perform extra checks or override settings here as necessary.
			// Ex: You may not necessarily want to fail the script task if the child package failed.
			// Dts.TaskResult = (int)DTSExecResult.Success;
		}

	}

There’s not a great deal to it. First, you set up a few things to identify that you will be running a package and what package you will be running. In this example the package we want to run is set up in a connection manager, so we pull the info from there. You could just as easily use a package name and a package path you keep in a variable by doing something like this:

	String loadPackage = (String)ReadVariable("User::packagePathBase")
		+ "MyChildSSISPackage.dtsx";

In this example we then pass some data into the child package’s variables and run the package. Finally, we do a few additional activities depending on the results of the child package’s execution.

As you can see, it’s pretty simple to follow. If you use this script task method often to run child packages, this is where you’ll be making changes to run specific child packages, dealing with variables and child package failure handling, and so on.

You’ll notice a couple of objects in this Main method that aren’t normal SSIS calls: the <strong>ChildEventListener</strong> and the way <strong>variables</strong> are used. These two items are covered in more detail below.

A Child Event Listener

I’ve used script tasks to call child SSIS packages for quite some time. One thing that always bugged me was that when I ran a child package using this method, figuring out child package failures became more difficult. When running in Visual Studio, or as a SQL agent job, from the command line using DTExec or via another runtime tool, if the child package failed you’d see an error message that the script task failed but you wouldn’t know what in the child package caused it to fail. Sure, if your packages are set up with good audit logging you should be able to discover in your logs what went wrong, but it’s nice to be able to get a better idea of the error message right in whatever tool you’re running your packages in.

Creating a custom event listener overcomes this problem. You can make your custom event listener to whatever you want when it captures a message. Microsoft uses an example where it writes a message to the console when an error message occurs.

Console messages are OK, I’ve used them in certain scenarios for debugging purposes and they’ve worked well. But you probably won’t be capturing console messages in your production environment when you run packages. What I have chosen to do is create an event listener that basically re-throws all of the messages the child package generates in the parent. By doing this we will be able to see in the normal error message location of our runtime tool all of the child error messages.

    #region ChildEventListener - A custom event listener to re-throw child package events in the master.
    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////
    // ChildEventListener
    //   A custom EventListener that re-throws child package events in the master package.
    //   Useful when running a master package for identifying why a child package failed in the error
    //   messages viewable in visual studio, SQL agent job history log file viewer, etc.
    //
    class ChildEventListener : DefaultEvents {
        // The constructor
        // Takes a dts as an argument so errors can be re-thrown against the currently running dts.
        Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel dts;
        public ChildEventListener(Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel dts) {
            this.dts = dts;
        }

        public override bool OnError(DtsObject source, int errorCode, string subComponent, string description
                                        , string helpFile, int helpContext, string idofInterfaceWithError) {
            dts.Events.FireError(errorCode, subComponent, description, helpFile, helpContext);
            // Continue executing the task even though an error was triggered.
            return false;
        }

        public override void OnInformation(DtsObject source, int informationCode, string subComponent
                                            , string description, string helpFile, int helpContext
                                            , string idofInterfaceWithError, ref bool fireAgain) {
            dts.Events.FireInformation(informationCode, subComponent, description, helpFile, helpContext, ref fireAgain);
        }

        public override void OnWarning(DtsObject source, int warningCode, string subComponent, string description
                                        , string helpFile, int helpContext, string idofInterfaceWithError) {
            dts.Events.FireWarning(warningCode, subComponent, description, helpFile, helpContext);
        }
    }
    #endregion //ChildEventListener - A custom event listener to re-throw child package events in the master.

DTS Variable Handling Functions

One thing I don’t really like about the script task in SSIS is the ReadOnlyVariables and ReadWriteVariables fields that are built into the GUI. I mean, it’s a script task where you’re going to be doing a bunch of coding, right? So why should you have to select a bunch of variables out of a GUI selection box that you plan on using within your code?

The good news is, you don’t have to. Methods are available to access your SSIS variables directly with code. You can use these methods to read or write to variables as you wish, without having to have anything selected in the GUI portion of the script task. Moreover, because we’re speaking about running child packages in this article, it’s good to know that you can also read from and write to variables in a child package that you have set up to run using a script task.

Directly accessing SSIS variables with code isn’t that difficult, but by including a few functions in our script tasks we can make it even easier. I usually include the following functions in script tasks where I plan on using variables:

	#region DTS Variable handling functions
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////
	// Some functions for handling Dts variables
	//   WITHOUT having to add them to the ReadOnlyVariables and ReadWriteVariables
	//   GUI fields in the Script Task Editor.
	//

	// Read a variable in the currently running DTS.
	private object ReadVariable(string varName) {
		object result;
		try {
			Variables vars = null;
			Dts.VariableDispenser.LockForRead(varName);
			Dts.VariableDispenser.GetVariables(ref vars);
			try {
				result = vars[varName].Value;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				vars.Unlock();
			}
		}
		catch (Exception e) {
			throw e;
		}

		return result;
	}

	// Read a variable in a package.
	private object ReadVariable(string varName, Package pkg) {
		object result;
		try {
			Variables vars = null;
			pkg.VariableDispenser.LockForRead(varName);
			pkg.VariableDispenser.GetVariables(ref vars);
			try {
				result = vars[varName].Value;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				vars.Unlock();
			}
		}
		catch (Exception e) {
			throw e;
		}

		return result;
	}

	// Write to a variable in the currently running DTS.
	private bool WriteVariable(string varName, object varValue) {
		try {
			Variables vars = null;
			Dts.VariableDispenser.LockForWrite(varName);
			Dts.VariableDispenser.GetVariables(ref vars);
			try {
				vars[varName].Value = varValue;
			}
			catch (Exception e) {
				System.Windows.Forms.MessageBox.Show(e.Message);
				throw e;
			}
			finally {
				vars.Unlock();
			}
		}
		catch (Exception e) {
			System.Windows.Forms.MessageBox.Show(e.Message);
			throw e;
		}
		return true;
	}

	// Write to a variable in a package.
	private bool WriteVariable(string varName, object varValue, Package pkg) {
		try {
			Variables vars = null;
			pkg.VariableDispenser.LockForWrite(varName);
			pkg.VariableDispenser.GetVariables(ref vars);
			try {
				vars[varName].Value = varValue;
			}
			catch (Exception e) {
				System.Windows.Forms.MessageBox.Show(e.Message);
				throw e;
			}
			finally {
				vars.Unlock();
			}
		}
		catch (Exception e) {
			System.Windows.Forms.MessageBox.Show(e.Message);
			throw e;
		}
		return true;
	}

	// Write to variables in a package the value of the like-named variables in the currently running DTS.
	private bool PkgVarsSet(Package pkg, String varList) {
		string[] userVariableArray;
		userVariableArray = varList.Split(',');
		foreach (string s in userVariableArray) {
			WriteVariable(s, ReadVariable(s), pkg);
		}
		return true;
	}

	// Write to variables in the currently running DTS the value of the like-named variables in a package.
	private bool PkgVarsGet(Package pkg, String varList) {
		string[] userVariableArray;
		userVariableArray = varList.Split(',');
		foreach (string s in userVariableArray) {
			WriteVariable(s, ReadVariable(s, pkg));
		}
		return true;
	}
	#endregion //DTS Variable handling functions

Putting it all Together

It can be frustrating to see bits and pieces of something you want to implement, and things don’t work right when you try to cobble the individual elements together. I don’t want that to happen to you, so here is a complete sample script to download that you can work with. I typically have a template SSIS script task with this code in it saved somewhere. When I need to use a script task I’ll just copy the template task, paste it into the package I’m working on, and modify the ‘Main’ method to do whatever I need it to.

Conclusion

In this article we discussed a simple, polished method for running child SSIS packages programmatically using a script task. Using script tasks to run packages can simplify parent/child variable operations and offers opportunities for child package re-use. If you’re looking to use a script task to call child packages, I hope you found this article useful. Try out these methods and let us know how it worked for you.

Leave a Reply

Your email address will not be published. Required fields are marked *