Dynamic Excel WorkSheets based on data tables using SSIS

Question

Trying to create dynamic Excel WorkSheets based on data tables using SSIS.

Each Excel worksheet would represent a State and State work sheet would have different rows and columns.

For instance: AL - Alabama WorkSheet

A/C No Col1 Col2 Col3 ..... Col 39 (there are 39 columns)

1000 123 ABC 39j........49sd
2000 123 ABC 39j........49sd

FL - Florida WorkSheet

A/C No Col1 Col2 Col3 ..... Col 39

1000 123 ABC 39j........49sd
2000 123 ABC 39j........49sd

540K rows are involved so each worksheet would have around 54K records.

Let me know what's the best approach

You say there'd be different rows and columns, but both your AL and FL worksheets have 39 columns. Is that a coincidence, or do your output sheets always have 39 columns? — Ann L.
My bad, there will be 39 columns and 54 states. Each state would have it's own worksheet so total 54 worksheets. Yes output sheets will always have 39 columns. Is it possible to do something dynamic on this.. — user1810575
Is the source data for all 54 states in the same place? In other words, are the variable parts just what state to pull and what to call the output file? — Ann L.
one table has all the data needed and "State" is one of the column in that table so answer your question Yes & Yes. i can create 54 different control flows meaning one for each worksheet or for each state. Is this the best way to do it or anything better — user1810575

Ann L. Ann L. · Accepted Answer · 2014-08-24T18:21:31

This sounds like a parameterized SSIS package, to me. You can do this by creating variables to hold the parts of the data flow that will change (the state to search for, the name and path of the destination file) and setting those properties on the Excel and ADO.NET connection managers using expressions. At run time, you'd change the values of the variables and run the package.

Now, that's a very high-level summary. Going through it step by step would be too big a task for this setting, but here's an article that goes into how to do it in reverse (flat file to SQL). The basic concepts are the same, though, and, in particular, this article shows how to set expressions and variables in your package.

Dynamic Excel WorkSheets based on data tables using SSIS

2 Answers