none
How to get child package name in case of an Error while executing its Parent package through "PackageInfo" class (using C# code) in a script task of another SSIS package RRS feed

  • Question

  • Hi,

    Context:

    The Architecture of my project is:

    I am calling a Master package (which further calling child packages and those child packages further calling their child packages and so on) through C# code which is written in a script task of another SSIS package. Because my Master package is deployed using the Project deployment model in SQL Server 2012, I am calling this Master package through the "Microsoft.SqlServer.Management.IntegrationServices.PackageInfo" class by setting all the prerequisite values like "Catalog" and "CatalogFolder" etc.

    I able to execute my Master package (and its child packages) successfully.

    Problem Statement:

    How to get the name of Child package and its execution ID in case child package encounters any error while execution?

    By using "Microsoft.SqlServer.Management.IntegrationServices.ExecutionOperation.PackageName" property I always get the name of my Master package and not its child package name. I able to get other exception/error details using the "OperationMessage" class but not the child package name and its execution ID.

    Please see the attached code and let me know if there is any way through which I can get the name of the child package in which the error has occurred and its execution ID.

    public bool ExecutePackage()
            {
                bool returnFlag = false;            
                string pkgExecutionStatus = string.Empty;
                
    
                try
                {
                    string projectName;
                    string serverName;
                    string packageName;
                    string connectionString;
                    string folderName;
                    bool use32BitRunTime;
                    string connectionStringOLEDBTarget;
                    string dbName;
    
    
                    serverName = Convert.ToString(Dts.Variables["ServerName"].Value);
                    folderName = Convert.ToString(Dts.Variables["ForlderNameInIntServicesCatalog"].Value);
                    projectName = Convert.ToString(Dts.Variables["ProjectNameInIntServicesCatalog"].Value);
                    packageName = Convert.ToString(Dts.Variables["PackageNameToExecute"].Value);
                    dbName = Convert.ToString(Dts.Variables["DatabaseName"].Value);
    
                    connectionString = string.Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", serverName);
                    connectionStringOLEDBTarget = string.Format("Data Source={0};Initial Catalog={1};Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;", serverName, dbName);
    
                    //Create instance of Integration Services using the connection string
                    IntegrationServices integrationServices = new IntegrationServices(new System.Data.SqlClient.SqlConnection(connectionString));
                    
                    //Get the SSIDB catalog
                    Catalog catalog = integrationServices.Catalogs["SSISDB"];
    
                    //Get the folders inside the catalog
                    CatalogFolder catalogFolder = catalog.Folders[folderName];
    
                    //Get the SSIS package from the catalog --> Folder --> Project --> Package Name
                    PackageInfo package = catalogFolder.Projects[projectName].Packages[packageName];
    
                    //Set Project Parameter (proParamFeedID) using Collection for ExecutionValueParameterSet                
                    Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();
                    setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
                    {
                        ObjectType = 20,
                        ParameterName = "proParamFeedID",
                        ParameterValue = feedID
                    });
                    setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
                    {
                        ObjectType = 20,
                        ParameterName = "proParamConnStrOLEDB",
                        ParameterValue = connectionStringOLEDBTarget
                    });
    
                    //Set this variable to make the execution of Main.dtsx package synchronous. Otherwise it will get executed asynchronously
                    setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
                    { 
                        ObjectType = 50,
                        ParameterName = "SYNCHRONIZED", 
                        ParameterValue = 1                     
                    });
    
                    //Set package parameters here (e.g. paramFeedID)
                    package.Parameters["paramFeedID"].Set(ParameterInfo.ParameterValueType.Literal, feedID);
                    package.Alter();
    
                    //Use 32BitRuntime if running on 32 bit machine
                    use32BitRunTime = true;
                    //long executionIdentifier = package.Execute(use32BitRunTime, null, setValueParameters);
                    long executionIdentifier = package.Execute(false, null, setValueParameters);
                    //long executionIdentifier = package.Execute(false, null);                
    
                    //Outcome data can be extracted from the Execution object. 
                    ExecutionOperation execution = catalog.Executions[executionIdentifier];
    
                    //Get the SSIS package execution status for logging
                    pkgExecutionStatus = execution.Status.ToString();                
    
                    //Check for SSIS package execution status
                    switch (execution.Status)
                    {
                        case Operation.ServerOperationStatus.Canceled:
                            returnFlag = false;
                            break;
    
                        case Operation.ServerOperationStatus.Failed:
                            returnFlag = false;
                            //Get the child package execution errors.
                            GetChildPkgExecutionErrors(execution);
                            break;
    
                        case Operation.ServerOperationStatus.UnexpectTerminated:
                            returnFlag = false;
                            break;
    
                        case Operation.ServerOperationStatus.Stopping:
                            returnFlag = false;
                            break;
    
                        case Operation.ServerOperationStatus.Success:
                            returnFlag = true;
                            break;
    
                        default:
                            returnFlag = true;
                            break;
                    }
                   
                }
                catch(Exception ex)
                {
                    if (!string.IsNullOrWhiteSpace(errorDetails))
                        errorDetails = errorDetails + "<br> Some Error has occurred in the 'ExecutePackage()' method of the script task. Below are the details: <br>" + ex.ToString();
                    else
                        errorDetails = "Some Error has occurred in the 'ExecutePackage()' method of the script task. Below are the details: <br>" + ex.ToString();   
                    
                    returnFlag = false;
                }
    
                return returnFlag;
            }
    
            /// <summary>
            /// This method fetches any errors (if occurred) while executing the child package
            /// </summary>
            /// <param name="exectionOperation"></param>
            public void GetChildPkgExecutionErrors(ExecutionOperation exectionOperation)
            {
                
                StringBuilder errorMsg = new StringBuilder();
    
                errorMsg.AppendLine("Error Details of the Child package: <br>");
                errorMsg.AppendLine("Child package Execution Status: " + exectionOperation.Status.ToString() + "<br>");
    
                foreach (OperationMessage message in exectionOperation.Messages)
                {
                    string messageSourceType = string.Empty;
    
                    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;
                    }
    
                    
                    string messageType = "";
                    switch (message.MessageType)
                    {
                        case 120:
                            messageType = "Error";
                            errorMsg.AppendLine("Package Name: " + exectionOperation.PackageName + "<br>");
                            errorMsg.AppendLine("Message source type: " + messageSourceType + "<br>");
                            errorMsg.AppendLine("Message Type: " + messageType + "<br>");
                            errorMsg.AppendLine("Details: " + message.Message + "<br>");
                            errorMsg.AppendLine("<br>");
                            break;
    
                        case 110:
                            messageType = "Warning";
                            errorMsg.AppendLine("Package Name: " + exectionOperation.PackageName + "<br>");
                            errorMsg.AppendLine("Message source type: " + messageSourceType + "<br>");
                            errorMsg.AppendLine("Message Type: " + messageType + "<br>");
                            errorMsg.AppendLine("Details: " + message.Message + "<br>");
                            errorMsg.AppendLine("<br>");
                            break;
                       
                    }
                }
    
                if (!string.IsNullOrWhiteSpace(errorDetails))
                    errorDetails = errorDetails + "<br>" + errorMsg.ToString();
                else
                    errorDetails = errorMsg.ToString();
                        
            }
            

    Thanks.

    Prateek

    Sunday, May 3, 2015 9:43 PM

All replies

  • Hi Prateek,

    Id do it like this: either when setting to run the child package, get its executionid and relate to what failed in the log, or pass back to the parent package the name of the package that failed using an Event Handler.


    Arthur

    MyBlog


    Twitter

    Tuesday, May 5, 2015 5:09 PM
    Moderator
  • Hi Arthur,

    Yes, I agree that's one workaround and if I do not find any other solution I have to implement one variation of the same logic.

    But I wanted to know is there any class like "PackageInfo" Or "OperationMessage" or some other class exposed by Microsoft to get the same information in my Parent package itself as I able to get the exception details in my Parent package using the "OperationMessage" class.

    I think there should be one because when I generate "All Executions" reports from SSISDB catalog then in that it shows all the information like "Package Name", "Task Name", "Complete Execution Path".

    Thanks.

    Prateek


    Prateek

    Tuesday, May 5, 2015 5:22 PM