SSIS Task to Create Excel Report/ Export Data to Excel with Header using SQL Statement, With Example

When we plan to automate a Process/System; sometimes we need to generate an Excel report to check the process status or create QC report using Excel to deliver or for internal use. Also somewhere we need formatted excel report for quick check the failure/warning status. For this, I have created an SSIS Custom Task to create a formatted excel report with/o header.

See Also: Create SSIS Custom Task with a complete example

I am sharing this Task (DLL) and will discuss the use. click below link to download the .dll files.

I have created two version of this Task depends on .NET version.

1. Download for .NET version 3.5 - ExcelReport_NET-V3.5.zip
2. Download for .NET version 4 - ExcelReport_NET-V4.zip (Updated UI)

Extract it and install. Please check my post to install SSIS Custom Task. 
Install supporting dlls also, comes with the ZIP.

After the installation, you will get the SSIS task in the Tool list.

After Install the Excel Report Task

Description of the Task with Example

Now, consider the below example to understand the use of this Task.

Let I have to create an Excel report of Employee List where I would mark all names as RED if his/her age is Above/Equal 40.

All employee information is available in a DB table named EMP_INFO.

Also, I have to put report information at the top of the Excel sheet, like Report Name, Report Creation Date etc.

So that, technically I will create an Excel Report which will contain the report information at the top of the sheet followed by the Employee information with the RED mark depends on the emp age (40).

Let's start to Configure the Task to generate

1. Double Click on the Task to Edit the Property using UI.

Excel Report Task UI

2. There are 5 sections available to give parameters value.

First one is the General Section, where you can change the Task name and description

The second one is DB info, Where I will put DB connection related info like Server Name, Database Name etc.

The third is Query, which is the SQL statement for the Report Header and Data/Body information.

The Forth is Excel Info, which is Excel related info, like Excel File name, Path to save the file, is the Excel file is New or Old, which sheet you want to modify if the Excel File already exists.

The Fifth and the Last one is Excel Formatting, by which we will Format the Excel Row/Column/Cell depends on some condition.

3. Now I am filling all the required field to generate the example employee report.

First, I will provide the DB info to connect the Database and run the SQL statement.

Second, I will provide two SQL. One for the Report information (Header) and the other for the Employee Information

Select "Employee List with Age Information" [Report Name], getutcdate() as [REPORT Creation Date]

Select ID as EMP_ID, Name, Age from [dbo].[EMP_INFO]

Third, I will put the Excel File Information

• OutputFilePath -> Excel File Path -> E:\TMP (in my case)
• OutputFileName -> Excel File Name -> Emp_Report.xls (in my case)
• IsFileNewOrOld -> New or Existing -> new (in my case)
• SheetName -> Output Sheet Name -> Emp List  (in my case)
• SheetNumber -> Position Of the Sheet -> 1  (in my case, I like to see as the first sheet)

Lastly, I will provide the Column and Row Formatting clause.

• ColumnFormatString -> Clause to Formate Full Column/Cell with a condition -> HN,Age,GE,40,HN,Name,FM,B; (in my case, see description section of UI for the menning of HN,GE,FM etc)
• RowFormatString -> HN,Age,GE,40,CL,250;10;10; (in my case)

After setting all the property, it will look like below image ...

After Set all Property

4. Now all is set to run the task. After running the Task, I have got a new Excel File in E:\TEM location as EMP_REPORT.xls. See below image (Excel Report with all Condition.)

Excel Report with all Condition

NOTE: For Property Description, please select the property and look at the description section of UI.

Property Description