Search This Blog


Thursday, July 30, 2015

Export Data from SQL to Excel Sheet using SSIS

Export Data from SQL to Excel Sheet using SSIS

Exporting Data from SQL table to Excel is a simple process using SSIS.

Below are the Steps that needs to be followed

First create a table in your database using below SQL script.

CREATE TABLE [dbo].[Employee](
       [Empid] [int] IDENTITY(1,1) NOT NULL,
       [EmpName] [nvarchar](50) NULL,
       [EmpSalary] [float] NULL
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'Amit', 5000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'Sumit', 6000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'Raj', 8000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'vijay', 9000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'suresh', 10000)

Requirement- we will export the Data of this sql table into excel file 

Open SSIS follow the below Step one by one.

Open BIDS(Visual Studio)

Click on New Project

Select Business Intelligence Project

Select Integration Service Project

Right click SSIS Packages

Create new SSIS Package

Give a name to the Package otherwise by default it will take Package1.dtsx

Control Flow

1-Go to Control flow(Left Side)

2-Drop a data flow Task

3-Name the Data flow Task as Exporting Excel Data (Optional ) as below Screenshot.

Data Flow Task

1-Now open the Data flow task by double clicking on it.

2-Drop  OlEDB Source

3-Drop Excel Destination as below Screenshot

Creating Connection for Source and Destination

1-Double Click OlEDB Source

2-Create a connection with your SQL Database as below screenshot.

3-Next we will create connection with excel Destination

4-Double click Excel Destination

5-Click on New(Excel Connection Manager)

6-Select the Path where you want to keep you file.

7-Give Name for the output file as ExcelData.xls

8-Now we will create sheet in the excel file.

9-Click on New(Name of Excelsheet)

10-It will automatically create a sheet for you with the name Excel_Destination

11-Once done click ok 

Next we will connect the Oledb Source with Excel Destination by dragging the blue arrow from Oledb source to Excel Destination

Once source and destination connected Run the Package by Right clicking the package and executing it.

Open Excel sheet to verify the Result. The data from Employee table has been exported to excel file as below screenshot.

Write to comment box if you are facing any issue while exporting the data from sql to excel file.

Sunday, June 28, 2015

SQL Server useful tips, tricks and shortcuts

SQL Server useful tips, tricks and shortcuts

For Below Headings we will display you some SQL Server useful tips,tricks and Shortcuts.

1. Display Line Numbers in SQL Editor
2. Comment SQL Code Shortcut
3. Uncomment SQL Code
4. SQL Code into Upper Case
5. SQL code into Lower Case
6. Add/View/Delete Bookmark in SQL

Display Line Numbers in SQL Editor

While working with large SQL Code it’s better to display line Numbers so that if in case you will get any error you can find the exact line Number.

1.       Open SSMS
2.       Click Tools-Options
3.       Expand Text Editor
4.       Click on Transact-SQL
5.       Check Box-Line Numbers
6.       Ok –As in Below Screenshots.

Comment SQL Code Shortcut

There are two ways to comment your SQL code.
  1.       Using  tool bar
  2.     Select your code
  3.     Click on icon as in screenshot.

2nd Method to comment SQLcode    
  1.       Select your code
  2.       Press CTRL+K,CTRL+C
         This will comment your entire Selected Code.

Uncomment SQL Code Shortcut

There are two ways to uncomment your SQL code.
  1.   Using  tool bar
  2.   Select your code
  3. Click on icon as in screenshot.

2nd Method to Uncomment SQL code
  1.    Select your code
  2.      Press CTRL+K,CTRL+U
      This will comment your entire Selected Code.

SQL Code into Upper Case

In order to follow proper SQL coding Standards, sometimes you need to change your code into Upper case.
  1.   Select your SQL code that you want to change into Upper Case
  2.    Press CTRL+SHIFT+U
   All Selected Code will get Converted into Upper case

SQL Code into Lower Case

 In order to follow proper SQL coding Standards, sometimes you need to change your code into Lower case.
  1.       Select your SQL code that you want to change into Lower Case
  2.       Press CTRL+SHIFT+L
        All Selected Code will get Converted into Lower Case


Add/View/Delete Bookmark

Sometimes while working with SQL Code you want to bookmark a Specific text .Wherever you will have that specific text in the code it will add a bookmark to that line as below screenshot.

Add Bookmark

  1.       Press CTRL+F
  2.      Click –Bookmark ALL

To view Bookmark Window

  1.        Press CTRL+K, CTRL+W
  2.     2nd way Go to View Menu-Click on Bookmark Window.

To Delete Bookmark Window

  1.        Press CTRL+K, CTRL+L
  2.      From Bookmark window delete all bookmarks as below.

Sunday, May 31, 2015

Drill down two columns in a single column in SSRS

Drill down two columns in a single column in SSRS

As an active user of various forums and groups someone had a query regarding SSRS Report Design Output.I found the question interesting so I am writing a post of it in my website.
Below is the question asked by user?
I have a table with country names,state names and population.
There should be an expanded or collapsed (i.e drill down) on country Names. So, when I collapsed the countries Name it should show sum of population country wise when I expand on country Names it should show population state wise, with the exception that when we expand country name it should open state Names under country name column not in a new column as below screenshot.

Requirement Screenshot.

Issue Solution -If you want to try the same use the below script the below script to create the table

CREATE TABLE [dbo].[country](
       [countries] [nvarchar](50) NOT NULL,
       [states] [nvarchar](20) NULL,
       [popvalue] [int] NULL

INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'ind', N'M.P', 210025)
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'ind', N'U.P', 3005605)
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'ind', N'A.P', 24563)
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'AUS', N'MEL', 23695)
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'AUS', N'SYD', 36985)
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'AUS', N'MEL', 21478)
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'usa', N'L.A', 96325)
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'usa', N'L.V', 31568)
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'usa', N'TEXAZ', 456328)

Next create dataset in your Report calling the above table.

 1-Add three columns countries state and Population in your dataset

2-Create a Parent Group-Group by Country

 3- Add Country fields above States

 4- Delete the Column generated after creating Parent Group

 5-Delete the country column also.
6- Set the visibility of Details to be Hidden and set the Display can be toggled with item-Select countries text box which you added above state as below screenshot..

Design Screenshot

Once you are done preview the Report .

 Countries and State Name will come in a single column as required screenshot.

Note: Drilldown two columns in a single column is little complicated process .So, feel free to ask me if you have same requirement like this I will be happy to help you.

Tuesday, April 28, 2015

Display comma Separated Values in SSRS Report

Display comma Separated Values in SSRS Report

Issue-How to Display comma Separated Values in SSRS Report.
Being a SQL and BI Developer I am quite active in various SQL and BI related groups including my own SQL and BI group.So there one of the member of the group asked be the below Question.

IN SSRS I have a table like this 

Id cellNo 
1 9xxx
1 8xxx
1 7xxx
2 9xxx
2 8xxx
3 7xx
4 9xxx

 output Required.

Id cellNo
1 9xxx,8xxx,7xxx
2 9xxx,8xxx
3 7xxx
4 9xxx

Ast it’s a common Requirement with lots of Geeks who are working with SSRS so I thought of writing a post on this  in my Site.

Solutions-In order to get the values as comma separated first we will create a test table with above records.
CREATE TABLE Number(Id int, CellNo varchar(100))
( 1, '9xxx'),
(1, '8xxx'),
(1, '7xxx'),
(2, '9xxx'),
(2,' 8xxx'),
(3, '7xxx'),
 SELECT   * FROM  Number 

So once we have created a table we will create a Datasource and dataset in the SSRS Report and will show the same table in SSRS Report
Now in order to get the same format as required go to design view of Report.

Write the below Expression in the cell highlighted in Yellow.

=Join (LookUpSet (Fields!Id.Value, Fields!Id.Value, Fields!CellNo.Value, "DataSet1"), ",")

Preview the Report

Now you are able to see the desired Result with comma separated values Group on Id.But you will notice that there are duplicates rows coming .

So, In order to Remove duplicates Records in SSRS .

Go to design view of Report

Select the second Row

Click on Row Visibility

Select show and hide based on Expression as in Screenshot.

Write the below Expression

=iif(Fields!Id.Value = Previous(Fields!Id.Value), True, False)

Click ok

Preview the Report.

We will get the exact desired format without any duplicate Records.

Note:If you want to Get the same format in SQL check my older post on same. 

Tuesday, March 31, 2015

SQL Server Job-Creating and Scheduling a SQL Job with an example

SQL Server Job-Creating and Scheduling a SQL Job with an example

In my previous post I have showed how to execute a sql script using a batch file. This post is about executing and automating  sql script using SQL Jobs
First we will create a test table and we will truncate the table using the SQL Job.

We can schedule a SQL  job to run at specific point or to run recursively after some time frame or we can schedule it daily, weekly, Monthly  quarterly and so on.

Below is an example :-

First we will create a database and a table and we will truncate that table using SQL  Job
--Below is the table and test script

create database test

create table test
(id int )

insert into test values (1),(2),(3)

Now we will truncate the table using SQL Job and will automate it to run on daily basis. For that we will write the truncate query as per the requirement. Here I will show an example of truncating table by scheduling a job.

We can consider a scenario where we need to truncate the table automatically on daily basis at 8 P.M so we will create a Job that will run on daily basis and will truncate the data daily daily at 8 p.m.

First Step

In Object Explorer Go to SQL Server Agent-Jobs
Note : Make Sure SQL Server Agent is running on your instance otherwise you won’t be able to see Jobs.
Right click on Job
New Job as in below screenshot.

Second Step
Give a Name to a Job and add a description as below Screenshot

Third Step
Go to Steps add a new step as below screenshot
-Give Step a Name
-Type-T-SQL  (As we are directly running a sql script in the job)
-Database-Select the database in which we are truncating table
-Command-Write or paste your SQL Code as in below screenshot.

Truncate table test

Step 4-Schedule the Job

-Click on New Schedule
-Give Job Schedule a Name
-Schedule Type-Recurring as we want to truncate the table daily at 8.PM
-Recurs every-1 Day
-Occur Once at -8 P.M as in below screenshot.

Schedule Type Description as below. 

Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started. 

Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition. 

Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog. 

Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog

Now you are done with your Job If you want any Alerts if Job fails and Notification to be send after completion of Job then you can add those steps also.

Target- If in case you want to target the Job in more than once server you can configure that also

A Target server allows the job to be run against a different server than the one it is defined on. 

Now once done click ok

In order to verify Job Right click on Job and run job you will get success message as below.

Now after testing schedule your job as per requirement

NOTE: SQL Jobs are not only limited to SQL Scripts you can create jobs to run SSIS Packages, analysis Services queries, Power shell commands and many others.