Generate Formatted Excel Destination (Output) in SSIS Data Flow Task

access_time 7 years ago visibility13099 comment 0

SSIS (SQL Server Integration Service) provides a number of convenient tasks to enable data integration. Exporting data from database to Excel file is a common task in ETL (Extract, Transform, Load) projects. Constantly the users/customers may raise format request regarding the Excel extract. To generate formatted Excel through SSIS is not straightforward but still feasible. I will summarize several ways to implement this in this article.

Approach 1: VSTO/Microsoft Office COM APIs

The first approach is to invoke COM APIs in a script task. This will be done by using programing mode to create unmanaged Office Excel objects.

Check out my previous post about this in the following page: <https://kontext.tech/Blog/DotNetEssential/Archive/2010/3/14/93ece962fe2f1bd6e7113a31.html> (in Chinese).

MSDN also provides many examples about how to access Office Interop Objects: <http://msdn.microsoft.com/en-AU/library/dd264733.aspx>

Approach 2: SSIS Data Flow Task by Using Excel Template

A simple direct way is to create one formatted Excel template and use it as the Oledb connection destination and directly insert data into it. Modify the properties of the Excel Destination Component and set Access Mode to "Open RowSet", then on the Open RowSet Property specify the Excel Tab and the Starting and ending cell.  For instance 'Sheet1$A3:C', this would insert data into the worksheet from row 3 which give you flexibility to add multiple header rows as you wish.

This can also be used if you want to skip rows when selecting data from Excel worksheets. Find the example at <http://www.bidn.com/forums/microsoft-business-intelligence/integration-services/1541/export-data-from-sql-server-to-excel-skip-first-4-rows-in-excel-sheet>

Inspired by this approach, you can also use VBA or VSTO to dynamically change the format of rows/columns at runtime.

info Last modified by Administrator at 26 days ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer mssql local_offer t-sql

visibility 17
thumb_up 0
access_time 11 months ago

In Oracle database, you can select from dual table if you only want to return a one row result set. In many other databases, the query engine supports select directly from constant values without specifying a table name. 

local_offer teradata local_offer t-sql local_offer mssql local_offer SQL local_offer SQL Server local_offer teradata-sql-query

visibility 4251
thumb_up 0
access_time 6 years ago

For Teradata developers, if you have no SQL Server installed, please go to the following link to download the SQL Server 2014 Expression Edition. http://www.microsoft.com/en-us/server-cloud/Products/sql-server-editions/sql-server-express.aspx It is easy to get started and free to use.

local_offer mssql local_offer teradata local_offer teradata-sql-query

visibility 3812
thumb_up 0
access_time 6 years ago

In this serial, I will compare Teradata SQL with T-SQL with samples provided. This is mainly prepared for SQL Server DBAs, Developers and other users to help them quickly master the common used SQLs in Teradata platform. Similar to T-SQL, Teradata SQL implements most part of the ANSI SQL with ...

About column