I am working on a project that involves generating multiple Excel sheets from a single source table of data. The source data needs to be split into separate Excel sheets based on change in a client number. Essentially each client in the source table, will need to have their own separate Excel file. I have an Excel template that needs to be used. The reason for the need for the template is that one of the columns needs to be highlighted in yellow when each of the separate Excel files are created. The format of each Excel file will be the same. The data however needs to be split based on the client number.
Additionally, as the separate Excel sheets are created they need to be named dynamically. They need to be named 'ClientNumber_ClientName_ReportTitle_YYYY.xls. The client's name is not in the source table. I need to find a way to join with the client's number in a control table or something.
Basically, the big picture is that I would like to create an SSIS package that runs a stored procedure that populates a single source table with the appropriate data for multiple clients, then splits the data into separate Excel files for each different client in the source table.
I am not sure whether I should make a Foreach loop or a Script task. I wouldn't think that a Conditional Split would work in this case. The approximate number of clients I need to create separate Excel sheets for is 150.