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

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

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?

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.