Redirect Duplicate Rows Using SSIS Step By Step

Redirect Duplicate Rows Using SSIS

There are many ways to redirect the duplicate into another direction. You can use Script Component, Lookup Transformation etc. I am showing you the simplest way to redirect the duplicate. For this, here I am using Fuzzy Group Transformation to redirect the duplicate into another direction.

Here, I am taking Repeat_Data table from my Employee Database. In this table I have created some duplicate records for better understand.

Now Go to BIDS, Start New projects, Name the Project.

Take Data flow Task and double click on it.

Take OLEDB Data Source, double click on it and configure it.

Choose Table Repeat_Data.

Go to Columns and take all columns.

Take Fuzzy Group Transformation and double Click on it. A Fuzzy Group Transformation editor will open. Leave the first tab that is Connection Manager . Click the second tab i.e. Columns and configure it as directed below.

 Make the Minimum Similarity of name column to 0.25.

Go to Advanced tab and set the Threshold to 0.50 and press OK button.

Take Conditional Split Transformation and double Click on it and write Condition.

Condition: _key_in == _key_out

Make the Default output name as Duplicate Rows.

Your all configuration has completed now you can take any destination where you want to keep your unique records and where you want to keep your duplicate records. Here I am taking Multicast Transformation for just showing you the output of this configuration with the help of Data Viewer.

It is the Preview of your Package.

(Note: It is very easy way to redirect the duplicate into another direction but it has a drawback. It is a blocking transformation so if you use it then may be  you have some performance issue.)

Dynamic Column Report In SSRS Step By Step

Dynamic Column Report In SSRS

I am showing you how to create Dynamic Column Report. It means when a user select two column the report will display only two columns and if they select five columns then it will display five columns. So we can say that the report will display the field on the basis of user selection.

For this we have to create two datasets.

Here is an example from Northwind Database.

Create first dataset named Hussain_Dataset.

SQL Scripts:

SELECT        CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone FROM  Customers

Insert a Table in design area and create a Table Like that: 

Now go to the dataset and create another dataset  Hussain_Dataset1

SQL Scripts:

SELECT        1 AS ID, 'Customer Id' AS CustomerID

UNION

SELECT        2 AS ID, 'Company Name' AS CompanyName

UNION

SELECT        3 AS ID, 'Contact Name' AS ContactName

UNION

SELECT        4 AS ID, 'Contact Title' AS ContactTitle

UNION

SELECT        5 AS ID, 'Address' AS Address

UNION

SELECT        6 AS ID, 'Postal Code' AS PostalCode

UNION

SELECT        7 AS ID, 'Country' AS Country

UNION

SELECT        8 AS ID, 'City' AS City

UNION

SELECT        9 AS ID, 'Phone' AS Phone

FROM            Customers

Now create a parameter DCParamaeter and click the Allow Multiple value.

Go to the Available Values and choose Get values from query and set dataset to Hussain_Dataset1 , Value Field CustomerID, Label Field CustomerID.

And Now the time to set the Column Visibility. For this we go to first column of the Tablix and right click after that we click on Column Visibility option.

A new popup box will appear. Here we choose Show Or Hide Based On Expression and click on fx button.

A new popup box will appear here we write the expression.

Expression :

=IIF(InStr(JOIN(Parameters!DCParameter.Value,","),"Customer Id")>0,False,True)

We have to perform this step to each column of Tablix.

(Note:Don’t forget to modify the expression for the respective column name accordingly.)

Your Dynamic Column report has completed. Now run the Report. Here is the some preview of your Dynamic Column Report.