SSIS - the value cannot be converted because of a potential loss of data

Question

I am relatively new to SSIS. I am trying to extract information from an Oracle database using Microsoft OLEDB for Oracle and I am using this query:

SELECT ID FROM Test

I get an error message saying: the value cannot be converted because of a potential loss of data. If I change the query to the following then it works:

SELECT '1' FROM Test

I think it is failing because the ID is not an integer. However, the flat file connection manager shows that the OutputColumnWidth is 50. What am I doing wrong?

Update 16:30 GMT
I have looked into this a little more and it appears to be the columns that have a Histogram of 'frequency' or 'none' that are causing the problems. Those with a Histogram of 'Height Balanced' appear to be OK.

Solution

This is a common thing. The default in a lot of cases for imports into ssis from another type of system where metadata for columns cannot be determined is to default to str(50). Since you are trying to push that into a one character column, it assumes that you may lose data. Simply go into the source component by right clicking and choosing "Show Advanced editor..."

Then navigate to the last tab (Input and Output Properties)

Click the + next to OLE DB Source Output

Click the + next to Output Columns

Highlight the ID column

Scroll to the Length Data Type Property on the right of the dialog box and change it from 50 to 1.

Comments

[billinkc]

What is the data type of ID in the table Test?

[w0051977]

@ billinkc, it appears to be: String[DT_STR]

[w0051977]

@billinkc, I have edited the question. Please have a look.

[Paul Sullivan]

my experience says it's complaining (as you rightly think) because of the mismatch of datatype and the possibility of truncation. I believe you can force it to do the transform anyway and I reckon it is just being safe and you have to add some error handling in your SSIS package. see msdn.microsoft.com/en-us/library/ms141706.aspx

[w0051977]

@PaulSullivan, could you explain how I can "force it to do the transform ". Thanks.

[Paul Sullivan]

read my link - you have to add an error handling element from memory which allows you to handle the error (using the UI to point click the option) "If the length of an output column of string data is shorter than the length of its corresponding input column, the output data is truncated. For more information, see Error Handling in Data."

[w0051977]

@PaulSullivan, thanks. If I enter a number e.g. select '1111111111111111111' from Test, then it seems to work.

[w0051977]

Thanks for this +1. The solution was to do the complete opposite to what you said i.e. change 1 to 50 (you pointed me in the right direction). I assume you said change 50 to 1 because you thought it was an integer type? - it was actually a String type. I noticed that some of the Output Columns had the wrong data type e.g. a four byte signed integer for a String. Can you explain why this could have happened before I accept your answer?

[William Salzman]

I assumed it was 1 because you said it worked for '1' I initially thought the error was in your next step where you were moving the imported data into a smaller destination field. Your description here points to the metadata being wrong on the actual import. Are you using a driver for Oracle that is at the same level as the Oracle DB? Also what is the state of the AlwaysUseDefaultCodePage flag (see my answer here: stackoverflow.com/a/8697899/236348)?

[w0051977]

Thanks. I had already set the AlwaysUseDefaultCodePage flag as per your link before writing the question. What do you mean by "meta data being wrong" and "at the same level"?

[William Salzman]

(Meta Data) Is SSIS properly pulling column data type precision and scale from Oracle? (Same release level) Are your Oracle drivers on the SSIS machine from the same release level as your Oracle installation (9i 11g etc)? Have you tried the Attunity drivers?

[w0051977]

Thanks for this. Are you able to answer my other question, here? stackoverflow.com/questions/11765026/…

[Geo]

Worked for me as well. Make sure you can get into the Output Columns and ensure that your data types aren't janky. I had to change the ones that were trying to pass through as double-precision floats to Unicode (255) and that helped me.