Friday 20 June 2014

SSIS 2012 Execute Package Task: External Reference does not work with SSISDB

Case
I have two SSIS projects (both project deployment) and I within project 'A' I want to execute a package from project 'B' with the Execute Package Task. Project reference won't work because there are two different projects, but External reference won't work either because it doesn't support packages from the SSISDB (only file or MSDB).
Pointing to MSDB instead of SSISDB























Solution
See (/vote) this MsConnect item. They are still considering to address this issue. Here is a workaround, but it requires a little coding. I while a go I did a post on executing an SSIS 2012 package from a .Net application and I thought that it would also be possible within an SSIS Script Task.

1) Connection Manager
Create an ADO.Net connection manager that points to the SSISDB on your server.
ADO.Net connection (don't use OLEDB)























2) String variable
Create a string variable and add the path from the package that you want to execute. Format is /SSISDB/folder/project/package.dtsx
String variable filled with package path
















3) Script Task
Add a Script Task to the Control Flow and give it a suitable name. Then edit the Script Task and add the string variable from step 2 as readonly variable.
Script Task - ReadOnlyVariables


























4) The Script - References
Choose the Scripting Language and hit the Edit Script button. We need to reference 4 assemblies, but they are not in the SQL Server folder. They are only available in the GAC. The path varies a little per computer. These are mine:
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll


Right click references in the Solution Explorer and choose Add Reference... Then browse these four dll files and add them one by one.
Add references

























IMPORTANT: After adding the references you should press the Save All button to save the reference changes!

5) The Script - Code
Now copy the usings(/imports) from my code and copy the content of my Main method to your main method. The example is in C#, but you can use this translator to get VB.Net code
// C# Code
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
// Added:
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;
#endregion

namespace ST_e71fdb73f68c4a3f9595ea5d37464a62
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
        public void Main()
  {
            // Boolean variable for firing event messages
            bool fireAgain = true;

            // Execution of child package starting
            Dts.Events.FireInformation(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Starting", string.Empty, 0, ref fireAgain);

            try
            {
                // Connection to the database server where the packages are located
                // SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=SSISDB;Integrated Security=SSPI;");
                SqlConnection ssisConnection = new SqlConnection(Dts.Connections["mySqlServerAdoNet"].ConnectionString);
                                
                // SSIS server object with connection
                IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
                
                // Split the variable containing the package path in smaller
                // parts: /SSISDB/Folder/Project/Package.Dtsx 
                string[] SSISDBPackagePath = Dts.Variables["User::SSISDBPackagePath"].Value.ToString().Split('/');

                // The reference to the package which you want to execute
                // Microsoft.SqlServer.Management.IntegrationServices.PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["folder"].Projects["project"].Packages["package.dtsx"];
                Microsoft.SqlServer.Management.IntegrationServices.PackageInfo ssisPackage = ssisServer.Catalogs[SSISDBPackagePath[1]].Folders[SSISDBPackagePath[2]].Projects[SSISDBPackagePath[3]].Packages[SSISDBPackagePath[4]];

                // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
                Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet>();
                executionParameter.Add(new Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });

                // For adding more parameters go to my WIKI post on MSDN:
                // http://social.technet.microsoft.com/wiki/contents/articles/21978.execute-ssis-2012-package-with-parameters-via-net.aspx

                // Get the identifier of the execution to get the log
                long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

                // If you want to catch the events from the package you are executing then you can add this
                // foreach loop. It reads the events and fires them as events. You can remove this loop if
                // you're not interested in them.

                // Loop through the log and fire events
                foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
                {
                    // Translate Message Source Type code and Message Type code to description. See
                    // MSDN for the complete list http://msdn.microsoft.com/en-us/library/ff877994.aspx

                    string messageSourceType = "";
                    switch (message.MessageSourceType)
                    {
                        case 10:
                            messageSourceType = "Entry APIs, such as T-SQL and CLR Stored procedures";
                            break;
                        case 20:
                            messageSourceType = "External process used to run package (ISServerExec.exe)";
                            break;
                        case 30:
                            messageSourceType = "Package-level objects";
                            break;
                        case 40:
                            messageSourceType = "Control Flow tasks";
                            break;
                        case 50:
                            messageSourceType = "Control Flow containers";
                            break;
                        case 60:
                            messageSourceType = "Data Flow task";
                            break;
                    }

                    // Translate Message Type (=event)
                    string messageType = "";
                    switch (message.MessageType)
                    {
                        case -1:
                            messageType = "Unknown";
                            break;
                        case 120:
                            messageType = "Error";
                            break;
                        case 110:
                            messageType = "Warning";
                            break;
                        case 70:
                            messageType = "Information";
                            break;
                        case 10:
                            messageType = "Pre-validate";
                            break;
                        case 20:
                            messageType = "Post-validate";
                            break;
                        case 30:
                            messageType = "Pre-execute";
                            break;
                        case 40:
                            messageType = "Post-execute";
                            break;
                        case 60:
                            messageType = "Progress";
                            break;
                        case 50:
                            messageType = "StatusChange";
                            break;
                        case 100:
                            messageType = "QueryCancel";
                            break;
                        case 130:
                            messageType = "TaskFailed";
                            break;
                        case 90:
                            messageType = "Diagnostic";
                            break;
                        case 200:
                            messageType = "Custom";
                            break;
                        case 140:
                            messageType = "DiagnosticEx";
                            break;
                        case 400:
                            messageType = "NonDiagnostic";
                            break;
                        case 80:
                            messageType = "VariableValueChanged";
                            break;
                    }

                    // Fire event depending on the message type (event) in the child package. Since there are event types that you
                    // can't fire from a Script Task, we need to 'translate' them. For example a TaskFailed event is fired as an
                    // error event. More info see: http://microsoft-ssis.blogspot.com/2011/02/script-task-and-component-logging.html
                    switch (message.MessageType)
                    {
                        case -1:    // Unknown
                        case 120:   // Error
                        case 130:   // TaskFailed
                            Dts.Events.FireError(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0);
                            break;
                        case 110:   // Warning
                            Dts.Events.FireWarning(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0);
                            break;
                        default:
                            Dts.Events.FireInformation(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0, ref fireAgain);
                            break;
                    }
                } // END FOREACH LOOP

                if (ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Status == Operation.ServerOperationStatus.Success)
                {
                    // Execution of child package succeeded
                    Dts.Events.FireInformation(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Succeeded", string.Empty, 0, ref fireAgain);
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                else
                {
                    // Execution of child package failed
                    Dts.Events.FireError(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "There may be error messages posted before this with more information about the failure.", string.Empty, 0);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

            }
            catch (Exception ex)
            {
                // Execution of child package failed (server timeout, can't find package, etc.)
                Dts.Events.FireError(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Failed: " + ex.Message, string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}


6) The result
Now you can execute the package and see the result. You could add some filtering in the message loop to reduce the number of messages.
The result in Visual Studio



















Note 1: The other project already needs to be deployed to the SSIS Catalog
Note 2: You get two executions with both their own ServerExecutionId
Two executions






If you want to pass parameters to the child package then you could check out my WIKI example on parameters.

There are alternatives like executing a SQL Server Agent job via an Execute SQL Task or Calling DTExec via an Execute Process Task.

* update: 30 second timeout workaround *

6 comments:

  1. Very nicely done! We are investigating a Master Package concept and this is very helpful. Thanks!

    ReplyDelete
  2. Is it possible to get and set package variables using this method?

    ReplyDelete
  3. Is it a bug or a feature? I'm reconsidering SSISDB because of this.

    ReplyDelete
    Replies
    1. It seems to be a feature. Please vote on the MsConnect suggestion! And spread the word :-)

      Delete
  4. Fantastic article, very helpful and very well commented. Puts my online contribution to coding to shame. I will try and up my game to match.

    Shame the native external package execution doesn't work but I have a need now so this was awaesome.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...