Limit SSIS Package Execution At Any One Time

           It is a common requirement to insure that only a single instance of an SSIS package can be running at one time.  This would appear to be simple and my first idea was to check to see if a package assigned to an SSIS step in a SQL agent job is running by querying the msdb database.  OOOOPPPPPSSSS!!  I forgot!  SSIS packages can be run using several methods including SQL Server agent.  What if a job is being run from an SSIS package, batch file, and directly from the command line?  The msdb database, and even the SSIS catalog for SSIS 2012, does not capture when a package is run outside of a SQL Server agent job. 

               After some consideration I initially considered using .NET to query the running services looking for DTExec and attempt a WMI query to obtain the running package, but I realized that I was making this much harder on myself.  In the Microsoft.SqlServer.Dts.Runtime.Package.Applcation namespace there is a method, GetRunningPackages, which can be used to return metadata about each running package on the server/workstation in which the method is run. 

               To demonstrate how this works create a package that contains a package scoped variable called IDCount that is an integer data type.  Drop a script task onto the control flow design surface that uses assigns the IDCount variable as ReadWrite and the System:PackageID as a Read variable. 

              My current code sample uses VB.NET, but I will update this to include C# in the near future.  The script task code will utilize the GetRunningPackages method to iterate through all running packages and when the packageid of the running packages matches the current packageid then the IDCount variable will be incremented by one.
Public Sub Main
‘ Declare and instantiate objects here.
Dim app As Application = New Application‘…
‘ Create a RunningPackages collection, named pkgs, and fill it
‘ with the running packages from the application object.
Dim pkgs As RunningPackages = app.GetRunningPackages(Nothing)

‘ Enumerate over each package in the collection and display some data.
For Each package As RunningPackage In pkgs
If package.PackageID.ToString().ToUpper = Dts.Variables(“PackageID”).Value.ToString().Replace(“{“, “”).Replace(“}”, “”) Then
Try
Dts.Variables
(“IDCount”).Value += 1
Catch ex
As Exception
MessageBox.Show
(ex.Message.ToString())
End Try

End If

Next
MessageBox.Show(“There are ” + Dts.Variables(“IDCount”).Value.ToString() + ” running”)
Dts.TaskResult = ScriptResults.Success
End Sub

           The short translation is that a new DTS application and runningpackages is instantiated in the code and then, using a for each loop, the packageids are retrieved and compared against the current packageid.  You will notice that the Replace and ToUpper functions are applied to insure that the package GUIDs match when compared.  If the packageid’s are equal then the IDCount variable is incremented by one. 

          The script task acts as the gateway to the package and a precedent constraint can then be used to insure that if the package is already running the package exits.  In order to exhibit how this works drop another script task onto the control flow design pane that accesses the IDCount as a read only variable.  In the code of the task simply call a messagebox show method to display the IDCount:
MessageBox.Show(“There is ” + Dts.Variables[0].Value.ToString() + ” instance of this package running”;

           Using a precedent constraint from the gateway script task to one or all other tasks you can evaluate the IDCount to insure that the package is not currently running, if the IDCount is > 1 then there are multiple instances of the same package running.

PrecConIDCount

          When the package is executed the first script task evaluates what packages are running and compares the packageid’s.  The IDCount will always be at least one, but if more the precedent constraint evaluates as to whether the next step(s) should be execute.  If the count is not equal to one than the package stops at the precedent constraint.

          The sample package referenced in this example can be downloaded here.

Comments ( 5 )

  1. Replydwitham2013

    Where is the C# version? This is exactly what I need now.

  2. Replymfiore

    public void Main() { // TODO: Add your code here Dts.Variables["User::CountRunning"].Value = CountPackages(); Dts.TaskResult = (int)ScriptResults.Success; } public Int32 CountPackages() { Int32 nRet = 0; string sPackageID = Dts.Variables["System::PackageID"].Value.ToString().ToUpper().Replace("{","").Replace("}",""); Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application(); Microsoft.SqlServer.Dts.Runtime.RunningPackages r = a.GetRunningPackages(null); foreach (Microsoft.SqlServer.Dts.Runtime.RunningPackage p in r) { if (p.PackageID.ToString().ToUpper() == sPackageID) { nRet += 1; } } return nRet; }

  3. Replymfiore

    public Int32 CountPackages() { Int32 nRet = 0; string sPackageID = Dts.Variables["System::PackageID"].Value.ToString().ToUpper().Replace("{","").Replace("}",""); Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application(); Microsoft.SqlServer.Dts.Runtime.RunningPackages r = a.GetRunningPackages(null); foreach (Microsoft.SqlServer.Dts.Runtime.RunningPackage p in r) { if (p.PackageID.ToString().ToUpper() == sPackageID) { nRet += 1; } } return nRet; }

  4. Replymfiore

    here is some working c# hope it helps Int32 nRet = 0; string sPackageID = Dts.Variables["System::PackageID"].Value.ToString().ToUpper().Replace("{","").Replace("}",""); Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application(); Microsoft.SqlServer.Dts.Runtime.RunningPackages r = a.GetRunningPackages(null); foreach (Microsoft.SqlServer.Dts.Runtime.RunningPackage p in r) { if (p.PackageID.ToString().ToUpper() == sPackageID) { nRet += 1; } } return nRet;

  5. Replymfiore

    //Int32 nRet = 0; //string sPackageID = Dts.Variables["System::PackageID"].Value.ToString().ToUpper().Replace("{","").Replace("}",""); //Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application(); //Microsoft.SqlServer.Dts.Runtime.RunningPackages r = a.GetRunningPackages(null); //foreach (Microsoft.SqlServer.Dts.Runtime.RunningPackage p in r) //{ // if (p.PackageID.ToString().ToUpper() == sPackageID) // { // nRet += 1; // } //} //return nRet;

Leave a Reply


Hit Counter provided by Sign Holders