SSIS Performance Enhancement- Remove Sort Column Additional Steps

Writing again after a long time, today while working on a SSIS package i came across a situation where i have a Type2 Dimension and i was getting duplicates rows to the table the only difference was BUsiness Key column was behaving as a case sensitive column i.e. if any row on source was found to change in case the code was treating this business Key as a new row and inserting whereas i needed it to ignore case.

Now here is the reason why it was happening and the resolution,

Cause: I was using the best practices for my Merge Join on the package wherein i have removed the SORT column and to achieve the same i was ordering it on my SOurce Trasnform and marking the ISSorted property and sorting the dataset from source. For details on what i mean read this

https://technet.microsoft.com/en-us/library/ms141031%28v=sql.105%29.aspx

My Data Flow

DataFlow

My SQL server is case insensitive, and SSIS takes by Default windows Sorting, so i was getting the case sensitive row as a new row as the key column changed from ABC to abc,

So to get this resolved what we have to do is set the, comparison Flag property to IGnore case and this would lead to ignore of case for the Key column, as per my DFT above this would have to done on both the source transform, such that setting is identical on both of them

Comparison_Flag

The link helped me resolve it:

http://microsoft-ssis.blogspot.com/2011/03/sorting-in-sql-vs-sorting-in-ssis.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: