CODEWITHSUNDEEP
sql-serverexcelreporting-servicesssisvba
Mchief
Mchief

Reputation: 137

Creation of excel files dynamically basing on parameter

The task is: basing on data returned by SQL query, produce automated periodical reports individually for each customer and save it as a separate excel file named as customer_name+[YY-MM].xlsx (or one timestamped excel file with separate worksheet for each customer).

CustomerID's will possibly vary each month, the target customer list is generated by SQL query.

What would be the best technology? Studied so far:
SSIS: Names of Excel files or tabs should be pre-defined in Control Flow and Data Flow tasks and cannot be set up dynamically. Output is non-formatted csv (bad, but can cope with it).
Excel: I can embed the bulk data for all customers into excel, but not sure if it is possible to write a macro that will fetch unique customers, create corresponding tabs and put corresponding data in each one.
SSRS: Subscription function is disabled on corporate level - cannot use it :( Even though, not sure it accepts dynamic parameters like this.

Did I miss any option? Or maybe there is other technology available?

Upvotes: 1

Views: 131

Answers (1)

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

SSIS is going to be your best option here for the following reasons:

  1. You can set up a set of excel templates if you have a finite number of patterns, then use SSIS to populate and rename the template pretty easily.
  2. If you need finer control, you can use Interop and C# to do pretty much whatever you want in Excel, however this is very slow to execute and is time consuming to build.
  3. It's easy to set up an execution loop over a dataset (your list of customers) and include which templates or tabs they need as part of the data set, which makes maintenance and adding new customers simpler and possibly something that can be moved to the business side.

Of course SSIS has a few drawbacks as well, such as the difficulty in getting the excel column formats to cooperate when using the excel connector, but it is still probably the best option.

Upvotes: 2

Related Questions