I was working on an SSIS package a few weeks back and was puzzled by a truncation warning that two of the Data Flow tasks kept throwing out. In the Data Flow, a string-based column of length 10 in the pipeline was being used as part of the input for an OLE DB Destination, which was storing the data into a SQL Server table. The column being populated by this specific input column had a data type of varchar(20). No problem, right?
Well, the Data Flow task was generating a warning stating that the column of length 20 might be truncated when used to populate the column of length 10. Huh? Exactly what I thought. Why was the Data Flow task complaining about that when clearly the column of length 20 was the destination and the column of length 10 was the source.
I reviewed every property I could think of with regards to the source column, the destination column, the destination table, the OLE DB Destination component, the Data Flow task, etc. Nothing I could find (at first glance) seemed out of place. And even though the package ran fine (it was, after all, just a warning), it continued to bug me that something about the package was causing the SSIS engine to generate the warning.
After scratching my head for quite some time, I finally found the answer. It seemed that someone working on the package prior to my development work on it had set the ValidateExternalMetadata property on the OLE DB Destination to False. This is typically done (in my experience) to keep a Data Flow task from complaining about the metadata of a destination table that doesn't exist yet (maybe it will be created in a step before the Data Flow task executes, for example). Well, believe it or not, setting this property to False seemed to cause the SSIS engine to misinterpret the metadata information about the source and destination, in this case, swapping the column metadata.
Here's a quick example to see this in action. Create a Data Flow task in a package, add an OLE DB Source component, a Derived Column component, and an OLE DB Destination component. For the OLE DB Source component, select a table from the AdventureWorks database (like the Production.Product table) and pick a character-based column. Connect the OLE DB Source component to the Derived Column component. In the Derived Column component, create a new column (with a new name) and use the SUBSTRING() function to make a shorter version of the string column. Send the output of the Derived Column component to the OLE DB Destination, mapping the source to the original table, using the shorter column you created as the source for the column you originally picked from the table. So, you end up with something like this:
In the OLE DB Source, I selected the Production.Product table and selected the Name field, which has a data type of nvarchar(50).
In the Derived Column component, I created a new column named ShorterName as follows:
Finally, for the OLE DB Destination, I mapped the new ShorterName column back to the Name column:
Now, there's no need to really run the package to see the warning. Simply change the ValidateExternalMetadata property for the OLE DB Destination component from the default of True to False.
As soon as you do this, you'll see a warning icon on the OLE DB Destination component. If you look at the warning in the Error List, you'll see something like the following:
Validation warning. Data Flow Task: OLE DB Destination : Truncation may occur due to inserting data from data flow column "Name" with a length of 50 to database column "ShorterName" with a length of 40.
Note that the truncation warning has inverted the metadata involved, stating that (in this case) truncation may occur inserting data from the Name column to the database column ShorterName. But, as is clearly the case, the database column (the destination) is the Name column while the ShorterName column is actually the source. If you set the ValidateExternalMetadata column back to True, the truncation warning goes away.
Strange behavior. Again, just a warning that doesn't keep a given data flow from working properly. But strange nonetheless. I've posted this as a bug on Connect; if you come across it, feel free to comment or validate it.