0

Good morning all, It's been 2 days now trying to figure out how to solve this issue, still no answer. Maybe you can help me out. Here is the thing. I have created an SSIS package to import data from another database server; here are the specifications of both servers: I am using Sql Server Data Tools 2012

Source: SQL Server 2008 r2 Connection Type: OLE DB

Destination: SQL Server 2012 Connection Type: OLE DB

The package is an Import and Export package, having 2 tasks, a "Data FLow Task" and an "Execute SQL Task". The source is an SQL Command, with a simple select from a couple of joins, nothing that special. the Destination is Table in the destination server. Of course we have a Data Conversion task in between. The package was working fine until we have added a new column to the Selection, of type varchar(50). I modified the destination Table with same, only i used NVARCHAR(50), and added the data conversion as UNICODE string [DT_WSTR] of length 50.

After this was done, I am receiving the error:

Error: 0xC020901C at [Package], Source - Query [127]: There was an error with Source - Query.Outputs[OLE DB Source Output].Columns[COL] on Source - Query.Outputs[OLE DB Source Output]. The column status returned was: "The value could not be converted because of a potential loss of data.".

I have tried to increase the sizes of the destination columns from 50 to 100, still the same error is happening. Weird thing is when I replace Col with NULL in the source query, I get the package to run perfectly.

Any suggestions?

Improve this question
5
  • Why not just use an NVARCHAR(50) as the data type in the SELECT statement so there's no implicit conversion when inserting the data into the table? Have you verified that the length of the data in the source table is all <= 50 characters? SELECT * FROM table WHERE LEN(column) > 50
    – Alf47
    Aug 21, 2014 at 22:07
  • The source column is of type VARCHAR(50), i don't really think we could have some data with len() > 50, right?
    – Hani El Mouallem
    Aug 25, 2014 at 6:51
  • Even trying to change the destination to VARCHAR(50) rather than NVARCHAR(100), and by that skipping any conversions that might occur, still the same problem...
    – Hani El Mouallem
    Aug 29, 2014 at 13:13
  • Did you ever solve this, I have the same error, same conditions.
    – Fran Hoey
    Sep 11, 2014 at 15:13
  • unfortunately @FranHoey not yet... still checking what to do.
    – Hani El Mouallem
    Sep 15, 2014 at 9:18

2 Answers 2

Reset to default
2

Okay I found the solution after searching and testing many things. The issue was in the Metadata (the connection between the Source Query and the Data Conversion), the length of the field was not matching the one from the source query anymore, which is to me, very odd as I thought that the Metadata would be automatically refreshed when you save and compile, or when you close the solution and reopen it.

I don't know why this occurred, but now the package is running again perfectly.

Improve this answer
0

This isn't a solution, but a strange work around.

I had exactly the same issue, it was down to just one column that contained a 6 character alpha numeric code in type Char(6). My source was a sql script, my destination was an nvarchar and I had a converter in between.

Nulls, where ok so I restricted the import to a single record with a value. I found I could run the import with LEFT([column], 1), but not with any other character from the code. For example SUBSTRING([column], 2, 1) still errored.

At this point I gave up, and put in a work around. I was able to import the column and associated key into a different table and run an UPDATE script to copy the data to the destination table.

Very odd.

Improve this answer
1
  • Odd is a good word, check my answer below to find what odd can be... please check it as well it might solve your case.
    – Hani El Mouallem
    Sep 15, 2014 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.