What I do want to bring to your attention is the differences between the two when it comes to redirecting error rows, specifically rows that are truncated. One of the beauties of SSIS is the ability to output rows that fail to import through the error pipeline and push them into an error table for example. With fast load there is a downside to this, the whole batch will be output even if there is only 1 row that fails, there are ways to handle this and a tried and tested method is to push those rows into another OLE DB Destination where you can run them either in smaller batches and keep getting smaller or simply push that batch to run in row-by-row to eventually output the 1 error you want. Take a look at Marco Schreuder’s blog for how this can be done.
One of the issues we have exerienced in the past is that any truncation of a column’s data in fast load will not force the package to fail. What? So a package can succeed when in fact the data itself could potentially not be complete!?! Yes this is certainly the case, lets take a quick look with an example.
Read on for details and potential workarounds.
Dave Mason continues his discussion of using Extended Events to handle errors:
In the last post, we explored a couple of examples of using Extended Events to enhance T-SQL error handling. There was some potential there. But a hard-coded SPID was necessary: we couldn’t use the code examples for anything automated. It was cumbersome, too. Let’s change that, shall we?
To make the code easier to work with, I moved most of it into three stored procs: one each to create an XEvent session, get the XEvent session data, and drop the XEvent session. There’s also a table type. This will negate the need to declare a temp table over and over. The four objects can be created in any database you choose. I opted to create them in [tempdb]. The code for each is below in the four tabs.
This is a very interesting solution.
Dave Mason shows how to use an Extended Event to capture error data:
Here’s an example for DBCC CHECKDB on a corrupt database. Remember from the last post that in this scenario, control never passes to the CATCH block. So we’ll need to check the Event Session data after END CATCH. You can also run this as a single batch in SSMS, but you’ll need a corrupt database to get similar results. As before, replace “2016” with your SPID.
There are a lot of working parts to this, so read the scripts carefully if you’re interested in implementing something similar yourself.
Ginger Grant walks through a couple of issues you might run into with Event Hub:
The input for this stream is set to an event hub which has a standard subscription. The basic subscription, which is of course cheaper, has one default consumer group. With a standard subscription multiple consumer groups can be created and more importantly named. When setting up the inputs there is a blank for the name of the consumer group. If you have a basic subscription this will be empty. If it is empty, then the event hub won’t pass data to the stream analytics job. Perhaps there is a way to get a basic event hub to work with a stream analytics job, but I couldn’t make it happen. When I created an event hub with a standard subscription and created a consumer group and added that name to the input of a streaming analytics job, it worked.
Read on for details.
However, when run via SQL Agent, it succeeds. GAH!
I tried 50 different variations; modifying the script, various TRY..CATCH blocks found on the internet. Nothing. Every single one of them succeeded.Then I remembered that by default, even though it had an error, by default errors always continue. ($ErrorActionPreference=”Continue”. So I added this line at the top:
Read on for the answer.
Dave Mason discusses a major problem with try-catch blocks:
We didn’t just lose one error. We lost them all! DBCC CHECKDB on a corrupt database just came back “clean” and the CATCH block never executed. We’ve moved into dangerous territory now. This is no longer an inconvenience or an annoyance. This could lead to data loss, financial harm to your company, maybe even some risk to your career.
Dave promises a better approach in his next post.
Ginger Grant discusses Azure Data Factory errors:
Unfortunately, while developing Data Factory I became very familiar with errors. All of the errors show up at the end and provide very little insight as to what in the process failed. Here’s an example.
Database operation failed on server ‘Sink:DBName01.database.windows.net’ with SQL Error Number ‘40197’. Error message from database execution : The service has encountered an error processing your request. Please try again. Error code 4815. A severe error occurred on the current command. The results, if any, should be discarded.
This sounds like classic Microsoft error messages: “An error occurred. Here is a code you can put into Google and hope desperately that someone has already figured out the answer. Good luck!”
Manoj Pandey was debugging an Informatica ETL and got back an uncommon error message:
So, to identify the cause I tried to execute the above MERGE statement directly and I got the same error:
EXEC spMergeTables 'STG.ABCtblXYZ','ABC.tblXYZ'(0 row(s) affected)
Msg 2754, Level 16, State 1, Procedure spMergeTables, Line 107
Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
This is a case in which an immediate error obscured the actual error.
Tibor Karaszi notes an important change to SQL Server Management Studio with the July 2016 release:
Now, exposing one more SET option can’t be bad, right? But Erland Sommarskog found out that this is checked by default. Now, using XACT_ABORT might now [sic] be a bad thing, au contraire. See this one of Erland’s series of error handle articles.
Making this change after all of these years is a little odd. Making it in a regular update is very odd. Using SET XACT_ABORT ON is a smart move in general, but there are times in which you don’t want to rollback immediately after an error; the problem is, are all of those places in your code well-documented?
Tara Kizer notes that there are limits in what TRY/CATCH blocks handle in SQL Server:
It’s well documented in Books Online (BOL). If you’re like me, then tl;dr. Are we even calling it Books Online these days? I still say “bookmark lookup” instead of “key lookup”. I suppose I’ll be saying Books Online for quite some time too. At least these days it really is online.
Here’s a shortened version:
Warnings or informational messages that have a severity of 10 or lower
Errors that have a severity of 20 or higher that stop the session
Attentions
When a session is KILLed
It’s important to know that not everything gets caught, particularly major issues.
Kevin Feasel
2016-11-15
Error Handling, Integration Services