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?

  • 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 '14 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 '14 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 '14 at 13:13
  • Did you ever solve this, I have the same error, same conditions. – Fran Hoey Sep 11 '14 at 15:13
  • unfortunately @FranHoey not yet... still checking what to do. – Hani El Mouallem Sep 15 '14 at 9:18
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.

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.

  • 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 '14 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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