TopMenu

Stopping execution of SSIS package in SSMS

Sometimes it required to execute the packages from SSMS after deploying it to SSISDB catalog in SQL Server. But what if we want to interrupt the execution in the middle or when package stops the progress or hangs. Recently one of my colleague faced this issue and we somehow couldn’t find any quick solution to stop the running execution of package. We tried to restart integration services and other SQL Server services(Which was kinda silly).

Then I figured it out that it’s something related to SSISDB as we’re executing the package in it. When we execute any package then it launches a process ISServerExec.exe for every execution which is in running in the catalog. Now to see all those running packages in the Sql Server instance follow below steps:

1. Go to the SSISDB catalog in Sql Server management studio. Right Click and select the “Active Executions”. The SSISDB catalog can be found under Integration Services Catalogs as shown in below image:

image

image

Now a window will appear that will show the currently running packages. Now select your desired package your desired package you want to kill and click STOP.

image

Cheers!!! :)

Using NLOG for smart logging

NLog is a free logging platform for .NET, Silverlight and Windows Phone with rich log routing and management capabilities. It makes it easy to produce and manage high-quality logs for your application regardless of its size or complexity. When you don’t want to care about the archiving, log formats then NLog is your friend. Using just few configuration setting will enable everything for you. So let’s get started:
First download the appropriate version of Nlog that you’ll be using in your application. Now when you’ve the desired library of Nlog just add the reference to your project. Next thing you need to do is putting some configuration lines in the config file.
Register the NLog section in config -
<configuration>
  <configSections>
    <section name="nlog" type="NLog.Config.ConfigSectionHandler, NLog"/>
  </configSections>

Note: keep in mind you’re putting the config sections tag in the top of Configuration file otherwise you’ll get an exception “Can not have more than one config section in the configuration” something like that.
Next you need to specify the Nlog section to be defined. Below is the sample code configuration of Nlog required to enable the logging in your application.
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" throwExceptions="true"
        internalLogFile="c:\temp\nLog_internal_log_file.txt" internalLogLevel="Trace" internalLogToConsole="true">
    <targets async="true">
      <target name="file" xsi:type="File" keepFileOpen="false"
              fileName="C:\\Temp\\Test_Log.MyApplication.${shortdate}.txt"
              layout="${newline}${newline}${level}: ${message} ${exception:format=Message,StackTrace}"
              archiveFileName="C:\\Temp\\archives\Log.FaultDetectionSitePoller.{#####}.txt"
              lineEnding="Default"
              archiveAboveSize="1024000"
              archiveNumbering="Sequence"
              concurrentWrites="true"
              />
 
      <target xsi:type="Database" name="database">
        <connectionString>
          data source=LABPC02;initial catalog=SampleDatabase;Integrated Security=SSPI;
        </connectionString>
        <commandText>
          INSERT INTO Diagnostics
          (Severity
          ,Message
          ,StackTrace
          ,User
          ,MachineName
          )
          VALUES (  @severity
          , @message
          , @stacktrace
          , @User
          , @machinename
          )
 
        </commandText>
        <parameter name="@severity" layout="${level}"/>
        <parameter name="@message" layout="${message}"/>
        <parameter name="@machinename" layout="${machinename}"/>
        <parameter name="@User" layout="${windows-identity:domain=true}"/>
        <parameter name="@stacktrace" layout="${stacktrace}"/>
      </target>
    </targets>
    <rules>
      <logger name="*" writeTo="file"/>
      <logger name="*" minLevel="OFF" appendTo="database"/>
    </rules>
 
  </nlog>

In this section we have couple of things that I need to explain.

InternalLogFile – this is the file that Nlog will use for it’s own logging. It’ll help you troubleshooting any issue with NLog configuration with your project. It’s optional and not required if you don’t want to log status of Nlog’s internal stuff.

InternalLogToConsole – You can turn it on/off if your application can run as console application.

Targets – This is the section where you define the targets i.e. your logging will be done in file or database etc. The attribute async=’true’ will define the behavior of Nlog to be async when writing logs. so you don’t need to consider the cost of logging statements if you’re using the async write behavior. Which is a great feature that I liked most cause I’ve seen application consuming enough time/space for logging statements. In the given sample configuration we have file and database logging enabled in targets table.

Renderlayouts – What ever is written under the target tag in the curley braces is called as render layouts. Whatever I used are the internal defined templates by Nlog. You can also create your own templates. These templates are knows as RenderLayouts. So Nlog is providing already quite good list of RenderLayouts check them out here.

That’s pretty much that is required to do in configuration area. Now you need to write an wrapper class that you’ll be using in your project.

Here’s the complete code for the wrapper:
using System;
using NLog;

namespace MyLogger
{
    public static class Logger
    {
        private static readonly NLog.Logger _logger; //NLog logger
        private const string _DEFAULTLOGGER = "CustomLogger";

        static Logger()
        {
            _logger = LogManager.GetLogger(_DEFAULTLOGGER) ?? LogManager.GetCurrentClassLogger();
        }

        #region Public Methods
        /// <summary>
        /// This method writes the Debug information to trace file
        /// </summary>
        /// <param name="message">The message.</param>
        public static void Debug(String message)
        {
            if (!_logger.IsDebugEnabled) return;
            _logger.Debug(message);
        }

        /// <summary>
        /// This method writes the Information to trace file
        /// </summary>
        /// <param name="message">The message.</param>
        public static void Info(String message)
        {
            if (!_logger.IsInfoEnabled) return;
            _logger.Info(message);
        }

        /// <summary>
        /// This method writes the Warning information to trace file
        /// </summary>
        /// <param name="message">The message.</param>
        public static void Warn(String message)
        {
            if (!_logger.IsWarnEnabled) return;
            _logger.Warn(message);
        }

        /// <summary>
        /// This method writes the Error Information to trace file
        /// </summary>
        /// <param name="error">The error.</param>
        /// <param name="exception">The exception.</param>
        public static void Error(String error, Exception exception = null)
        {
            if (!_logger.IsErrorEnabled) return;
            _logger.ErrorException(error, exception);
        }

        /// <summary>
        /// This method writes the Fatal exception information to trace target
        /// </summary>
        /// <param name="message">The message.</param>
        public static void Fatal(String message)
        {
            if (!_logger.IsFatalEnabled) return;
            _logger.Warn(message);
        }

        /// <summary>
        /// This method writes the trace information to trace target
        /// </summary>
        /// <param name="message">The message.</param>
        public static void Trace(String message)
        {
            if (!_logger.IsTraceEnabled) return;
            _logger.Trace(message);
        }

        #endregion

    }
}

So the usage of this wrapper should be very simple as you will only need to write lines in the middle of your code.
Logger.Debug("Time elapsed: " + timeElapsed);

And you’re done with setting up the new generation logger with you application. If you need to turn off the logging for any target then you can use minLevel=”OFF” for that target.

for e.g.

<logger name="*" minLevel="OFF" appendTo="database"/>