Sunday, February 24, 2008

Incorrect Truncation Warning in SSIS

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:

Simple Data Flow

In the OLE DB Source, I selected the Production.Product table and selected the Name field, which has a data type of nvarchar(50).

OLE DB Source Component

In the Derived Column component, I created a new column named ShorterName as follows:

Derived Column Component

Finally, for the OLE DB Destination, I mapped the new ShorterName column back to the Name column:

OLE DB Destination

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.

OLE DB Destination Properties

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 [140]: 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.


Chinna said...

Good Work Bro...It Really helps me..

Bob Dickey said...

Unfortunately, this doesn't fix the problem in SQL Server 2005. With a similar Data Flow (Flat file source, Derived Column Transform, OLE DB Destination) I get exactly the same warning message with the ValidateExternalMetadata flag set to True. So there must be some other cause.

Tom H. said...

I'm glad that this turned up at the top of my Google search as it's exactly the problem I was having. In my case I turned off the validation so that I could drop and restore my database between testing my imports without having to get completely out of SSIS. It seems that SSIS grabs a connection to the database and won't let go.

Thanks for the research and heads up!

Jamie A. Grant said...

I actually bumped into this issue today and I'm using SQL Server 2008 R2. I finally resolved it by right-clicking on my OSL DB Source and selecting "Show Advanced Editor." Under the last tab, "Input and Output Properties" - under "OSL DB Source Output" and then "External Columns" - I found the mis-matched "Length" Property that I was looking for. A quick edit there fixed the truncation warning and got my utility working again...

Thanks for writing about this obscure issue!

Anonymous said...

And thanks for the SQL Server 2008 R2 solution. A happy end to frustrating afternoon :)