Search code examples
sqlssissql-server-2012truncated

Package fails only when executed in Execute Package Utility

I have a simple package which transfers data from normalized to denormalized db.

It runs fine on Visual Studio 2015 against remote dev server which is SQL Server 2012.

But when I upload it to dev server and run it using Execute Package Utility I get this error:

Text was truncated or one or more characters had no match in the target code page

Things checked:

  1. Checked the error column, it's the same type, length (varchar 250) & collate in source & destination databases.

  2. Installed SQL Server 2012 SP3 on dev server

  3. Googling this error I see it's typical to flat file import but I'm using OLE DB source & destination

  4. Package was upgraded from Visual Studio 2008 and ssis project is set to run against SQL Server 2012

  5. Redirecting error / truncate rows to a flat file yields nothing

  6. Inputs & outputs checked with advanced editor, all are [DT_STR] 250 & same code page

Any ideas? Thanks.

Solution

  • You should check the advanced editor by right clicking the OLEDB Source and destination components. Check their datatypes and length. They should match.

    Process :

    On your source task ,right-click - > Show Advanced Editor - > Input and Output Properties - > Source Output - > Output Columns - >Data Type Properties - > Data Type /Length

    Change ( Data Type /Length) in both source & destination in similar way as per your requirement.

    On your source task ,right-click - > Show Advanced Editor - > Input and Output Properties - > Source Output - > Output Columns - > TruncationRowDisposition .

    --Fail Component - To fail if truncation occurs

    --Ignore Failure - To ignore truncation error

    --Redirect Row - To redirect truncated row to desired destination

    You can check the rows also to guess the reason, If they are still failing

    I hope this could help you!