March 2, 2015 at 10:21 am
I've done some extensive work over the past year with SSIS for a client, developing a comprehensive series of packages to do ETL and perform other tasks on numerous Excel spreadsheets containing payroll data.
Client has mentioned another side project to query an underlying SQL Server database and export records for each unique plan (the query would pull info. on various payroll plans... Plan "A", Plan "B", etc.) for an entire year to individual Excel spreadsheets. This process would only be run once yearly. Currently, the export of yearly plan data is done manually through the user interface of a supporting third party piece of software and while it's not difficult, the export process has to be manually initiated and repeated for several hundred clients, so it's a large time involvement. They'd like to get this to where it's an automated process that can just be initiated once yearly and a few min. later, they have all of the exported data present in individual spreadsheets for each Plan (A, B, C, etc.).
Is SSIS possibly overkill for this type of functionality? I know an SSIS package can be written to perform this reading of records and parsing/output of individual Excel spreadsheets for each plan's data obtained from the SQL Server query, but I'm not wanting to overcomplicate how this can be achieved by going w/SSIS by default when the functionality could be done more simplistically elsewhere outside of SSIS.
Thanks for any input.
March 2, 2015 at 11:01 am
If the layout for each client is the same and it's a one-spreadsheet-per-client thing, it would seem that SSIS is quite a good fit.
The trick, obviously, is in the design of the Foreach loop which iterates around all the clients.
I cannot imagine it being much faster to develop in any other tool.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 2, 2015 at 11:02 am
brad.mccollum (3/2/2015)
I've done some extensive work over the past year with SSIS for a client, developing a comprehensive series of packages to do ETL and perform other tasks on numerous Excel spreadsheets containing payroll data.Client has mentioned another side project to query an underlying SQL Server database and export records for each unique plan (the query would pull info. on various payroll plans... Plan "A", Plan "B", etc.) for an entire year to individual Excel spreadsheets. This process would only be run once yearly. Currently, the export of yearly plan data is done manually through the user interface of a supporting third party piece of software and while it's not difficult, the export process has to be manually initiated and repeated for several hundred clients, so it's a large time involvement. They'd like to get this to where it's an automated process that can just be initiated once yearly and a few min. later, they have all of the exported data present in individual spreadsheets for each Plan (A, B, C, etc.).
Is SSIS possibly overkill for this type of functionality? I know an SSIS package can be written to perform this reading of records and parsing/output of individual Excel spreadsheets for each plan's data obtained from the SQL Server query, but I'm not wanting to overcomplicate how this can be achieved by going w/SSIS by default when the functionality could be done more simplistically elsewhere outside of SSIS.
Thanks for any input.
Hi Brad,
I don't think SSIS is overkill for this. While it may be possible to do this with seemingly "simpler" methods, the ability to create logical loops, deal with the file system and control the workflow of the process would definitely steer me towards SSIS.
Also think about the fact that you may want to send out user notifications...strengthening the case for SSIS in my opinion.
March 2, 2015 at 12:20 pm
Many thanks for both of your replies & opinions. I just didn't want to look past a simpler solution that I wasn't thinking of and overcomplicate things w/SSIS for this.
Based on your replies and other research I've done, I'll recommend to the client that we use SSIS for this upcoming effort. Should be fairly straightforward to develop from what I have in mind conceptually.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply