How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server - SQL Server Central

How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server - SQL Server Central: "How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server"

By M. Deschene, 2008/01/09

Recently at a client, the need was identified to be able to send data from a database to multiple Excel workbooks dynamically depending on the number of different criteria identified by the client. In our case it was to be by large geographical area such as North America, South America etc.
Within the Excel workbooks there were to be individual spreadsheets/tabs for each country within the larger geographical areas. Within North America there would be a spreadsheet/tab for Canada, the United States and Mexico. Each of these spreadsheet would then be populated with data specific to that country from a database.
Since I needed to do this with little to no manual intervention, I decided to use some of the new tools in SQL Server. In this article, I will show you how to create a new Excel output file and populate the file with discrete spreadsheets containing specific data from a database. We will use Microsoft Visual Studio to develop an Integration Services that handles the processes involved from variable creation through various packages.
Before we begin, let me review the requirements for this process. We must be able to do the following:
1. Must be able to create the Excel file automatically including the file name (dependent on the identified naming convention).
2. Must be able to create each spreadsheet or tab, name it and have top line column headers.
3. Must be able to populate each spreadsheet or tab with discrete data for that spreadsheet or tab. This would be based on a select statement.
4. Must be re-runnable.