Last post Jun 28, 2011 02:40 PM by AZMatt
Jun 28, 2011 01:36 PM|dagnar|LINK
new to ssis...so i have a flat file and importing into a table. i need to fail the process and send out an email based on the values of a datetime column. any tips on how to go about this? should i be importing into some temp table first then to my permantent
destination cause if i hit a fail condition i have to fail the whole process and can't have any committed rows?
thanks in advance.
Jun 28, 2011 02:04 PM|AZMatt|LINK
Yes, I often import into a "stage" table to make it easier to do analysis on the data before actually updating production tables.
As for the email, you can either return a value from the scripts that you use to analyze your stage tables that will kick of an SSIS email task, or you can do it from the scripts themselves using SEND_DBMAIL.
Jun 28, 2011 02:10 PM|dagnar|LINK
thanks for the reply...what if there was no requirement to rollback data, and just fail that particular row with a bad datetime column...how do i examine row data as it's being imported into a table.
Jun 28, 2011 02:25 PM|AZMatt|LINK
Again, you could import the file into a "stage" table. When you are processing the data through a script, you can examine each row using an SQL CURSOR...
Jun 28, 2011 02:27 PM|dagnar|LINK
i was hoping for a solution that didn't require a 'stage' table.
Jun 28, 2011 02:40 PM|AZMatt|LINK
In that case, you can customize the Data Conversion step. See the following article. In the Error Options, you would want to "Redirect Row". You can either write the error row out to an error table or have it kick off a different process like an email.