SQL Server Home
Incorrect Truncation Warning in SSIS Data Flow
2/7/2008 7:42:29 AM
User(s) can reproduce this bug
When a string-based source column in an SSIS data flow is smaller than a destination column being populated via an OLE DB Destination, if the ValidateExternalMetadata property for the OLE DB Destination is set to False, SSIS will incorrectly warn that truncation may occur. In this situation, SSIS seems to get the metadata for the source column and the destination column mixed up.
SQL Server 2005 SP2 - Developer Edition
Integration Services (DTS)
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
1. Create a test table in the AdventureWorks database to hold rows from the Production.Product table. Use the schema from the Production.Product table, but make the ProductNumber column nvarchar(50) versus nvarchar(25).
2. Create a new SSIS package with a single Data Flow Task.
3. In the Data Flow Task, add an OLE DB Source with a Connection Manager to the AdventureWorks database. Once connected, select the Production.Product table as the source table.
4. In the Data Flow Task, add an OLE DB Destination, using the output of the OLE DB Source as the input. Connect the destination to the AdventureWorks database and select the new test table as the destination. Map the columns from the Production.Product table to the new test table.
5. Note that the ProductNumber column in the Production.Product table is nvarchar(25) while the ProductNumber column in the new test table is nvarchar(50). Thus, no truncation will occur as data flows from teh source table to the destination table.
6. Select the OLE DB Destination and review the properties of the destination. Set the ValidateExternalMetadata property of the destination to False (versus the default of True).
7. The package should now be showing a truncation warning for the OLE DB Destination. The truncation warning will state that truncation may occur as data moves from the source column with a length of 50 to the destination column with a length of 25 (which is backwards).
A truncation warning states that data flowing from the source column may be truncated as it goes to the destination column. This, however, is backwards in that the source column is actually smaller than the destination column. Note that the warning is only produced when the ValidateExternalMetadata property for the OLE DB Destination is set to False.
No warning message should be generated.
to post a comment.
Please enter a comment.
on 2/8/2011 at 8:39 AM
I still have this problem while using Katmai. Could you guys give it another look?
on 4/16/2008 at 7:57 AM
I have a similar problem with a substring / concatenation issue in SSIS (2005 SQL Server). OLE DB Source & Destination, no middle tasks. I use a CTE to concatenation an AreaCode (varchar 3) to a Telphone (varchar 7) in the following manner:
agyAreaCd + '-' + Substring(agyTelephone,1,3) + '-' + Substring(agyTelephone,4,7) as Phone
This is only 12 characters. I have verified this by putting the same string ina LEN() function in the original query. However, when I put it in my OLE DB Source, the Metadata incorrectly reflects this DT_STR datatype as being a length of 15 instead of 12 and warning me that it may truncate by being put in my destination column of varchar 12.
ValidateExternalMetaData in both Source & Destination is set to TRUE, so it isn't quite the same issue. However, using LTRIM () & RTRIM () around each of my fields & the hypen does NOT resolve the problem. And if I try to change the External Column size at the Source, it complains about truncation at that point. I do not know how to resolve this issue. Please look into this.
on 3/11/2008 at 2:56 PM
Thanks for your feedback. We appreciate it.
We have investigated and fixed this issue. You should try out the upcoming SQL Server 2008 (Katmai) CTP-Refresh build in April/May timeframe to verify whether this issue is fixed or not. Please reactivate this issue if you still hit this problem on Katmai CTP-R build.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft