Split Data Into Named Excel Files

Question

I'm working with a set of data from SQL Server that I'd like to get into a group of Excel files. This task needs to be automated to run on a monthly basis. The data looks like

Site    ID  FirstName LastName
------ ------- --------- ---------
North 111  Jim   Smith
North 112  Tim   Johnson
North 113  Sachin  Tedulkar
South 201  Horatio  Alger
South 205  Jimi  Hendrix
South 215  Bugs  Bunny

I'd like the results to look like

In Excel file named North.xls

ID  FirstName LastName
111  Jim   Smith
112  Tim   Johnson
113  Sachin  Tedulkar

In Excel file named South.xls

ID  FirstName LastName
201  Horatio  Alger
205  Jimi  Hendrix
215  Bugs  Bunny

There are between 70 and 100 values in the Site column that I'd like to split upon. I'm using SSIS to perform this task, but I'm getting stuck after I've pulled the data from SQL Server with a OLE DB Source task. What should come next? If there is an easier way to do this using other tools I'm open to that too.

Answer 1

I think SSIS is a good tool to use, and you have a couple of options.

Basically, you can use the multicast and conditional split objects to achieve what you want.

Here is how I would do it:

1) I would create the two MS Excel files with all of the data in them as I would like to see it. Delete the data and keep these as template files and make a copy for the full files.

2) Setup file connections to these files in the connection manager.

3) Make a file system task that overwrites the full files with the template at the start of the job (other ways to do this, but I like this one the best).

4) Add data flow task and in it drop in an OLE DB Source, a Multicast, two Conditional Splits, and two MS Excel Destinations.

5) Configure each of these and you should be done. The configuration is pretty intuitive and in the order I have above.

Answer 2

Not sure if this would work, but put all your condition queries in a table along with another field for a file name. Then use a for loop to go through each of those and dynamically modify the select clause of the transform task. SSIS calls these dynamic modifications - expressions.

You do a select on your table of statements/conditions and then throw that into an object variable. The object variable is then used in the for loop.

The only thing I'm not sure of is the mapping into the excel file name.

edit: Also found this, which uses a linked server http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/04/62781.aspx