Validations within the SSIS


I currently have the following flow in an SSIS

My question is, is there any way to put an OLE DB, which allows me to insert information into a table called errors if the Excel Source comes to fail, in a few words, if the Excel file does not exist, I want to save 3 things in the table of errors, which would be, the date, an action and the message it produces. Is it possible to do this?

asked by Broodwing009 03.05.2018 в 01:42

1 answer


The way in which this is usually handled in SSIS is as follows:

  • Go to the 'Event Drivers' tab of the package.
  • Select the corresponding executable. In this case your 'Data flow task'.
  • Select the event you want to capture, in this case an error (OnError).
  • Add the element or set of elements that should be executed when the error occurs.
  • In the following image you can see the process summarized:

    For your case (Performing an insert when an error occurs), what you can do is add an 'Execute SQL' element, with a connection to your OLE DB to your database . In the field SQLStatement we add the SQL query that we want, as you can see in the following example:

    And finally, in the section on the left 'Assignment of parameters', we assign the data that we want to insert in our table. In the following example we are saving the start date of the package execution and the description of the error that has occurred:

    You can find all the system variables that can be used in the official documentation . You can also use own variables that you have previously defined in the package.

    answered by 03.05.2018 / 12:23