Why are my CASTS seemingly ignored in SSIS?

Question

In SSIS, I use a data flow task to retrieve data from one server (SQL Server 2005) to another (SQL Server 2008 R2). In an OLE DB Source connection, I use the SQL command to get the data. In this command, I already selected the content as a specific data type:

SELECT CAST(column1 AS VARCHAR(10)) AS NameColumn1 ,CAST(column2 AS INT) AS NameColumn2 ,CAST(REPLACE(REPLACE(REPLACE(REPLACE(column3, CHAR(10), ''), CHAR(13), ''), CHAR(9), ''), ';', '-') AS VARCHAR(100)) AS NameColumn3 ,CAST(REPLACE(REPLACE(REPLACE(REPLACE(column4, CHAR(10), ''), CHAR(13), ''), CHAR(9), ''), ';', '-') AS VARCHAR(255)) AS NameColumn4 ... FROM (etc.) 

(Note that the drives cast the original data type of the column values ​​in the original database. Thus, the data type of column 1 is initially VARCHAR(10) , and the original type of column4 is initially VARCHAR(255) , etc.)

These roles are used because in the package I received the following warning for column3 and column4 :

Validation Warning. [...] Truncation may occur due to the insertion of data from a column of data stream "column4" with a length of 8000 into the database column "column4" with a length of 255.

The drives do not seem to work because truncation warnings for columns3 and column4 continue to come. (I already set a truncation warning in OLE DB Source to ignore the failure, but that doesn't seem like a difference.)

I can’t find anything in this online and am currently “resolving” this by importing columns as VARCHAR(8000) . But I would like to know what is the reason for this behavior in SSIS. Anyone got it?

Answer 1

Go to the Advabnced Properties of the source component and check the size of the 3rd and 4th columns (in the input and output properties). If they were already calculated as varchar (8000), you will have to manually change them.

Alternatively, remove the component and add a new one using Select / Cast in place from the beginning - this should correctly assign the correct field length.