Data Dump to Excel through SSIS and SSRS

2009-07-15T15:07:54-12:00:00

There are many ways to handle dumping data to Excel, and while there is no right or wrong way to push the data to Excel, there are some methods which will work out to the most benefit depending on the users' needs.

For the options below, I will use the example of a department store where the Excel file provides information to Department Managers on their product pricing across 52 weeks.

Option 1: Use a Reporting Services report.
When to use: When desired set of data frequently changes through parameters, or when data and charts are relatively unchanged. Few steps needed to get the data (couple stored procs or SQL commands). Small to medium sized set of data. Include drilldowns in the Excel file. Few destinations for subscriptions (unless using data driven subscriptions).

One great advantage with Reporting Services is the efficiency of generating the report and the flexibility offered in running the report. Maybe, for our example, the Electronics Department Manager wants to only view television pricing, the can be set up as a parameter to filter the report in RS, while with a SSIS package, the user may need to filter the full set of data in the Excel file after it is generated. The subscriptions can become unwieldy if there are a lot of destinations (such as 10 departments with 10 different emails), but can be better managed with data driven subscriptions (available in SQL Server Enterprise). Also, any data manipulation after the report is generated to an Excel file will take some extra time (such as setting up a pivot table or pivot chart to view the data in various ways).

Option 2: Use an Excel file through Integration Services
When to use: When format of data is of little importance. Many tasks needed to get data. Large set of data. Many subsets of the report and/or delivery destinations.

Maybe option 1 was not a good choice for our case. Could be that the data was in several places, the amount of data is more than we would prefer reporting services to generate, etc. The SSIS package can be set up to key off email addresses and department mappings in a table if we want to send only an excel file with that department’s product information. We create an Excel file for the data, we set it up as an Excel Destination in SSIS and add the tasks to our package, run the package, and the data in Excel is listed as test. Not very useful for the Department Managers, because now they will need to convert the numerical data out of the textual form, prior to being able to manipulate the data through pivots, etc.. Also, the header formatting in our Excel file was carried over across the data as well (we can fix the header formatting issue in the excel file from SSIS, and I will cover that more when discussing the steps for setting up SSIS to use Option 3). Because we don’t necessarily know what the user does not want in the report, we are likely to have a larger Excel file using this method than Option 1, where the user may filter down to products or weeks they are interested in prior to exporting to Excel.

Option 3: Use an Excel file through Integration Services with SQL Task Drop/Create Table
When to use: When formatting of data is important and/or data manipulation is desired (such as through a pivot table or pivot chart). Many tasks needed to get the data. Large set of data. Many subsets of the report and/or delivery destinations.

With this option, we are able to merge together aspects of Option 1 and Option 2 to remove some of the limitations that resulted from either method. Using this method, we can create a pivot table on one sheet and have the data refresh when open (a limitation with Option 2 because the data was non-numeric). We also leave the option open for the user to add/remove fields from the pivot, a limitation of a pivot in a RS report. As with Option 2, the Excel file size is likely to be larger than Option 1 and we can use supporting tables in the database to determine where individual reports should go. There is more time involved in setting up the report in this method compared to the other methods, be depending on the circumstances and users’ needs, the user may have a much better experience in obtaining the information they through this method.

Next update will cover the setup of Option 3 in SSIS, followed by an update later on the topic of Reporting Services.

Introduction

2009-07-13T11:41:10-12:00:00

My name is Guiseppe Scarfone. I am a Senior Report Developer with a focus on Reporting Services, Integration Services, and Analysis Services.

Blog posts will likely be focused mostly around Reporting Services and Integration Services and ways in which to accomplish tasks with the technologies or ways to solve issues that others may experience.

Feel free to share your experiences along with my posts. I will likely have a post up regarding SSIS and data exporting to Excel within the next couple days.