CODEWITHSUNDEEP
sql-serveroracle-databasessisetl
w0051977
w0051977

Reputation: 15817

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.

Upvotes: 32

Views: 128592

Answers (2)

questionto42
questionto42

Reputation: 9630

This is not an answer to your question, but helps fixing the error if your input can be changed in SQL instead.

If you run into this not with a flat file connection manager but with a TSQL query, you can also fix this in SQL so that the external length comes in right and you do not need to change its length in the advanced settings at all:

SELECT cast(ID as varchar(1)) FROM Test

I had to do that for the first day of the year 50 years after the birthday.

cast(concat('01.01.', convert(varchar(4), birthday, 112)+50) AS varchar(10))

Without the cast(), the length came in as 18, now it is 10 right away.

Upvotes: 0

William Salzman
William Salzman

Reputation: 6446

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.

Upvotes: 51

Related Questions