How to use SORT control in SSIS ? TIP #115

Dear Friends,

In the series of Learn SSIS step by step this is the 5th post. Now from this post we are going to use each transformation control one by one.

So, lets start with simplest one transformation control which is “Sort”.

By the name it is clear data it will sort the data which is provided to it and give sorted output as a result. Lets understand this by below step by step example. (Here I am not writing step to start visual studio and create a new project, I am pretty much sure you are aware of this now.)

Before going further let me tell you want we are going to do  here. We will have a input result (which will be a text file) and then sort it and save in another file.

Step 1:-I renamed package to SortPackage if you want you can rename your package as well. Now drag drop a Data Flow task as shown below and double click DATA flow task

Step 2:- When you click it you will get a data flow screen where you can drag drop FLAT file source. You can choose source assistance as well. Now configure this flat file source. Means give the path of the file which you want to have as a input source. I am taking a text file which contain fruits name in different order. as shown in below figure

Right click flat file source click on EDIT option and follow the screens

Step 3:- Now once the file is configure. We have to drag drop SORT control from SSIS tool box as shown in below figure.

I also added the output of flat file source to sort control. As shown in above figure. Now configure it. Click on Sort control and you will get following screen. As show in below screen you can sort the data on any column and in any direction like Ascending or descending.

Right there is only one input column which is name so we are sorting name in Ascending order as shown below.

Step 4:- Now once this configuration is done we will save the data in new file with new name which will be sortedFruits. Now to achieve this drag drop the destination control. So I took same float file destination as shown in below figure

Step 5:- Now configure this file destination. Which means where we need to save this file and what are the columns which we required. so In current case there is only one column which is Name (Fruit name) and I am saving this file at same place with sortFruits name. so lets configure it by clicking right click on flat file destination and click on edit button

Step 6:- Once this is configured we need to run this package by pressing F5.When you run hit F5 and everything going right then in execution screen at each step you will find green right check image as shown in below screen

Step 7: Now to cross check we will first see whether that file is created or not. If created then whether we have sorted data or not. So lets open the flat files.

So, if you see at provided destination location a file is created and the data inside this file is sorted file.

I hope you like this first transformation control. till then Enjoy!!!

Thanks !!

RJ!!

First_value & Last_Value according to group set is that easy ? tip #44

 

Problem:- 

Most of the time we require data in which we require first value and last value from different group of rows. Now how easy we can get result this is one of the challenge for us.

Solution:-

Lets understand this by an example. Suppose you have a sales table in which you maintain daily sales. Now your want a result sent in which you know what is first sale of the day and what is last sale of the day.

SQL SERVER 2012 provides you facility to achieve this task easily with First_Value & Last_Value function.

The syntax of first_value & Last value is exactly same as Row_Number, Dense_Rank & Rank_function.

See below example in which I have used Adventureworks SalesOrderHeader table.

Now if you see below snap I took a random date 2005-07-12 and fetched record and highlighted is first row & Last Row.

So on date 2005-07-2012 Sales order have 3953.9884 as a frist value and  772.5036 as a second value.

 

Now above specific result  we can achieve by first_Value & Last_Value function of SQL SERVER 2012 as shown below

so, In this way you can achieve the first_Value & last_Value from a group of rows

I hope this may help you somewhere.

Enjoy !!!

RJ

Interview Question- How conditionally sort the records ? TIP#43

 

Problem:-

Can we sort records according to particular condition ?

Solution:-

Most of the time developer faces this challenge of sorting records conditionally. I know  many of us faces this question in interview.

Lets understand this by an example.

Suppose I have a person table in database and  I want to sort the records while fetching from database. Records sorting is depended on a variable which is passed by the consumer from front end.

So if Sort variable 1 then we have to sort the records by  first Name

If Sort variable is 2 then we have to sort the records by Last Name

Else we have to sort by Middle Name

To achieve this I have created following stored procedure

CREATE PROCEDURE proc_SortPerson
@SortBy  TINYINT    --  if one then sort by first name if 2 sort by last name else sory by middlename
AS
BEGIN
  SELECT *
  FROM [Person].[Person] WITH(NOLOCK)
  ORDER BY (CASE  @SortBy WHEN 1 THEN FirstName
                       WHEN 2 THEN LastName
                       ELSE MiddleName
                       END)
END
GO

Now when  I execute this by specific parameter result sorted according to that variable value.

See below snaps for proof of concept

Sort by Last Name when @sortby value  = 2

You can add any condition according to your business need.

Thanks

Rj !!

Ranking by Rank() function then what about Row_NUBER() TIP #37

 

In last post we have discussed Row_Number() function. In similar way we have RANK function. By the name it is clear that we provide  some kind of ranking for rows.

It syntax is exact same like ROW_NUMBER().

Now the question comes in mind if it is same then why SQL SERVER introduce new function.

so there is slightly difference

ROW_NUMBER() will generate sequential number regardless of  duplicate rows in partition.

While  RANK() will generate sequential number for unique row in partition and use same sequence for duplicate and skip sequence which lies between duplicate.

For example in  below query we want to know total dues rank according to  territory and  modified date.

so we write following

SELECT TerritoryID,TotalDue,ModifiedDate,SalesOrderNumber ,RANK() OVER (Partition by ModifiedDate,TerritoryID ORDER BY TotalDue) ranks FROM [Sales].[SalesOrderHeader]

When we run it we get following result

enjoy !!!

RJ

Row_Number() function for providing sequence number as per your wish TIP #35

 

Sometimes we require sequence column or we can a row number column so we have ROW_NUMBER() function in SQL SERVER.

Lets understand this by following example

In this example we have  person table which have firstname, last name, middlename  columns. Now we want to fetch records  with a extra column ROW_Number() so we write following query

SELECT ROW_NUMBER() OVER (ORDER BY LastName) As ROWNO,FirstName,MiddleName,LastName  FROM [Person].[Person]

When we run above query we get following result set. If you see below result set we have one extra column which is ROWNO

Lets understand the query in this we are getting sequence or Row number order by Last name.

There is one more option with ROW_NUMBER which is partition by

lets understand this by below query what we want for we want row number for each record based on last name means whenever new last name introduce row number start again from one. to achieve this we have to write following query

SELECT ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY LastName) As rowNo,FirstName,MiddleName,LastName  FROM [Person].[Person]

See below snap when we run we get following result

If you see above snap you will find that when the Last name change row number also change so we did partition by last name means for each change.

I hope you might have used earlier but I thought It might be possible it will helpful to someone who not aware.

Enjoy!!

RJ

Get different records or random order at each time when you fetch record by newId() TIP#33

Hello friends,

Sometimes your project require whenever a page load or user search then each time you need to show a random order or we can say different records or order.

To achieve this we can use following command

SELECT * FROM yourtableName order by NewID()

in below snap I am fetching person table of Adventureworks database

 

So each time when I press F5 I get different records or order.

The only thing which need to remember here NEWID() make your fetch slow on large collection so the best idea is use it with selected record set.

I hope this will help you somewhere enjoy.

Thanks

Rajat Jaiswal