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?