SSIS- Transform SQL table records to Excel with Dynamic Name

SQL Server Integration Services (SSIS) is used to perform a data migration task.

Let's create a simple package. if you have SSIS installed on your system then you can see Integration service project while creating new project in Visual Studio.

Step 1:

Open Visual Studio => Go to Menu => File => New => Project. It will open the dialogue below. Select Integration Service Project.


















It will create new blank SSIS project.


Step 2:
Go to project Menu and select SSIS Import and Export Wizard as shown in below screenshot.















It will open the wizard below and press next



Step 3:
Choose a data source, Select SQL Server Native Client as shown in below screen shot. Set the source database connection string and press Next.

Step 4:
Choose a destination, Select the Excel File Path where you want to place the file. Select the excel version as well and Press Next.

Step 5:

Select the option to write the query and press Next.

Step 6:

Write you SQL statement below. you can run the stored procedure as well.
Create the stored procedure and write EXEC Procedure Name. Press Next

Step 7:

It will automatically convert the datatype to excel format datatype. Press Next.

Step 8:

Press Finish and Complete the wizard.

Step 9:

Once you close the wizard, you can see it will automatically create Data Flow Task as shown in snapshot below.

When you click Data flow tab, you can see it has 3 things.

A. Source (SQL table)
B. Data Conversion (That will convert datatypes)
C. Destination (Excel File Columns)

Step 10:

Run the project, it will generate the excel on specific location that you mentioned on Step 4.

How to give random name to excel file every time you run the SSIS package?
Click on link below

Excel name with time stamp

That is all about the article. Hope you like this.

How to give random name to excel file every time you run the SSIS package

If you don't know how to create SSIS Package then go through below link first.

Create SSIS Package

Now, I'll explain how to give random name to excel file with time stamp.

Step 1:

Right click on the Destination excel connection (Excel Connection Manger) and click properties as shown in the below snapshot.




Step 2:


Click on the Expressions as shown in snapshot below.

Step 3:


Click on the ExcelFilePath in the property dropdown and set the expression

In expression we are concatenating time stamp  with excel file name. As example below

"F:\\Client_"+
RIGHT("0" + (DT_STR,2,1252)DATEPART("MM" ,GETDATE()), 2) + "-"+
RIGHT("0" + (DT_STR,2,1252)DATEPART("DD" ,GETDATE()), 2)  + "-"+
(DT_STR,4,1252)DATEPART("YYYY" ,GETDATE()) +  "_" + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) + "" +  Right("0" +  (DT_STR,4,1252) DatePart("n",getdate()),2) + ".xls"


Now when you run the package, every time it will generate excel file with time stamp.
This is all about the article.

Typing Tips & Tricks

Typing Tricks

Below are the few useful typing tricks that help you to speed up your work.

1) Delete the word  

As you all already know by pressing the Backspace will delete single character. However, pressing the CTRL + Backspace will delete the whole word behind the cursor. It is very helpful  particularly Microsoft programs like Word or Outlook.

2) Paste plain text that you copied  

Usually when you copy text from any website or any other source, it will usually copy formatting(means font size and style) that comes with it. To paste this as plain text, press CTRL + ALT + V instead of the standard CTRL + V, and the system will show a 'paste special' dialog box. Select option and system will paste unformatted text.

3) Move Cursor to beginning of the next or previous word  

  

To speed up your work, move the cursor around with keyboard shortcuts. To move it to the beginning of the previous word, use CTRL + Left Arrow. To move it to the beginning of the next word, use CTRL + Right Arrow.



This is all about article. Hope you like it.

Change Rows to Columns - Pivot in SQL Server

PIVOT

In this article, we will discuss PIVOT keyword in Sql Server. Pivot is responsible to  convert Rows to Columns in Sql Server.

Let's take the example.

Step 1: Declare SQL table and add some dummy data

DECLARE @ScoreTable TABLE
(
MatchType VARCHAR(50),
Year INT,
Score  INT
)

INSERT INTO @ScoreTable VALUES('OneDay',2012,1000)
INSERT INTO @ScoreTable VALUES('Test',2012,2200)
INSERT INTO @ScoreTable VALUES('OneDay',2013,800)
INSERT INTO @ScoreTable VALUES('Test',2013,370)
INSERT INTO @ScoreTable VALUES('Test',2014,1100)


Step 2: Run the Select query and see the default data

This is not mandatory step. However, just check you dummy data.

SELECT * FROM @ScoreTable

In above query, you can see we have 3 columns. MatchType, Year and Score.
We will change Year Rows data in Columns.


Step 3: Write query to covert rows to columns


SELECT * FROM @ScoreTable
PIVOT(SUM(Score)
FOR Year IN ([2012], [2013],[2014])) AS PVTTable




In above snapshot, you can see query changed year to columns and score under each column.


This is all about the article. I hope you like it.

Parent and Child Relationship in the Same table - SQL Server

Parent and Child Relationship

In this article, I'll explain the parent and child relationship in the same table.

Let's take the example by declaring the SQL table and insert some records.

Step 1: Declare the table and add some dummy data

DECLARE @table TABLE
(
Id INT,
Name varchar(50),
ManagerId INT

)
INSERT INTO @table VALUES(1,'Sunny',2)
INSERT INTO @table VALUES(2,'Nitin',4)
INSERT INTO @table VALUES(3,'Amit',5)
INSERT INTO @table VALUES(4,'Sumit',1)
INSERT INTO @table VALUES(5,'Jay',null)

SELECT * FROM @table

When you run the query and you will find below result

In the above snapshot, User belongs to the following manager. We need output as below.

Sunny - Nitin
Nitin - Sumit
Amit - Jay
Sumit - Sunny
Jay - No Manager Assigned


Step 2: Write a query to achieve the desired result.

1. With Help of Join

SELECT a.Name,b.Name as ManagerName
FROM   @table a left join @table b on
 b.Id = a.ManagerId

2. With Help of Sub Query

SELECT a.Name, (SELECT b.Name
                       FROM @table b
                       WHERE b.Id=a.ManagerId)as ManagerName
FROM   @table a

Step 3: Output would be the same for the above 2 queries.




This is all about the article. I hope you like it.

New in Visual Studio 2015

Features of Visual Studio 2015

There are many features in Visual Studio 2015. In this article we will discuss top 5 features of Visual studio 2015.

1. Debug Lambda Expressions

The best feature in VS2015 that developer is waiting for so long. Earlier we were not able to debug Lambda expression. Now you can debug the code and see the result in Immediate window as shown in below snapshot.




2. Yellow Light Bulb


This feature is similar to Resharper. It will give you suggestions. Here in below shapshot when you click on Yellow bulb, it will show some suggestion related to missing reference.

3. IntelliSense for Angular


Visual studio will display the Intellisense for Angular. Just write ng in div tag. It will show the list of ng tags as shown in screenshot below.

4. IntelliSense for Bower and NPM

In Asp.Net 5 project, you will find Bower, Gulp and NPM in new template.
Microsoft provide Intellisense for both Bower and NPM.

Bower:  Bower is for client side packaging for e.g. Jquery and Angular.
NPM: NPM is for developer tools for e.g. Grunt, Gulp

5. References Counts

On the top of each Method, Property and Class you will find reference count. This feature is available in Visual Studio 2013 updated version. However, you will find here in Visual Studio 2015 as well.



When you click on 4 references text, you will see list of references as shown in screenshot below.

This is all about the article. I hope you like it.

SQL Server - Caluclate column sum on each Row

Running Total in SQL Server

In this article I'll explain how we can get running total on each row.

Let's take the example.

Step 1: Declare Sql Table

DECLARE @TempTbl TABLE  

(

  Id INT IDENTITY(1,1),

  Score INT

)

Step 2: Insert dummy records in the table

INSERT INTO @TempTbl(Score) VALUES(10)

INSERT INTO @TempTbl(Score) VALUES(20)

INSERT INTO @TempTbl(Score) VALUES(30)

INSERT INTO @TempTbl(Score) VALUES(40)

INSERT INTO @TempTbl(Score) VALUES(50)

Step 3: See the records in table

This is not mandatory step. However, check everything is fine.

SELECT * FROM @TempTbl



Check you table has above records.

Step 4: Write the query to get running total

There are many ways to get running total. I'll explain 3 ways.

1. With help of Sub query

     SELECT Id, Score,

(

   SELECT SUM(b.Score)FROM @TempTbl b 

   WHERE b.Id <= a.Id

 ) as Total

     FROM   @TempTbl a

2. With help of Join
     

    SELECT a.Id,a.Score,SUM(b.Score) as Total

    FROM   @TempTbl a, @TempTbl b

   WHERE b.Id <= a.Id

    GROUP BY  a.Id,a.Score

3. With help of Over (Only for SQLServer 2012 and above)

     SELECT a.Id, a.Score, SUM(a.Score) OVER (ORDER BY a.Id) as Total

     FROM   @TempTbl a

All 3 options will give the same output as shown in below screenshot. See the Total column, it is showing running total of Score column.



That is all about this article. I hope you like it.