How to split table data into separate named Excel files using an SSIS package?

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.

Solution 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.

Solution 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

Solution 3

I found the solution the problem was with the excel destination.I reinstalled Microsoft access engine Redistributable(32-bit version). All is working good. I had a 64bit and 32 bit problem they where not compatibility as my system was 64 bit.