7

In an SSIS dataflow task we have a derived column transformation with approximately 100 columns (basically converting raw input string data into typed variables). When this task fails, is there any way to tell which column caused the failure, for logging purposes? The only other alternatives I can think of are a custom script task to perform each conversion individually (yuck) or a separate derived column transformation for each data point (double yuck).

Basically I just want to be able to re-direct failure rows and know why they failed.


So an example. Our package is being used to allow users to bulk-upload to our database using Excel spreadsheets. So let's say the spread sheet coming in looks like this (except there's hundreds of columns):

+--------+-----------------+---------+------------+---------+
| Text1  |     Number1     | Number2 | DateTime1  |  Text2  |
+--------+-----------------+---------+------------+---------+
| Spring | 1               |       1 | 1/1/0001   | Flowers |
| Summer | 2               |       2 | 6/1/2015   | Sweaty  |
| Fall   | N/A             |       3 | 10/31/2099 | Crunchy |
| Winter | This is garbage |       4 | 12/12/2020 | Icy     |
+--------+-----------------+---------+------------+---------+

In this instance we want Spring, Summer, and Fall to succeed. Fall have a null value for Number1. The derived column will have logic that looks something like this (not valid syntax, just the logic)

sanitizedNumber1 = Number1 == "N/A" ? null : cast(Number1 as int)

Winter will be redirected down the error path and logged. Is there any way to know which derived column failed? Again, we have about 100 inputs that are being processed in this transformation in a similar fashion. I'd like to be able to log something like:

Import record "Winter" failed due to invalid data in "Number1"

It doesn't necessarily have to be this format, but anything that would allow a user to be able to uniquely identify the bad data point would be acceptable. I know this would be possible using a script component and performing the conversions manually (which is what we're going to have to do if there's no better option) but if it's possible to just modify the Derived Column Transformation to provide something along these lines I'd rather do that instead of re-implementing the entire component in a script.

7
  • Where is this data coming from? Can you not do your type casts in the source?
    – Dave
    Nov 20, 2015 at 20:39
  • It's slightly more complicated than that. The source is an Excel spreadsheet, where values can be numeric or string sentinel values (think N/A or NaN). If a row has garbage data we'd like to discard that row and log the failure rather than fail the component, but we'd like to know which data point was garbage rather than just "there was garbage". Nov 20, 2015 at 21:04
  • This is what the ErrorColumn property is for. Though mapping that back to a column name seems harder work than it should be... Nov 21, 2015 at 16:45
  • 1
    SQL Server 2016 offers the error column name mssqltips.com/sqlservertip/4066/… Just a short 11 years after the product was released
    – billinkc
    Nov 21, 2015 at 22:11
  • @bill hahaha, and we just upgraded from 2008 to 2012, so I'll look forward to using that in 6 or 7 years sobs quietly Nov 21, 2015 at 22:16

2 Answers 2

1

You have to manually identify the failures.

Redirect the rows (as you currently are) into a table that matches your columns, with the single addition of an IDENTITY column. This table should be all VARCHAR data types, so that you retain all original values.

Now you can identify your failures using TRY_CAST when querying the table.

For example:

SELECT
Identity,
TRY_CAST(Winter As INT) As WinterConverted,
Winter
--------------------------------------
Identity  |  WinterConverted   | Winter
   1      |    NULL            | NaN

TRY_CAST failed to cast the value in Winter column to an int leaving you with a null. You can do this for all columns.

If it's worth your time, you could make a more complex query to return each column name that contains a NULL. If not, you should be able to relatively easily visually inspect.

Of course, if most of your errors are 'NaN' or 'N/A', there's a good chance you're wasting time identifying errors you already knew about. Cleanup the issues before they become one:

Winter == "NaN" || Winter == "N/A" ? -1 : (DT_I4)Winter

As a side note, you can add a TRY_CAST to 100 columns very easily by using vertical editing: hold Alt+Shift in SSDT or SSMS (some other apps too) and then or , then edit hundreds of lines at the same time.

2
  • I think I'm either not understanding this answer or I didn't explain my question very well. I'm going to edit my question with a bit more information. Nov 20, 2015 at 22:17
  • I edited the question and added an example. Nov 20, 2015 at 22:35
0

Edit the Derived Column transformation, and click the [Configure Error Output...] button at the bottom of the transformation editor dialog.

Under the [Error] column, select Redirect row from the dropdown.

Now your failed rows will pipe through the transformation's error output (the red line) - depending on how you want to log the failures you'll add an OLE DB Destination or a Recordset Destination or whatever you want to send the failed rows to.

2
  • Yes, I know that (that's what we're doing now) - however that doesn't tell which column failed. All I know is which row failed. Given that we're dealing with potentially hundreds of columns I'd like to be able to identify which specific piece of data was bad rather than just saying Row whatever has something bad in it. Check all 3784 columns and find it. Nov 20, 2015 at 22:53
  • Ok. Well, your post wasn't exactly clear about that. If your derived column component is only dealing with 1 column, then you know which column failed. If your component defines 200 derived columns and one fails, your only other option AFAIK is to inspect the 200 derived columns somewhere down the error path, to add metadata before you persist the failed rows anywhere. Nov 20, 2015 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.