SSIS: How to read error messages from a console app and send them by email

SSIS: How to read error messages from a console app and send them by email

SSIS: How to read error messages from a console app and send them by email

Let’s say you have a SSIS package that runs a console app, everything works fine, but one day, the console app fails, and the SSIS package fails with a non helpful error message. You want to know what was the error, what data was involved and you want it be email

Works with

  • SSIS project in Visual Studio 2015
  • Simple console app in .NET 4.8

Steps

Console app

  1. Create a console app for C#
  2. Replace the code in Program.cs with this:
     class Program
 {
     static int Main(string[] args)
     {
         try
         {
             string value = "a";
             //Output to StdOutput variable
             Console.WriteLine("Value to convert:" + value);
             int i = int.Parse(value);
             return 0; //In console apps, normally returning 0 indicates success
         }
         catch (Exception ex)
         {
             //Output to StdError variable
             Console.Error.WriteLine(ex.Message);
             return -1; //Returning any value different than 0 might indicate failure or some other condition
         }
     }
 }
    In this program we’re doing the following
    • Writing data to the console
    • Deliberately throwing an error by trying to cast a letter to a number
    • Catching the exception, writing the error to the console and returning -1
  3. Build the project

SSIS

  1. Create an SSIS package

  2. Add the following variables

    • ReturnCode (int32) - Returns the code number from the Main function in the console app, this number could be 0 for success or -1 for error;
    • StdError (String) - Stores anything printed out with Console.Error.WriteLine(…) in the console app
    • StdOutput - String - Stores anything printed out with Console.WriteLine(…) in the console app

  3. and add an Execute Process Task to the control Flow

  4. Right click the element and select Properties

  5. Configure properties like this:

    • Executable: Browse the consola app.exe, should be something like C:\...\ConsoleApplication1\bin\debug\ConsoleApplication1.exe
    • ExecValueVariable: ReturnCode variable
    • FailPackageOnFailure: False
    • FailParentOnFailure: False
    • StandardErrorVariable: StdError variable
    • StandardOutputVriable: StdOutput variable
    • WorkingDirectory: The folder of ConsoleApplication1.exe, should be something like C:\...\ConsoleApplication1\bin\debug\


    Changes are highlighted:

  • You can also directly use the task editor

  1. Add a Send Mail Task element
  2. Connect the Execute Process Task to the Send Mail Task
  3. Double click the green arrow
  4. In Constraint options, set Value = “Failure”
  5. Click OK
  6. Double click Send Mail Task and configure it like this:
    • SMTP Connection - click <New connection> and configure your SMTP manager
    • From - An email account to send emails
    • To - A destination account
    • Specify a subject
    • Message Source - Don’t write anything
  7. In the same window, click Expressions
  8. Add an expression for MessageSource
    "Return Code:" + (DT_WSTR, 4)@[User::ReturnCode] + "\r\n" + "Error:" + @[User::StdError] + "\r\n" + "Output:" +  @[User::StdOutput]
  9. Click OK
  10. Run the package
    • Execute Process Task should fail
    • Send Mail Task should succeed
  11. Check you email

Resources

Download source code

Comments

Post a Comment

Popular posts from this blog

WPF - Combobox with a null item

--- --- WPF - Combobox with a null item It adds an item for “Select All” or “Empty” functionality into a combobox Converter public class ComboBoxEmptyItemConverter : IValueConverter { /// <summary> /// this object is the empty item in the combobox. A dynamic object that /// returns null for all property request. /// </summary> private class EmptyItem : DynamicObject { public string Name { get ; set ; } = "Empty" ; public override bool TryGetMember ( GetMemberBinder binder , out object result ) { // just set the result to null and return true result = null ; return true ; } } public object Convert ( object value , Type targetType , object parameter , CultureInfo culture ) { // assume that the value at least inherits from IEnumerable ...
Read more

Add Cordova and Electron to an Angular App

Image
--- --- Add Cordova and Electron to an Angular App Run your angular app as a web, Windows, Android or Linux app, and and if you have the means, in an Iphone or a Mac. Here I’m just covering the browser, Windows and Android. You can find this code in Github . Works in Windows 10 Node js 12.13 Angular CLI 10.1.1 Cordova 10 Android Studio Create the Angular App Create an Angular app by running this command in the terminal ng new angular-app Run in the browser Run ng serve Open http://localhost:4200 in your favorite browser Install Electron in the Angular App To install electron in angular-app run npm install --save-dev electron Inside angular-app create a file and name it main.js Copy the code the from the first app tutorial in the Electron web site and paste it in main.js const { app , BrowserWindow } = require ( 'electron' ) function createWindow ( ) { // Create the browser window. const win = new BrowserWindow ( { width : ...
Read more

How to enable CORS between an Angular app and an ASP.Net Web API Service

Image
How to enable CORS between an Angular app and an ASP.Net Web API Service If you have an ASP Net WebAPI service running in one domain, and an angular app running in another domain, you’ll need to enable CORS in the web service, in this example, we’re also enabling Windows authentication Works for NET Framework 4.8 Microsoft ASPNET Web API Angular CLI 10.1 Node 10.15 Windows 10 Create a .NET WebAPI application with Windows Authentication Create a folder and name it Models Inside Models create Employee.cs and this code: public class Employee { public int Id { get ; set ; } public string Name { get ; set ; } public string Email { get ; set ; } } Create the Employees controller, right click the Controllers folder and click Add Controller In the Add Scaffold window, select Web API 2 Controller - Empty In Controller Name write EmployeesController In EmployeesController.cs include this code: [ RoutePrefix ...
Read more