Hans Michiels

Executing your own .NET console application from SSIS

April 24, 2017 by

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.

Reasons you might want to do this also include:

  • You want to use the functionality in multiple SSIS packages and do not want to copy (the code inside a) a Script Task over and over again.
  • Your C# application becomes more complex than a single script, and developing it outside the SSIS package allows you to create it with a better structure (tiers, namespaces, classes).
  • You do not want to (or company policy does not allow you to) register an .NET assembly in the Global Assembly Cache of a server on which SQL Server/SSIS is installed.

Both sides of the story: Console app and SSIS Package

To demonstrate you how this works I have to tell you both sides of the story: first how to develop your .NET console application to accept command line parameters and to return feedback and errors, and then how to call this console application in a SSIS Package and intercept the return code and (error) messages.

Side A: develop the .NET Console application

For developing the console application I used Visual Studio 2017 Community Edition.

As this post is not about any specific application, I have made a generic example, in which the console application (which I have called “ExampleConsoleApp”, sorry for that, no inspiration for a more interesting name) accepts three required string parameters and one optional boolean parameter.

If you want to rebuild the example, just follow the steps:

To create a new empty Console application in Visual Studio select File > New > Project from the menu. Then from the project templates, choose

Installed > Templates > Visual C# > Windows Classic Desktop > Console App (.NET Framework)

when using Visual Studio 2017 or

Installed > Templates > Visual C# > Windows > Classic Desktop > Console Application

when using Visual Studio 2015.

I’ll walk you through the code for the application, which is underneath. I have added a method CheckArgs which checks if the application is called with command line parameters. If not, a help text is shown for 30 seconds, that describes which command line parameters are required and optional.

Otherwise the application continues by parsing the command line parameters in method ParseArgs. Furthermore, the inline comment should help you to understand the concept. In the console application replace the entire contents of Program.cs by this (please note that at the bottom of the article a link will be provided to download all the source code):

In Visual Studio select menu option Build > Build Solution or Rebuild Solution. Copy the ExampleConsoleApp.exe and ExampleConsoleApp.exe.config files from the bin subfolder to a folder of your choice. I used C:\Temp.

The files of the Build

Side B: develop the SSIS Package

In the SSIS Package we use an Execute Process Task to execute our example console application. There are a few different ways to handle the execution result (return code) of the console application, which I will show you in a minute.

But first, execute the following steps:


Step 1 – Add parameters

Add a number of parameters to the package: ExecutableName, ExecutablePath, Param1, Param2, Param3 and Param4, as shown in the picture below. Optionally this can also be variables, when the values do not need to be configurable after the SSIS package is deployed.


Step 2 – Add variables

Add a number of variables to the package: ReturnCode, StdError and StdOutput, as shown in the picture below. These variables are needed to store the information- and error messages and execution result of the console application.


Step 3 – Add an Execute Process Task to the Control Flow

Add a Execute Process Task and configure its properties as follows:

Property Value
FailPackageOnFailure False
FailParentOnFailure False
Expressions – Arguments “-param1 ” + @[$Package::Param1] + ” -param2 ” + @[$Package::Param2] + ” -param3 ” + @[$Package::Param3] + ” -param4 ” + LOWER((DT_WSTR, 5)@[$Package::Param4])
Expressions – Executable @[$Package::ExecutablePath] + “\\”+ @[$Package::ExecutableName]
ExecValueVariable User::ReturnCode
StandardErrorVariable User::StdError
StandardOutputVariable User::StdOutput
SuccessValue 0
FailTaskIfReturnCodeIsNotSuccessValue True

The final result should look as follows:


Step 4 – Handle the console application return code and proceed with the package

Now you can use an Execute SQL Task or a Script Task to do something with the ReturnCode, StdError or StdOutput values. This is optional. If you do not intend to use the values of StdError or StdOutput, for instance for logging, no extra task is needed.

Handle the error – option A

Add a Script Task.
Add the following variables to the ReadyOnlyVariables list:
User::ReturnCode,User::StdError,User::StdOutput

Then add this code to public void Main()


Handle the error – option B

Add an Execute SQL Task.
Configure the Parameter mapping as shown in the picture below:

On the General pane, make sure SQLSourceType is set to Direct input.
Add a connection, for the demo it does not really matter to which database.
Edit the SQLStatement by pasting the code below:

The end result should be similar to this:

Finally the demo package should look as follows when a Script Task is used ..

.. or as follows when a Execute SQL Task is used:

We still have time for a little demo ..

I will just run the package with the Execute SQL Task, because it contains the code for logging, so we can check the error message later. As you might have noticed in the code snippets, if Param1 is given the value testerror, a divide by zero error will occur, as it is deliberately programmed in the console app.

So let’s run the package with this parameter value and see the error occur!


The expected error occurs ..


… and yes, it is the error that occurred in the console application and which was added to the error output stream of the console application.

Conclusion / Wrap up

In this blog post I explained:

  • Why it can sometimes be useful to execute a console application instead of using a Script Task in a SSIS package.
  • How you can create your own C# console application that can be executed with command line parameters.
  • How this console application can be executed from within a SSIS Package.
  • How the Return Code, Standard Error and Standard Output streams can be captured in SSIS Variables.
  • How these variables can be used in a Script Task or an Execute SQL Task.

Downloads

Download the source code of the examples here:

See more

For SSIS package documentation, consider ApexSQL Doc, a tool that enables documenting SSIS packages from file system, SSIS package store, SQL Server, SSISDB catalog and SSIS project file in different output formats, and with various included details.

To compare SSIS packages with each other, consider ApexSQL Diff.

Resources on the web


Hans Michiels

Hans Michiels

Hans is an Independent Business Intelligence and Data warehouse Consultant, working in the Netherlands.

He works in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on data warehouse and business intelligence projects using Microsoft technology, preferably a Data Vault and Kimball architecture.

He has a special interest in Data warehouse Automation and Metadata driven solutions.

* Certified in Data Vault Modeling: Certified Data Vault 2.0 Practitioner, CDVDM (aka Data Vault 1.0)

* Certified in MS SQL Server: MCSA (Microsoft Certified Solutions Associate) SQL Server 2012, - MCITP Business Intelligence Developer 2005/2008, MCITP Database Developer 2005/2008, MCITP Database Administrator 2005/2008

His web site and blog is at www.hansmichiels.com, where you can find other contact and social media details.

View all posts by Hans Michiels
Hans Michiels
Business Intelligence, Integration Services (SSIS)

About Hans Michiels

Hans is an Independent Business Intelligence and Data warehouse Consultant, working in the Netherlands.

He works in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on data warehouse and business intelligence projects using Microsoft technology, preferably a Data Vault and Kimball architecture.

He has a special interest in Data warehouse Automation and Metadata driven solutions.

* Certified in Data Vault Modeling: Certified Data Vault 2.0 Practitioner, CDVDM (aka Data Vault 1.0)

* Certified in MS SQL Server: MCSA (Microsoft Certified Solutions Associate) SQL Server 2012, – MCITP Business Intelligence Developer 2005/2008, MCITP Database Developer 2005/2008, MCITP Database Administrator 2005/2008

His web site and blog is at www.hansmichiels.com, where you can find other contact and social media details.

View all posts by Hans Michiels

2,324 Views