DERIVED COLUMNS component and casting issue
Package looks like this. 1. OLEDB Source - stored proc to get data 2. Derived Column 3. OLEDB Destination - insert records 4. Derive column - error fields 5. OLEDB Destination - error records I wrote out a derived error column in component #4 here (the key field). This worked fine in our test environment. The table destination column looks like BUSINESS_KEY_VALUE nvarchar(500) NULL Then we deployed to PROD and started getting an "invalid type casting error". The error occurs on component #4. We're even taking the field and allowing whatever datatype it is to naturally populate as the output data type in component #4. The field is naturally a DT_WSTR[200]. Then, by moving the same expression into component #2, the problem went away. The TEST and PROD environments run the same version of SQL Server 2008. We tried deleting the failing component (#4) and re-creating it, with no luck. There are two troubling issues with this: 1. Even with the exact same package (same build version number and everything), same source data, same target structure, we cannot re-create the issue in TEST. 2. Can anybody help me with why this might work by simply moving the expression upstream from component #4 to component #2. If there is a pattern or something specific I can look for, then I can avoid this issue in the future. But right now, I still don't understand the problem properly. Error scenario (error in Prod, success in TEST) Derived column: [customer_code] -> [Business_Key_Value] DT_WSTR[200] OLEDB Destination: [Business_Key_Value] -> [Output_Column] nvarchar(500) NULL changed to: Derived column1: [customer_code] -> [Business_Key_Value] DT_WSTR[200] Derived column: no expression - columns carry through OLEDB Destination: [Business_Key_Value] -> [Output_Column] nvarchar(500) NULL
April 11th, 2011 3:13pm

You have overlooked something that is different between your TEST and PROD environments. What is the full error message? Does the package uses configurations and/or any configs in DTExec? How do you execute the package? Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 11th, 2011 3:35pm

What version of SSIS are you using? The derived column is different in 2005 vs. 2008+. Are your patch levels in TEST and PROD the same? (Probably not the issue, but worth checking.) What's your expression? Does the package in production always generate an error on the same row? Can you configure the derived column to send its errors to a persistent location so you can see the actual data? I believe the root of the issue can be determined if you can tell how data in production is different from your test data. The short answer is no - I can't see how an expression can behave differently between your two scenarios based solely on location. But I've never seen that before - there must be a reason. Talk to me now on
April 11th, 2011 3:40pm

We're on SQL Server 2008 R2. The expression looks like this "[customer_code]". We're just writing a field out to the "business_key_value" column. SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DER Derive Insert Error Data" (24633)" failed because error code 0xC0049062 occurred, and the error row disposition on "output column "BusinessKeyValue" (43356)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. This error is independent of which row is being processed or how many rows are processed. We fiddled with the leading stored proc to allow different records to flow through, all with this result. It failed with this error in PROD. Then we simply moved the expression upstrem in PROD and it worked. I think I confused the issue by mentioning that it never failed in TEST. My only thought was that by putting it further upstream, the field was getting type casted twice (once in the first Derived Column component and again in the second Derived Column component) before getting inserted. But I still don't see how that makes a difference. Thanks for the help. Any insight would be appreciated.
Free Windows Admin Tool Kit Click here and download it now
April 11th, 2011 3:52pm

I suggest to inspect your datatypes, the error is clearly stemming from the fact they are different. How [customer_code] is defined?Arthur My Blog
April 11th, 2011 4:35pm

Arthur's gotta be right. One question though - why are you using the Derived Column at all? Why not just map the [customer_code] column to the appropriate field in the OLE DB Destination? Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
April 11th, 2011 7:16pm

I agree with mapping it directly. In fact, we've started to do just that in new packages, but we use this method in packages across lines of business and projects (probably 60-70 packages). The general method is to do a bulk load (or fast load or whatever SQL Server calls it in newer versions), send the errors to a regular load (api load) that has more detailed logging, and then send those errors to a derived columns component and write the records to an exception table. The most common data exception is that the primary key, or BUSINESS_KEY_VALUE, is NULL or invalid. I'm just trying to understand why we get this error only in certain packages and why moving the expression upstream always fixes it. When we get the error, it occurs even when there are 0 records that make it to that flow in the package, which is why I don't believe it's a data type error, despite the error message. It seems as though we have only gotten this error with 2008R2 packages. 75% of our packages were written in 2005, but we've been slowly moving to 2008 R2. We've only had this issue with 2008 R2, but that may be a coincidence as well. Thanks again for the insight.
April 12th, 2011 8:57am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics