Casting in SSIS

Question

I'm trying to add 3 letters to my data in SSIS (OLE DB Source). Source format of my data is float, so first I cast it to int, next to char and add to string:

'ABC'+cast(cast(KL.ID as int) as char(46)) Id

and I get in MS 2008R2 something like

ABC443400

but when I run this code in SSIS I get:

[OLE DB Source [1]] Error: There was an error with output column "Id" (37) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data.".

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Id" (37)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "Id" (37)" 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_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

In Advanced Editor of OLE DB Source:

External Columns: DataType - string[DT_STR], Length - 49, Output Columns: DataType - double-precision float[DT_R8]

In Advanced Editor of OLE DB Destination:

External Columns: DataType - string[DT_STR], Length - 50,

How do you mean "running this code in SSIS"? It looks like T-SQL code to me, so I'm guessing that it's in the SQL query which supplies the OLE DB Source? If that is where it is, then it's not the code you've quoted that's causing the problem (SSIS doesn't execute that). The problem will be that output column "Id" is of the wrong data type. Look at the Advanced Editor in SSIS to see what data type it is (SSIS often guesses wrong) — SebTHU
DECLARE @f FLOAT = 443400.253243 SELECT 'ABC' + cast(cast(@f AS INT) AS VARCHAR(10)) This runs without any error in ssms — StackUser
According to @SebTHU, the error is in the output column of OLE DB Source. Source columns with no fixed data types makes SSIS guess data types based in results. To prevent this you have to specify a fixed data type in source query: CAST('ABC'+CAST(CAST(KL.ID as INT) as CHAR(46))) AS CHAR(50) — Balde
@Balde: true, explicitly casting the whole thing in SQL should work. Instead of this, I usually use the Advanced Editor to "slap" SSIS into using a column type and length which will work and won't be truncated. — SebTHU

SebTHU SebTHU · Accepted Answer · 2016-03-16T08:38:17

Looking at your latest update. The OLEDB Destination column type shouldn't be relevant (though it will become relevant later, probably), because the error is occurring on the OLEDB Source.

The problem is in the OLEDB Source Output Columns: Id is defined as double (RT_8). In Advanced Editor, you can change the datatype of the External Column "Id" (if you're happy with the current setting - and it looks fine: 49 characters - then change it to something else and then back again). This should automatically update the Output Column type as well (but check and manually change that as well to match).

Further down the line, it sounds as though you're writing this column to an OLEDB Dest. You may have to change the column type in your destination table to varchar(50) or nvarchar(50) (matching the SSIS-internal "Id" column type) and let SSIS read the table metadata again - or force a type change with Advanced Editor. Once you change the column type in the source, SSIS should complain that there's a type mismatch in the OLEDB Dest, but I won't guarantee this!

What it comes down to, I think, is SSIS guessing the wrong type for your Id column from the start.

Casting in SSIS

3 Answers