CODEWITHSUNDEEP
c#ssis
serializer
serializer

Reputation: 1013

Monitor progress of SSISDB execution

I am using integration services reference in a c# project to execute a SSIS package. Is it possible to capture more verbose logging like when certain steps has been completed etc - similar to output you see in DTSEXEC?

Upvotes: 0

Views: 1027

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

As long as your application gets the exeuction id, you can query the catalog directly for messages. Also, SSISDB needs to have some form of logging turned on. "Basic" will get you something equivalent to the output of DTEXEC:

SELECT event_message_id
    , message_time
    , message
    , package_name
    , event_name
    , message_source_name
    , package_path
    , execution_path
    , threadID
FROM CATALOG.event_messages em
WHERE em.operation_id = 2 --execution id
ORDER BY event_message_id DESC

EDIT:

Here is the programmatic version of the same task:

using System;
using System.Text;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Data.SqlClient;
using System.Collections.ObjectModel;

namespace C_junk
{
    public class PackageExecuter
    {
        public string Catalog { get; set; }         
        public string Folder { get; set; }
        public string Project { get; set; }
        public string Package { get; set; } 
        public string SsisConnString { get; set; }

        public string Execute()
        {
            SqlConnection ssisServer = new SqlConnection(this.SsisConnString);
            var ssis = new IntegrationServices(ssisServer);
            var package = ssis
                    .Catalogs[this.Catalog]
                    .Folders[this.Folder]
                    .Projects[this.Project]
                    .Packages[this.Package];
            var setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();
            setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
            {
                ObjectType = 50,
                ParameterName = "SYNCHRONIZED",
                ParameterValue = 1
            });
            var executionId = package.Execute(true, null, setValueParameters);
            Catalog catalog = new Catalog(ssis);
            var execution = catalog.Executions[executionId];

            return GetMessages(execution.Messages);
        }

        private static string GetMessages(OperationMessageCollection messages)
        {
            StringBuilder logMessages = new StringBuilder();
            foreach (var message in messages)
            {
                string logMessage = String.Format(@"Message id: {0}. {1} - Type: {2}: {3}"
                                    , message.Id
                                    , message.MessageTime
                                    , GetMessageType(message.MessageType)
                                    , message.Message);
                logMessages.AppendLine(logMessage);
            }

            return logMessages.ToString();
        }

        private static string GetMessageType(short? typeId)
        {
            string messageType = "";
            switch (typeId)
            {
                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;
            }
            return messageType;
        }
    }
}

This class would be called like this:

   PackageExecuter pkg = new PackageExecuter();
    pkg.Catalog = "SSISDB";
    pkg.Folder = "<SomeFolder>";
    pkg.Project = "<SomeProject>";
    pkg.Package = "Package1.dtsx";
    pkg.SsisConnString = @"Data Source =<MyServer>; Integrated Security = SSPI;";
    string messages = pkg.Execute();

Note that the message type is returned as an integer and needs to be translated to text to be something meaningful. Also note that this method executes the package synchronously, so that the messages can be retrieved from the server when its execution completes. The Catalog class has a collection called executions. An execution has a messages collection which we can use to get a set of messages. The output from my test looks like this:

Message id: 106. 3/13/2017 8:23:09 PM +00:00 - Type: Pre-validate: Package1:Validation has started.
Message id: 107. 3/13/2017 8:23:09 PM +00:00 - Type: Pre-validate: Data Flow Task:Validation has started.
Message id: 108. 3/13/2017 8:23:09 PM +00:00 - Type: Information: Data Flow Task:Information: Validation phase is beginning.

Message id: 109. 3/13/2017 8:23:09 PM +00:00 - Type: Post-validate: Data Flow Task:Validation is complete.
Message id: 110. 3/13/2017 8:23:09 PM +00:00 - Type: Post-validate: Package1:Validation is complete.
Message id: 111. 3/13/2017 8:23:09 PM +00:00 - Type: Pre-execute: Package1:Start, 8:23:09 PM.
Message id: 112. 3/13/2017 8:23:09 PM +00:00 - Type: Post-execute: Package1:Finished, 8:23:09 PM, Elapsed time: 00:00:00.141.
Message id: 113. 3/13/2017 8:23:09 PM +00:00 - Type: Pre-execute: Data Flow Task:Start, 8:23:09 PM.
Message id: 114. 3/13/2017 8:23:09 PM +00:00 - Type: Pre-validate: Data Flow Task:Validation has started.
Message id: 115. 3/13/2017 8:23:09 PM +00:00 - Type: Information: Data Flow Task:Information: Validation phase is beginning.

Message id: 116. 3/13/2017 8:23:09 PM +00:00 - Type: Post-validate: Data Flow Task:Validation is complete.
Message id: 117. 3/13/2017 8:23:09 PM +00:00 - Type: Warning: Data Flow Task:Warning: Component "Derived Column" (2) has been removed from the Data Flow task because its output is not used and its inputs either have no side effects or are not connected to outputs of other components. If the component is required, then the HasSideEffects property on at least one of its inputs should be set to true, or its output should be connected to something.

Message id: 118. 3/13/2017 8:23:09 PM +00:00 - Type: Warning: Data Flow Task:Warning: Component "OLE DB Source" (8) has been removed from the Data Flow task because its output is not used and its inputs either have no side effects or are not connected to outputs of other components. If the component is required, then the HasSideEffects property on at least one of its inputs should be set to true, or its output should be connected to something.

Message id: 119. 3/13/2017 8:23:09 PM +00:00 - Type: Information: Data Flow Task:Information: Execute phase is beginning.

Message id: 120. 3/13/2017 8:23:09 PM +00:00 - Type: Post-execute: Data Flow Task:Finished, 8:23:09 PM, Elapsed time: 00:00:00.016.

Upvotes: 2

Related Questions