Building a Windows Workflow SQL Server Integration Services Activity
Workflow Service: SSIS_Service
The ExecutePackage function in SSIS_Service starts SSIS Package execution. The important parts of the ExecutePackage function appear below.
public CurrentPackageStatus ExecutePackage(string packageId, IComparable queue) { SSIS_ServiceTrace.WriteLine("Executing " + packageId); SSIS_Package_Execution exec; SSIS_Package_ExecutionContext parms; Thread threadExec; CurrentPackageStatus status = CurrentPackageStatus.Missing; if (_packages.ContainsKey(packageId)) { parms = _packages[packageId]; //Once here, you want to be the only one changing this lock (parms) { if (_packages[packageId].IsExecuting) { status = CurrentPackageStatus.Executing; } else { parms.IsExecuting = true; parms.WorkflowInstance = WorkflowEnvironment.WorkflowInstanceId; parms.ResponseQueue = queue; exec = new SSIS_Package_Execution(parms, this); threadExec = new Thread(new ThreadStart(exec.Execute)); parms.CurrentRunningThread = threadExec; threadExec.Start(); status = CurrentPackageStatus.Started; } } } else { status = CurrentPackageStatus.Missing; SSIS_ServiceTrace.WriteLine("NOT FOUND " + packageId); } return status; }
A collection of SSIS_PackageExecution_Context classes maintains the execution information, including the thread executing the package and WorkflowQueue to notify when package execution has ended. Multiple steps in a workflow may be executing SSIS Packages so, to avoid race conditions, SSIS_Service locks the collection.
The details of SSIS package execution are wrapped in SSIS_PackageWrapper and SSIS_Package_Execution classes. I'm going to look at those next.
SSIS Package Execution
The SSIS_Package_Execution Execute function runs on a thread created in the SSIS_Service class. Execute appears below.
public void Execute() { SSIS_PackageWrapper package = new SSIS_PackageWrapper(); try { _parms.IsExecuting = true; _parms.ErrorStack = ""; _parms.ErrorLastRun = false; package.Open(_app, _parms.PackagePath, _parms.PackageName); package.Execute(); _service.ExecuteEnded(_parms.PackageID); _parms.IsExecuting = false; } catch (Exception ex) { SSIS_ServiceTrace.WriteLine("ERROR Execute " + _parms.PackageID + ex.Message + ex.StackTrace); _parms.ErrorStack = ex.Message + " " + ex.StackTrace; _parms.ErrorLastRun = true; _service.ExecuteEnded(_parms.PackageID); } } }
SSIS_PackageWrapper runs the SSIS package. A complete discussion of the SSIS Object model is beyond the scope of this article. You can view a complete discussion in the resources at the end of this article.
Executing an SSIS Package is straightforward. The SSIS_PackageWrapper Open and Execute functions appear below.
public void Open(Application app, string path, string packageName) { _path = path; _name = packageName; _app = app; _package = _app.LoadPackage(Path + "\\" + Name, null); } void Execute () { _package.Execute(); }
Page 3 of 4