August 13, 2014 at 12:39 pm
I am receiving an error (sometimes) when loading data from an excel source onto a sql server database. During the load stage I am using the derived column transformation editor to create a new column that is the result of a mathematical expression between two other columns:
Derived Column = <Add as a new column>
Expression = (DT_Numeric,10,2)[Column1] - (DT_Numeric,10,2)[Column2]
Data Type = numeric[DT_Numeric]
Precision, Scale = 11,2
The error only occurs when there is a decimal in one of the columns (like 0.5 or 0.33). The error does not occur when they are whole numbers.
The error codes are below:
[Derived Column [96]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [96]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Change Number of Bays]" 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.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (96) failed with error code 0xC0209029 while processing input "Derived Column Input" (97). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Why would whit work with whole numbers and not decimals? What would I need to do to get this to work when there are decimals in source columns?
Any help is appreciated, thanks!
August 13, 2014 at 12:42 pm
BTW, when I preview the data in the Excel Source editor the decimal numbers look like "0,33" for 0.33.
August 13, 2014 at 12:58 pm
I just tried to reproduce the problem, but everything is good. Attached are the screenshots.
May be you can post your spreadsheet.
August 13, 2014 at 1:06 pm
acapone (8/13/2014)
BTW, when I preview the data in the Excel Source editor the decimal numbers look like "0,33" for 0.33.
Try to replace the comma with decimals and wrap that inside the rest of your statement for that derived column.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2014 at 3:51 pm
How do I do that from within the SSIS package?
August 13, 2014 at 3:57 pm
So I changed up the data flow and set it up to convert the columns to DT_NUMERIC (10,2) before the derived column task and now a different error is coming up:
[Data Conversion [2]] Error: Data conversion failed while converting column "New Number of Bays" (167) to column "New Number of Bays" (97). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Data Conversion [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion.Outputs[Data Conversion Output].Columns[New Number of Bays]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[New Number of Bays]" 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.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (2) failed with error code 0xC0209029 while processing input "Data Conversion Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
I have changed the input and output properties of the excel source so the output columns are a datatype DT_WSTR with a length of 20 and I am still getting the same error.
Any ideas?
August 13, 2014 at 3:59 pm
acapone (8/13/2014)
How do I do that from within the SSIS package?
Use a derived column
Try something like the following. Bear in mind that you still have the other transformation in your derived column transformation that you still need to do.
Replace Column1 with the appropriate column name.
LEN(LTRIM([Column1])) > 0 ? REPLACE(LTRIM([Column1]) , "," , ".") : ""
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 14, 2014 at 9:48 am
Changing the column to a period worked, thanks!
I don't understand why this was necessary though. It seems like SSIS should be able to recognize that it was a number format being sent over and transform the comma to a decimal when I requested the transform to numeric.
Well, at least it is working now. Thanks for your help everyone!
August 14, 2014 at 9:52 am
acapone (8/14/2014)
Changing the column to a period worked, thanks!I don't understand why this was necessary though. It seems like SSIS should be able to recognize that it was a number format being sent over and transform the comma to a decimal when I requested the transform to numeric.
Well, at least it is working now. Thanks for your help everyone!
Glad it is working.
The source is text and a comma most commonly is used as a separator (at least where SSIS is concerned). The international formats are frequently a pain with SSIS. This is not the only datatype issue with SSIS. There are plenty of others that are still being fixed in current versions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply