Tag Archives: CSharp

Executing your own .NET console application from SSIS (SSIS Series)

Introduction

The SSIS Script Task is a very powerful component to use in a SSIS package, and most of the time you can achieve with it what you want.
Still I have faced a few situations where a Script Task was not the best solution. In those cases I made a .NET Console application and executed it from within a SSIS package.

The full article is posted on SQLShack.com, so you can read further there ..

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

    I promise you the following:

    • I will not give or sell your email address to any third party.
    • I will not spam you.
    • If I start with a newsletter, you can easily unsubscribe or change the frequency.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    Implement logging in a Script Task (SSIS Series)

    Introduction

    This post is a follow-up on two older posts and will be the last one before my summer break.

    Today I want to enhance the SSIS package made in post Fixing corrupt csv files in a SSIS Package (SSIS Series) by using the Plug and Play Logging Solution.

    Problem

    As long as a script runs well, you might not need logging inside. BUT .. when something GOES wrong, having logging in the script can reduce the time needed to troubleshoot the issue.
    In the example for fixing csv files, anything can be wrong with a file, causing an error in the script. In that case it is for instance useful to know which file was being processed when the error occurred.

    Prerequisite

    For this to work, you have to install my Plug and Play Logging Solution, which can be downloaded from this post. It is just one SQL Script you have to run, that creates a [logdb] database with objects inside. You can also add those objects to a different database that already exists.
    It is however not mandatory to rebuild the Fixing corrupt csv files in a SSIS Package example, as you can also add logging to your own Script Task.

    Solution

    This post is a enhancement on the Fixing corrupt csv files in a SSIS Package post, so you might need to read that post first, if you did not do that already.

    Therefore I will dive directly into the changes that are needed to add logging in the Script Task.
    This will be done with lots of screenprints with some comment in between.

    SSIS-S01E07-169Adding a package parameter “LoggingConnectionString”
    First you need to add a package parameter LoggingConnectionString that can be used in an expression of the OLE DB Connection and as input variable for the Script Task.

    SSIS-S01E07-170OLE DB Connection configuration ..
    Then add an OLE DB Connection for the logging database.

    SSIS-S01E07-171OLE DB Connection properties ..
    Use an expression to let the ConnectionString of the OLE DB Connection be equal to the value of the package parameter LoggingConnectionString.

    SSIS-S01E07-173OLE DB Connection – Expression configuration [1]

    SSIS-S01E07-172OLE DB Connection – Expression configuration [2]

    SSIS-S01E07-174Final result
    By the fx icon you can see that the connection manager uses an expression.

    SSIS-S01E07-176Enable logging
    Now enable logging for the package.

    SSIS-S01E07-177Enable logging [2]
    Add a log provider for SQL Server and let it use the logdb.oledbConnection by selecting it under the Configuration column header.

    SSIS-S01E07-178Enable logging [3]
    Then select all events. Filtering on what is actually logged is done by the logging solution (by the value of @MaxMessageClass, see this blog post for more details).

    SSIS-S01E07-179Select the Script Task
    Select the Script Task and add the following Variables to ReadOnlyVariables:

    • System::ExecutionInstanceGUID
    • System::PackageID
    • $Package::LoggingConnectionString


    • SSIS-S01E07-180The added ReadOnlyVariables in the red rectangles

      Below you will find a number of screenprints of the script task to talk you through the changes.
      You can download the C# script here.

      SSIS-S01E07-181Overview
      First make sure the Namespaces region is as shown.
      Then fold the namespace with the guid in the name, and paste the entire namespace HansMichielsCom.PlugAndPlaySSISLoggingSolution underneath it.
      This code is put in a separate namespace, so that it could also be placed in a .NET assembly that is added to the GAC (Global Assembly Cache). When you would do this, you do not have to add the code to every Script Task.
      For the example of today, we just put this namespace inside the Script Task to make things not too complicated for now.

      SSIS-S01E07-182Using the HansMichielsCom.PlugAndPlaySSISLoggingSolution namespace
      As a result, you have to tell the first guid-like namespace, that you want to call code inside the second namespace. Therefore add the using statement as shown above.

      SSIS-S01E07-183Constant used for logging
      Below you will see some printscreens with changed parts in the script.

      SSIS-S01E07-184Method GetSsisLogWriter to instantiate a SsisLogWriter object

      SSIS-S01E07-187Method Main is extended with logging.

      SSIS-S01E07-188Pass the logWriter as parameter to other methods ..

      SSIS-S01E07-189IMPORTANT: Bugfix in CheckAndReturnHeader!

      SSIS-S01E07-190IMPORTANT: Bugfix in CheckAndReturnHeader!
      (header == null) is added to cope with empty files.

      Testing, testing, one, two ..



      SSIS-S01E07-191Test preparations [1]

      SSIS-S01E07-193Test preparations [2]

      SSIS-S01E07-194Test execution

      SSIS-S01E07-195Test result: logging rows done inside the script are in the log table.

      Conclusion / Wrap up

      In this post I have demonstrated how to implement logging in SSIS Script Tasks using my Plug and Play Logging Solution.
      This type of logging gives more control on what to log and how to log it than when you implement logging using SSIS events.
      The examples given are very basic. You can use your imagination to implement logging of errors using a try .. catch block, or use all available parameters of logWriter.AddLogEntry to change the Retention Class, Message Class, and so on.

      In the summer I will take some time for study, reflection, holiday, and still .. work.
      My next post will be early September at the latest, maybe earlier.

      Download the C# script here.

      (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.