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.
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: <http://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>
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.
Context SQL Server Integration Service ( SSIS ) has tasks to perform operations against Hadoop, for example: Hadoop File System Task Hadoop Hive Task Hadoop Pig Task In Data Flow Task, you can also use: Hadoop HDFS Source ...View detail
Infosphere DataStage is adopted as ETL (Extract, Transform, Load) tool in many Teradata based data warehousing projects. With the Teradata ODBC and .NET data providers, you can also use the BI tools from Microsoft, i.e. SSIS. In my previous post, I demonstrated how to install Teradata Tool...View detail
Serial: An Introduction to SQL Server Features Case Scenario For this ETL project, the requirements are listed below: Sales data will be pushed to specified shared fold...View detail