Introduction
In our previous post we saw how to create excel dynamically in SSIS. Now let’s advance further and find out how to create Excel reports in SSIS using Powerful Template engine (introduced in PowerPack 2.7.4) In this post you will learn how to use ZappySys Export Excel Task to export data from Multiple SQL Tables to Single or Multiple Excel Sheets, we will use Formatting, Expressions and Filtering to export parent child datasets (Master-Detail) using Template approach which supports full customization as per your need.
We will be using following ZappySys SSIS PowerPack component to make things work in this tutorial:
You can check the following video to learn basic things about Export Excel Task. In future we will post video tutorial to show how Template engine works.
[youtube https://www.youtube.com/watch?v=PXuOWo0xMoc&w=560&h=315]Requirements
- First of all, MS Excel installed.
- Secondly, an Excel template that you can download here.
- In addition, you will need SQL Server installed.
- Aso, the SQL Server Northwind sample database that you can download here.
- Additionally, SSDT for Business Intelligence.
- Finally, use the SSIS Excel package example that you can download here.
Concept about Excel Template Engine
ZappySys Export Excel Template engine is compatible with FlexCel engine described here. So read it carefully to get most details about how template engine works and what elements supported in Template engine. Basically you have to use Template file which will be used to generate final excel report in SSIS. Advantage of Template engine approach compared to Simple export is you can control every aspect of your report. Also you can create nested reports use multiple datasets in a single sheet (Master-Detail JOINS) which is not possible in default export mode. The only down side of this approach is… learning curve. However once you understand concepts of Template Engine it wont take you too much time to master it.
In Template file you can define bands (e.g. Customer Info, Order Info…). Each band can grow in Vertical or Horizontal direction. Band must be created by Named Range in Excel. You must name your range in a very specific way. Based on how you suffix and prefix your named range it will decide how to grow your band (i.e. Expand Vertical or Horizontal).
Few things to remember with Template Engine.
- Each Template file must have at least one Band which can be used to attach Dataset.
- Each Template file must have Config Sheet somewhere.
- You can use many functions in your Template using Tag system
- You can define Charts, Graphs any elements you like in Excel Template and link to Ranges you define.
Getting started
In next few section we will see step by step instructions on how to use export SQL server data to Excel using Template File approach.
Prepare Sample Tables (Source data)
In this example, we will create an Excel report based in two SQL Server tables from the Northwind database mentioned in the requirements. We are going to use Northwind dataset. You can download SQL Script file from here. Just download / extract and run it in SSMS to create necessary Tables with Sample Data.
- First of all, we will see the data displayed in the Customer table:
Northwind table in SQL Server
- Secondly, we will check the data of the Orders table. Note that the CustomerID is included in this table, so we can create a master-detail report based on these 2 tables:
SQL Server Orders
Prepare Excel Template
Now let’s create a template file which will be used to share our exported file. To make demo simple we will use this already prepared template file. Lets understand how template file was created.
We assume you have read concepts of template engine by reading this link.
Basically few things to check in Template file
- Defining correct Config Tab (Only needed if you are using special features such as reusable expression, or filters (i.e. subset of data).
- Defining bands which is bound to dataset
Define Reusable Configuration Settings ( <#Config> tab )
In our example we are creating 2 extra tabs (UK, US) using filter expression (subset of dataset) so we need to use Config setting for that purpose. Notice that Config tab must be names as <#Config> and Reusable sections for Data, Format and Expression must be defined as below Cell A10 , Cell H10, Cell L10.
Define Config Settings for Excel Report
Create Bands
Now let’s look at how we define bands in template file. Read more about different types of bands in Excel Template Engine. Band are defined using Named ranges. In our example we use two bands in first Sheet1.
- For customer data __cust__ (start / end with double underscore)
- For orders data __ord__X (Read more on “X” ranges )
- For US tab __cust_us__
- For UK tab __cust_uk__
Designing Excel Report Template (Define Ranges for Master-Detail Report)
Using Filter (Subset of data) in Excel Report
Create SSIS Package
Now let’s use Our sample package to get started. You can create your own Package but we have already created one to make demo simple.
- In an SSIS project add the Demo SSIS package from the step 5 of the requirements:
SSIS add package
- In addition, in the SSIS project, drag and drop the ZS Export Excel task and Create a connection to the Northwind Database by clicking New for Source Connection.
- and enter the source tables/queries you like to use in your report. In our example, we will use two tables.
Syntax for Source data is alias1=Table1_Or_Query1 | alias2=Table2_Or_Query2 .... In this example, we will use cust= select * from dbo.Customers | ord=dbo.orders.
You can also use just the table names for both e.g. cust=dbo.Customers | ord=dbo.orders
Source table/query for Excel Report
- Check the option to Use Template Engine To get the access to Excel Report Template Tab.
Use Template Engine
- Now go to Excel Report Template tab, enter the path of the template file which we downloaded in step 2 of the requirements and write the relationship between the customer and order tables (cust.CustomerID=ord.CustomerID):
Specify template file for Excel report, define relationship for master-detail report
- Finally, in the Target tab specify the path of the Excel Target and run the package:
SSIS target in Excel
Checking the results of the package to create an Excel Report in SSIS using a template
- Once that you run the package, you can check the results. First of all, we will check the Sheet1. It will contain the orders grouped by customerID:
Excel report exported in SSIS based on a template
- Secondly, in the US sheet in Excel, it will show the customers filtered by the country where the country is US:
filter data by country in Excel
- Also, in the UK sheet, it will show all the customers where the country is equal to UK:
Show data where the country is the United Kingdom
Add Dynamic Comments
Sometimes you might want to add dynamic comments. Several cells may need comments, similar to tooltips, which come from another data column in the database.
To achieve this, add the comment to the cell in the template file and place a placeholder column under the specified column.
Place the placeholder column under the specified column.
This will allow you to achieve the desired output. The Excel file will be generated with comments in this format.
Excel File with Comments
Other Examples
There are many other examples of template engine use case found here. Refer to related Github link and download xlsx files from Github. You can use those template file path to run demo in SSIS.
Pivot Table Example
For example to run Pivot Table demo using Template Engine download below files
Found under below path
https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/VS2019/netframework/Modules/20.Reports/80.Pivot%20Tables
Conclusion
To conclude, we learned how to create a report from data in SQL Server and export based on a template. The template not only included a nice format in Excel, but also filter and relationships between two tables. If you liked our products, enjoy them HERE.
References
For more information about creating an Excel Report in SSIS using a template, refer to the following links:
