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 with Integration Services


By:   |   Read Comments   |   Related Tips: More > Integration Services Excel

FREE Webcast > 5 Easy SQL Server Query Performance Boosters


Problem

I have a SQL Server table and I need to export the data to different Excel worksheets based on some conditions of the data. How can this be done with SQL Server Integration Services (SSIS)?

Solution

In this article, I explain how can we divide and export data from a SQL Server table into multiple worksheets for an Excel file by using a SSIS Conditional Split transformation.

What is a Conditional Split Transformation in SSIS

The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is like a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output. This article explains how the Conditional Split Transformation works.

Moreover, I am going to show how to use a conditional split transformation to divide data retrieved from a table in a SQL Server database and save it to multiple worksheets of an Excel file.

For this demo, I will use ‘WideWorldImportors’ database. I will export data from the [Application].[Countries] table to Excel. I have created an Excel sheet named ‘WorldWide_Importors.xls’ with two worksheets named ‘High Population’ and ‘Low Population’. Data will be divided based on the value of total population column. I have generated an average population by using the below query.

select AVG(LatestRecordedPopulation) from Application.Countries

If the value of LatestRecordedPopulation is less than the average of the LatestRecordedPopulation than it will be stored in the ‘Less Population’ worksheet and if the value of LatestRecordedPopulation is greater than the average of LatestRecordedPopulation then it will be stored in the ‘High Population’ worksheet.

In this demo, I will perform the below tasks.

  1. Create an SSIS package and create OLEDB as well as Excel connections for the SSIS Package.
  2. Create a data flow task, which has:
    1. SQL Database as a source
    2. Conditional Split transformation to split the data
    3. Excel file as a destination
  3. Test the SSIS package.

Create SSIS package and Connection Manager

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

First, I will create a new Integration Services project named “ConditionalSplitDemo”.

ssis new project

After creating the SSIS project, I am going to create SQL Connection Manager and Excel Connection Manager. To do this, right click the Connection Managers window and then click New OLEDB Connection as shown below.

ssis new old db connection

After clicking on it, the Configure Connection manager dialogue box will appear. In the Server Name, select the name of the SQL Server instance that hosts the database which contains the data you want to use. 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 SQL instance which is installed on my local machine, so the Server Name is “Local” (you can use hostname OR IP address). Using Windows Authentication as my authentication type and connecting to the “WideWorldImportors” database, as shown below.

ssis connection manager

Now, as a destination, we are going to use an Excel file, so you must create an Excel connection manager. For that, right click on 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 Excel Connection Manager and click Add.

ssis add connection

After adding the Excel Connection Manager, a dialog box will open. In that you should provide the path of Excel file where you want to save the data. I already created an Excel file “WorldWide_Importors.xls” on my desktop which I am going to use for this demo.

ssis excel connection manager

Configure SSIS Data Flow Task

Configure OLEDB Connection

As I have created the Connection Managers in the SSIS Package, we can setup our Data Flow to retrieve data from the [Application].[Countries] table and divide it into multiple sets and store it into different worksheets. To do that, we should add a Data Flow task in our Control Flow. Drag the Data Flow Task from Favorites to the Control Flow and rename it to “Export Data to Excel Worksheet”, as shown in the below image.

ssis data flow task

After you have added the Data Flow task, you should configure the OLEDB Source. To do that double click the component to open the OLE DB Source Editor. As we have already created an OLE DB connection manager, it will be the default value for the OLE DB Connection Manager drop down list. We are going to use SQL Command to access the data in the table, so select SQL Command and write the below query in the SQL Command Text box as shown in the below image.

ssis oledb source editor

The query we will use is:

select CountryID, CountryName, FormalName, CountryType, LatestRecordedPopulation 
from [Application].[Countries]   

Configure Conditional Split

Now we will add the Conditional Split transformation to the Data Flow. To do so, drag the Conditional Split from the Common section as shown in the below image.

ssis conditional split

Now we need to connect OLEDB source to the Conditional Split transformation. Click on the blue arrow at the bottom of ‘Export Data to Excel Work Sheet’ and drag it to the Conditional Split transformation as shown below.

ssis steps

Now I will configure the Conditional Split transformation. As I mentioned previously, I will divide data from the [Application].[Countries] table based on the average value of ‘LatestRecordedPopulation’ column and store them in different worksheets. To do this, double-click on Conditional Split transformation and a dialogue box will open as shown below.

ssis conditional split editor

The Conditional Split transformation editor will provide the below functionality to define how to split the data.

  1. Columns and variables
  2. Functions
  3. Conditions

For this example, we’ll define our condition based on the ‘LatestRecordedPopulation’ column. So first expand the Columns node and drag the ‘LatestRecordedPopulation’ column and drop it in the bottom section. The column text will remain red until you define the condition. Please refer to the below image. 

conditional split transformation editor

As I mentioned at the beginning of the article, if ‘latestrecordedpopulation’ column is greater than the average of ‘Latestrecordedpopulation’ column which is 35829639, than it will be considered as High Population else it will be considered as low population. Based on that condition, we are going to add this logic into the Conditional Split transformation as shown below.

conditional split editor

Configure Excel Destination

Now that we have split the data flow into multiple path, we are going to add a destination for each path. In the beginning we configured the Excel destination and we are going to export the data to multiple Excel worksheets, so we should create two Excel destinations. Now we will drag and drop two Destination Assistants as shown below.

data flow

Now I am going to configure the data path. To do that drag the blue data path arrow from the Conditional Split transformation to the High Population Excel destination. When you connect the data path to the Excel destination, a dialog box will appear, which will let us choose the output we want to direct toward the selected destination. The dialog box will look like the below image.

ssis input outpu selection

The dialog box includes all outputs in the drop down list, which is available from the Conditional Split transformation. In this case the drop down list will include two options.

  1. HighPopulation
  2. LowPopulation

For the first Excel destination, right click the High Population Excel destination and click on edit. A dialog box will open as shown in the below image.

ssis excel destination editor

We have already configured the Excel connection manager, so the default Excel connection manager will be selected. The data access mode will be table or view. As I mentioned in the beginning of the article, the Excel file has two worksheets named High Population and Low Population. I will select ‘HighPopulation$’ for the Name of Excel sheet.

Now, I will map the table columns with the Excel worksheet column. To do that click on Mappings on the left side of the Excel Destination Editor as shown below.

ssis excel destination editor

In the above image, the Input column is the column name of the SQL table and the destination column will be the Excel sheet’s column. In this demo, the column name for the SQL table and Excel file are the same, so the Excel Destination Editor will automatically map the input and output columns.

We now need to do the same things for the ‘LowPopulation’ data path. Once finished, the SSIS package will look like the below image.

ssis steps

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 the execution will be successful as it is below.

ssis steps

In the above image, the package inserted 33 records into the High Population worksheet and 157 records into the Low Population worksheet.

Next Steps
  • In this article, we created an SSIS package with a single data flow. We added a Conditional Split transformation to the data flow to split the data into multiple paths and then we directed each of these data paths to a different Excel worksheet. Try it out for yourself.
  • Check out the SQL Server Business Intelligence tips.


Last Update:



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    Notify for updates 


Send me SQL tips:

    



Learn more about SQL Server tools