SQL Server–Business Intelligence Development (BIDS)–Warnings-Truncation May Occur due to inserting data from Data Flow Column

 

Background

Experiencing SSIS Package execution errors and so sought out understanding looking at the dbo.sysdtslog90 table.

 

Errors And Warnings

Here is what [dbo].[sysdtslog90] contains

Event Source Datacode Message
OnWarning PaymentAccount Transformation -2145348953 Truncation may occur due to inserting data from data flow column “card_lastfour” with a length of 100 to database column “card_lastfour” with a length of 4.
OnWarning PaymentAccount Transformation -2145348953 Truncation may occur due to inserting data from data flow column “card_firstfour” with a length of 100 to database column “card_firstfour” with a length of 4.
OnWarning StudentDiscountHistory Transformation -2145348953 Truncation may occur due to inserting data from data flow column “discountCode” with a length of 600 to database column “discountCode” with a length of 50.
OnWarning DTSAnalytics90 -2145348953 Truncation may occur due to inserting data from data flow column “discountCode” with a length of 600 to database column “discountCode” with a length of 50.

 

Remediation Process

Access the Data Flow and review each step.

Component

OLE DB Source

Advanced Editor – Input and Output Columns

For the OLE DB Source, there are two sets of Columns, the “External Columns” and the “Output Columns.

The “External Columns” is exposed by the OLE-DB Provider when queried by BIDs.

And, the “Output Columnsis maintained in house by BIDs in response to what was received upon quering the provider.

External Columns

Here is how the discountCode column is defined for the column cited.

BTW, the column cited is discountCode.

AdvancedEditor-OLEDBSource-ExternalColumns-DiscountCode-0620PM

 

Output Columns
Output Columns – Original

Here is the original settings for the discountCode column.

AdvancedEditor-OLEDBSource-OutputColumns-DiscountCode-0621PM

 

Output Columns – Revised

Here is the revised setting

AdvancedEditor-OLEDBSource-OutputColumns-DiscountCode-0622PM

 

Explanation:

  1. We changed the Length to 50

 

After Effect

The change will reverberate across dependant components.

Non-Conforming Lineage ID

Here is a sample error message:

Input column dest_discountCode has lineage ID 3632 that was not previously used in the Data..

 

InvalidColumnDueToLienage

 

To fix, approach each component, edit it by double-clicking on it and “Apply” previous changes.

RestoreInvalidColumnReferenceEditor

 

Ensuring the following:

  1. Include downstream invalid column references
    • The checkbox should be checked

 

OLE DB Destination

 

Here is the warning message we receive on the OLE-DB Destination

Control Flow

Image

 

dbo-PaymentAccount-TruncationWarning-Silo

dbo-PaymentAccount-TruncationWarning-Silo

OLE-DB Destination Control

OLE-DB Destination Control – Tab – Mappings

Here is the Mappings setting.

 

OLEDBDestination-Mappings

 

There is nothing indicative of column sizes.

 

XML File

Very, Very thankfully SSIS files are saved in XML Files.  And, so we can look at the file, see what is in it, and search through it.

And, so brought by an XML Editor and searched for the column name, card_lastfour, cited.

XML File – Student Sort

Here is the code for Student Sort ..

Image

ComponentStudentSort

 

Explanation
  1. We see that both card_lastfour and card_firstfour have a size of 100
  2. And, our target is only expecting 4
SSIS

Back to BIDs, let us look for the component referenced, “Student Sort

StudentSort-BIDS

 

Explanation
  1. We see that both card_lastfour has a size of 100, as well
  2. We will like to change to 4, but the Length is readonly in the Sort component

 

XML File – Component – Student Reference Merge Join
Here is the code for “Student Reference Merge Join”.
Image

ReferenceMergeJoin

 

BIDS – Component – Student Reference Merge Join

Before

In the merge join, we see our two suspect columns, card_lastfour and card_firstfour.

MergeJoinTransformationEditor-Before-20161026-0115PM

 

In Progress – Delete Columns

In the merge join, we will remove the suspecting columns, card_lastfour and card_firstfour.

We do so by selecting each column, right clicking on it, and choosing “Delete” from the drop-down menu.

In Progress – Delete Columns – Initiate Delete

MergeJoinTransformationEditor-InProgess-20161026-0120PM

 

In Progress – Delete Columns – Initiate Delete

MergeJoinTransformationEditor-InProgess-20161026-0126PM

 

Click the OK button to apply the changes.

 

In Progress – Re-add Columns

Here we re-adding the columns we removed earlier.

MergeJoinTransformationEditor-InProgess-Adding-20161026-0134PM

 

Summary

Using the GUI development tools, BIDs, we are able to find unaligned column sizes.

Unfortunately in some cases the tool does not display and avail the column sizes and one has to use an XML Editor or Text Editor.

When doing so search out the identified column and pay attention to the column length attribute.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s