Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Export SQL Server Data to Multiple Excel Worksheets using SQL Server Integration Services


By:   |   Last Updated: 2018-02-16   |   Comments   |   Related Tips: More > Integration Services Excel

Problem

I need to export different sets of data from SQL Server to different worksheets within the same Excel file. How can that be done using SQL Server Integration Services (SSIS)?

Solution

In this tip, I am going to demonstrate how can we export data from multiple SQL Server sources into multiple worksheets of an Excel file by using an SSIS package.

For this demo, I am going to use the ‘AdventureWorks2014’ database. I have created an Excel file which has two worksheets named Employees and Job Candidates. The SSIS package will retrieve data from [HumanResources].[vEmployee] and [HumanResources].[vJobCandidate] views and export them into the Employees and Job Candidates worksheets.

In this demo, I am going to perform the below tasks.

  1. Create a SSIS package and connection managers.
  2. Create a data flow task to pull data from SQL views and write to an Excel file.
  3. Test the SSIS Package.

Create SSIS Package and Connection Managers

For this demo, I am using SQL Server Data Tools 2015.

First, I will create a new Integration Services project. The project name is “HRData”.

ssis new project

After creating the new project, I am going to create a SQL connection manager and an Excel connection manager. To do this, right click the connection managers window and then click New OLEDB Connection as shown below.

ssis new ole db connection

After clicking on it, the Connection Manager dialog box will appear.

In the Server Name, select the name of the SQL Server instance that hosts the database which contains the data. Then configure the appropriate authentication type. Finally, in the Connect to a database drop down, select the database which you want to use. In this demo, I am connecting to a SQL instance which is installed on my local machine, so the Server Name is “(Local)” (you can use a hostname or an IP address). I am using Windows authentication as my authentication type and connecting to the “WideWorldImporters” database as shown below.

ssis connection manager

As a destination, I am going to use an Excel file, so I will create an Excel connection manager. For that, right click on the connection manager and select New Connection as shown in the below image.

ssis new connection

A dialog box with all connection managers will open as shown in the below image. Select the Excel connection manager and click Add.

ssis add connection manager

After adding the Excel connection manager, a dialog box will open. In that I will provide the path of the Excel file that I have already created named “HRData.xls” which is on my desktop.

ssis excel connection manager

Configure Data Flow Task

I created the connection managers, now I will setup the data flow tasks with two OLE DB sources to retrieve data from [HumanResources].[vEmployee] and [HumanResources].[vJobCandidate] along with that I will create two Excel destinations to write data to Employees and Job Candidates worksheets. To do that, I will add a Data Flow task in the Control Flow. Drag the data flow task from the Favorites tab and drop it onto the control flow window. Rename it to Export Table data to Excel File, as shown in the below image.

ssis control flow

Configure OLE DB Connections

In the data flow task, drag and drop two OLE DB Sources to the data flow window. Rename the first OLE DB Source to Employee Details and the second OLE DB Source to Job candidates. The data flow window will look like the below image:

ssis control flow

Now, double click on the Employee Details data flow task. A dialog box ‘OLE DB Source editor’ will open. As we have already created an OLE DB connection manager, I will select the connection which was created before from the drop down. After that we will configure the data access method. In this demo, I am going to retrieve the data using the following SQL Query. I will select SQL Command as the data access mode. In the command text, I will write the below query to retrieve data from the view.

SELECT BusinessEntityID, Isnull(Title,'') +' '+ Isnull(FirstName,'') +'.' +Isnull(MiddleName,'') +'.'+Isnull(LastName,'')+' ' +Isnull(Suffix,'') AS [Employee Name] ,
       JobTitle,
       PhoneNumber + ' (' + PhoneNumberType + ')' As [Phone Number],
       EmailAddress,
       AddressLine1 + ' ' + ISNULL(AddressLine2,'') as [Employee Address],
       City,
       StateProvinceName as [State / Province Name],
       PostalCode as [Postal Code],
       CountryRegionName as [Country / Region Name]
FROM   HumanResources.vEmployee
			

Paste the above query in the SQL command text. The OLE DB Source Editor will look like the below image:

ssis ole db source editor

Similarly, we will create another OLE DB Source using this query for the other data:

SELECT JobCandidateID, Isnull( [Name.Prefix],'') +' '+ Isnull([Name.First],'') +'.' +Isnull([Name.Middle],'') +'.'+Isnull([Name.Last],'')+' ' +Isnull([Name.Suffix],'') AS [Candidate Name] ,
       [Addr.Type] as [Address Type],
       [Addr.Loc.CountryRegion] as [Country / Region],
       [Addr.Loc.State] as [State],
       [Addr.Loc.City] as [City],
       [Addr.PostalCode] as [Postal Code]
FROM   HumanResources.vJobCandidate
			

The second OLE DB Source Editor will look like the below image:

ssis ole db source editor

Thus, I have created and configured both OLE DB sources, now let’s configure the Excel destinations.

Configure Excel Destinations

Now, I will add two Excel destinations for writing data to the different Excel worksheets. In the beginning, I have already created an Excel connection that I am going to use. Now I will drag and drop the Excel destination into the control flow as shown below.

ssis excel destination

Before configuring, I will rename both Excel destinations to Employee Details Worksheet and Job Candidates Worksheet. Now I am going to configure the data path. To do that drag the blue data path arrow from the OLEDB Source (Employee Details) to the Excel Destination (Employee Details Worksheet) as shown in the below image.

ssis control flow

Now double click on the Excel destination (Employee Details Worksheet), a dialog box will open as shown below:

ssis excel destination editor

We have already configured the Excel connection manager, so the default Excel Connection Manager will be selected in the Excel Connection Manager drop down. The data access mode will be table or view. As I mentioned in the beginning of tip, the Excel file has two worksheets named Employees and Job Candidates. In the first worksheet, I want to write data from [HumanResources].[vEmployee], so in the Name of the Excel sheet I will select Employees$. Now, I will map the table columns with Excel worksheet columns. To do that click on Mappings on the left pane of the Excel Destination Editor dialog as shown in the below image.

ssis excel destination editor

In the above image, the Input Column is the column name of the SQL Server view as well as the destination column for the Excel worksheet. In this demo, the column names in SQL Server and Excel are the same, so the Excel destination editor will automatically map the input and output columns.

I will repeat the data flow configuration process to write data for the [HumanResources].[vJobCandidate] view to the ‘Job Candidates’ worksheet. I want to write the job candidates to the Excel worksheet JobCandidates$. Again, the column names for SQL Server and Excel are the same, so the destination editor will automatically map the input and output columns.

Once I finish the Excel destination, the final SSIS package will look like the below image:

ssis control flow

SSIS Package Testing

Now let’s run the package and see how it works. To do that click on Start button on the menu bar. If the package completes successfully then it should look like the below image.

ssis control flow

The above image indicates the package inserted 290 records into the Employees Worksheet and 13 records into the Job Candidates Worksheet. Data in the Employees worksheet is shown below:

excel worksheet

The output for the Job Candidates is shown below.

excel worksheet

Our problem has been solved with this approach.

Next Steps


Last Updated: 2018-02-16


next webcast button


next tip button



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools