Hi,
I am getting this error when my ssis package is running
Data Conversion failed due to Potential Loss of data
the input column is in string format and output is in sql server bigint
the error is occuring when there is an empty string in the input. what should i do to overcome this
It is an ID field and should i convert to bigint or should i leave it as char datatype is it i a good solution or is there a way to over come this.
Add a derived column to either change the empty string to NULL or a zero. Up to you, but you can't insert a string into an integer field.|||I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:
ISNULL(<<input field>>) ? 0 : <<input field>>
In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.
|||
desibull wrote:
I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:
ISNULL(<<input field>>) ? 0 : <<input field>>
In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.
NULL and "empty string" are two very different things.
To expand on what I suggested earlier and desibull's code above:
ISNULL([InputColumn]) || [InputColumn] == "" ? 0 : [InputColumn]
OR
ISNULL([InputColumn]) || [InputColumn] == "" ? NULL(DT_I8) : [InputColumn]
