Remove Duplicate Records Using SSIS

 Remove Duplicate Records Using SSIS Step by Step

It is very easy task to remove the duplicate rows from the table using SSIS package. To remove the duplicate records in SSIS there are multiple ways and I am using SORT transformation. Below I am showing you a simple example for removing the duplicates.

Go to the BIDS >> choose new project >> choose Integration Service Project >> give your package name and press OK button.

Now inside the Toolbox from Control flow item select the Data Flow Task and drag and drop into design area.

Rename the Data Flow Task.

Double click on Data Flow Task OR Right Click on Data Flow Task and choose edit.

Inside the Data Flow Task choose OLEDB Data Source and Drag and drop it into design area.

Rename it.

Double click on OLEDB Data Source.

 OLEDB Data Source Editor will open. Now click on NEW button.

 Again a new Editor will open here we again click on NEW button to configure the connection from the Database.

Here we create  connection to Database. For this I am using AdventureWorks Database So I use our local Server and Database AdventureWorks. After choosing the Database just check that everything is ok or not by clicking Test Connection button. If everything is ok then press OK button.

Click on Name of the Table or View and choose the table then click on column to select the column.

Now choose the columns which you want to display and click on OK.

Drag the SORT Transformation and double click on it for configure.

Here enable the check box i.e. at the bottom of the SORT  Transformation to remove the duplicate row and press OK.

Now you can choose any destination to load the data. I am using derived column to show you the result.

Here Data Viewer Shows the output rows.

MOUSE HOVER REPORT OR TOOL TIP REPORT STEP BY STEP

MOUSE HOVER REPORT OR TOOL TIP REPORT IN SSRS

I am showing you that how to create Mouse Hover or Tool Tip report in this article.

Here is an example from  AdventureWorks Database.

SQL Scripts : 

SELECT     Person.Contact.ContactID, (Person.Contact.FirstName+' '+ Person.Contact.MiddleName+' '+Person.Contact.LastName) As FullName,

                      Sales.ContactCreditCard.CreditCardID, Sales.CreditCard.CardType, Sales.CreditCard.CardNumber, convert(varchar(10),Sales.CreditCard.ExpMonth) as months, Sales.CreditCard.ExpYear

FROM         Person.Contact INNER JOIN

                      Sales.ContactCreditCard ON Person.Contact.ContactID = Sales.ContactCreditCard.ContactID AND

                      Person.Contact.ContactID = Sales.ContactCreditCard.ContactID INNER JOIN

                      Sales.CreditCard ON Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND

                      Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND

                      Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID

                     where Person.Contact.MiddleName <> 'NULL'

                     order by ContactID

Select the Header Row of the table in the report.

Go to Properties Window.

Under the BackgroundColor property, Select color from drop down.

Repeat previous step for data row to set color for better look

Now select the ContactID column and go to the properties window.

Select Expression from Tool Tip

Set the expression for Tool Tip

Expression :

=("FULL NAME :  " & Fields!FullName.Value & chr(10)

 &"EXPIRY MONTH :  " & Fields!months.Value & chr (10)

 & "EXPIRY YEAR :  " & Fields!ExpYear.Value & chr (10))

Click Ok

Now Run the report

Above the preview of report.

Drillthrough report in SSRS Step by Step

Drillthrough report in SSRS

A Drillthrough is a kind of report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. The data in the drillthrough report is not retrieved until the user clicks the link in the main report.

Here I am showing you how to create Drillthrough Report.

For this I am creating two Report  i.e. Parent and Child Report.

Here is an example from  AdventureWorks Database for Parent Report.

SQL Scripts

SELECT        CustomerID, ContactID, Demographics, ModifiedDate

FROM            Sales.Individual

Now insert a table and  Format the column

And Now create a Child Report

Here I am also using  AdventureWorks Database for Child Report.

SQL Scripts

SELECT        CustomerID, ContactID, Demographics, ModifiedDate

FROM            Sales.Individual

WHERE        (CustomerID = @CustID)

Here I am creating a Parameter Custid.

Here both report created and now its time to map the parent report to child report. For this

We go to the parent report [CustomerID] column.

Right click on [CustomerID] column and choose Text Box Properties

Go to Action tab and map these:

Then Click on OK.

And now run the report. Below the preview of Drillthrough report.

Page Break at Nth row in SSRS Report Step by Step

We can design a SSRS report with a constraint of only N^th rows per page.

I am showing you that how to display only N^th rows in the report in this article.

Here is an example from  AdventureWorks Database.

SQL Scripts.

SELECT TOP 1000 [StateProvinceID]

      ,[StateProvinceCode]

      ,[CountryRegionCode]

      ,[Name]

      ,[rowguid]

      ,[ModifiedDate]

  FROM [AdventureWorks].[Person].[StateProvince]

Select the Header Row of the table in the report.

Go to Properties Window.

 Under the BackgroundColor property, Select color from drop down.

Now create a group.

After creating the group go to the group properties and click on fx button.

And set the expression for group properties.

Expression :

=Ceiling((RowNumber(Nothing)) / 15)

( Note : You can put N^th number in place of 15. For example if you want to your page break affect after each 25^th row than you have to put 25 in place of 15 )  

 Now, report will show 15 rows in a group. Next step is to define page break for the group. Go to page breaks tab and select page break option.

And now run the report. Below the preview of report.

Create SSRS report using Excel Data Source Step by Step

By creating a report using Excel as a Data Source we will follow the following steps ...

Right click on Shared Data Source folder and choose Add New Data Source. A new popup screen will open. Give the Data Source Name and Choose ODBC from the dropdown box.

Then Click on Edit button. A new screen will open. From the new screen i.e. Connection Properties go to the Use user or system data source name and from the drop down choose Excel Files .

Then Choose the Use Connection String and click on Build button. A new pop up screen will open . Click on New button.

Then click on Next button

Choose the Microsoft Excel Driver and click on Next button and then click on Finish button.

Then click on Select Workbook button and choose the file and click on OK button.

Click on OK.

Test the connection and press OK.

Click on OK.

Click on Reports folder and choose add new items and choose Report. Map your Data Source and click OK.

Right click on Dataset Folder and Write the query for the table. (I have used a very simple query .For ex.)

This is Your Report from Excel data source.

Alternating Table Row Color In SSRS Step by Step

Alternating Table Row Color  In SSRS Step by Step

Here is an example from AdventureWorks Database.

Sql Script.

SELECT TOP 1000 [StateProvinceID]

      ,[StateProvinceCode]

      ,[CountryRegionCode]

      ,[Name]

      ,[rowguid]

      ,[ModifiedDate]

  FROM [AdventureWorks].[Person].[StateProvince]

Select the Header Row of the table in the report.

Go to Properties Window.

 Under the BackgroundColor property, Select color from drop down.

Now, Select the Data Row of the table in the report.

Go to Properties Window.

Under the BackgroundColor property, select expression from the drop down.

Enter the expression "=IIF(RowNumber(Nothing) mod 2 = 0, "SeaGreen","Pink") " in the Expression Window.

Click OK.

Below is a Preview of a SSRS report with two different colors for alternate rows.

When should you use a Star and when should you use Snowflake Schema

Q: When should you use a Star and when should you use Snowflake Schema ?

Star Schema :  If the performance is the priority Than go for Star Schema, Since here dimension tables are denormalized.

Snowflake Schema : If memory space is the priority than go for Snowflake Schema, since here dimension tables are normalized.

Advantage and disadvantage of Snowflake Schema

Q: What are the Advantage and disadvantage of Snowflake Schema ?

Advantage of Snowflake Schema

• The main advantage of Snowflake Schema is the improvement of query performance due to minimized disk storage requirements and joining smaller lookup tables.
• It is easier to maintain.
• Increase flexibility.

Disadvantage  of Snowflake Schema

• The main disadvantage of the Snowflake Schema is the additional maintenance efforts needed to the increase number of lookup tables.
• Makes the queries much more difficult to create because more tables need to be joined.

Snowflake Schema

Q : What is Snowflake Schema in SSAS ?

A Snowflake Schema contains fact table and dimension tables, with the fact table storing facts about business transactions  and some columns of the fact containing foreign key references to the dimension tables. The dimension tables of a snowflake schema are typically normalized to the third normal form (3 NF) or higher. As a result the data for a given dimension is spread out over multiple tables, and a diagram of the database resembles a snowflake.

Difference between a data source and data source view

Q 3 : What is the difference between a data source and data source view ?

A Data Source represents a connection to a particular data source using one of the supplied providers. A Data Source View is an abstraction layer that you design using a data source. The latter also contains expressions that create calculated fields.

Difference between online and offline mode in SSAS

Q 2. What is the difference between online and offline mode in SSAS ?

Online mode means that you are directly connected to a particular SSAS database instance. Any changes you make are implemented on save. Offline mode means that means that you are creating metadata files that can be deployed to the SSAS server when you want. An important consideration for offline mode is that BIDS includes no granular conflict resolution.

Aggregation types are available for measures in BIDS

Q 4 : What aggregation types are available for measures in BIDS ?

The default aggregation is SUM. Other available aggregation types include the following: MIN, MAX, COUNT, DISTINCT COUNT, AVERAGE OF CHILDREN, FIRST CHILD, LAST CHILD, FIRST NON EMPTY, LAST NON EMPTY. You can also design custom aggregations using MDX.

What is star schema

Q 1. What is star schema in SSAS ?

A star schema is a type of modeling used as a basis for  building an OLAP cube. It consists of at least one fact table and many dimension tables. The rows in the dimension tables have a 1:M relationship with the rows in the fact table.

In a star schema every dimension table is related directly to the fact table.

Cube, Fact Table, Dimension Table

Q : What is Cube ?

A cube is a set of related measures and dimensions that is used to analyze data. A Cube is the primary storage mechanism for SSAS data. It consists of at least one fact table and many dimension tables. The rows in these tables have a key relationship to each other.

Q : What is fact table ?

A fact table is the center table surrounded by a dimension tables. All the dimension tables directly or indirectly connected with the fact table.

A fact table contains foreign keys to relate the rows in the fact table to the dimension table rows and facts. Facts are key performance indicators, for example net sales dollar, net sales unit, etc. Facts are usually numeric and additive.

Q : What is a dimension table ?

Dimension table consists of one or more source tables. Dimension tables have three types of columns. The first is the original primary key for each row. The second is a new primary key (Or surrogate key) for each row. This key is used to guarantee uniqueness, because the original data could come from multiple sources. The third is a column for each attribute that describes each dimensional value.

How many types Control Flow elements provides SSIS

Q: How many types Control Flow elements provides SSIS ?

SSIS provides three different types of Control Flow elements

i)                    Containers that provides structure in packages

ii)                   Tasks that provides functionality

iii)                 Precedence Constraints that connects the executables, containers and tasks into an ordered control flow.

SSIS Package, Tasks, Containers, Variables, Data Pipeline, Event Handler

SSIS Package, Tasks, Containers, Variables, Data Pipeline, Event Handler

SSIS Package :  A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, error and event handling and data destinations.

Containers :  Package objects that provide structure to packages and special services to tasks. Containers are used to support repeating control flows in packages and to group tasks. Containers can include other containers in addition to tasks.

Tasks : Package elements that define activities and processes, including data sources, destinations, transformations, and others.

Precedence Constraints :  Constraints that link executables, containers, and tasks within the package control flow and specify conditions that determine the sequence and conditions for determining whether executables run.

Variables :  Storage for values that an SSIS package and its containers , tasks, and event handlers can use at run time. The scripts in the scripts task and the script component can also use variables.

Control Flow :  An SSIS package process control component used to control flow elements that containers that provide structure in packages and services to tasks, tasks that provide functionality in packages, and precedence constraints that connect containers and tasks.

Data Flow :   An SSIS package data process  control component defined from within package control flow  that loads data from sources, transforms  and routes it through transformations, and saves it to destinations.

Event Handler :  An SSIS package process control component used to define the process activities to be performed at the time of a specific event state for the package or for any of its tasks or containers.

Data Pipeline :  The memory – based, multithreaded, buffered transformation process flow of data through an SSIS data flow task during package execution.

BIDS :   SQL Server Business Intelligence Development Studio. Provides the integration services project in which you create packages, their data sources, and data sources views. Read more ...