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?
The way in which this is usually handled in SSIS is as follows:
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.