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?
Best Answer
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.