February 26, 2013 at 10:15 am
Hello guys,
I have a process that we are trying to automate and we were wondering if we could use SSIS to accomplish the feat...We started trying to use it and ended up getting confused on how it would work...I am going to go over our problem in a big picture view:
We have 3 different groups with each group have 5 process codes...Manually, we have to run a stored procedure (that takes group and process code as parameters) for each group for each process code...We take each result set and put it in an Excel file...That is the big picture view! The thing is currently, we have to run the process manually 15 times, but as business grows, more groups will be added! Therefore you can see why we need to automate this...Here is what we attempted but ended up getting lost:
1) We created a table that had each group with each process code (15 rows)
2) In SSIS, we created a Data Flow task, SELECT group, processcode FROM GroupProcessCode ---> Store them in a result set
3) Here is where we werent sure how to proceed, we then tried to use FOR EACH LOOP container to run the stored proc using the result set as the parameters (using each row in recordset(group, processcode))????
4) Then we need to take each result from the stored proc and store in an Excel file
I know I have explained this at a high level overview of what we need SSIS to do...Is this even possible? Are we on the right track? If so, where are we messing up? Like I said, we are not sure how to run the proc for each group for each process code then take the results from the proc and store it in Excel...If anyone has any tips/suggestions, it will be greatly appreciated! If you need any other details, just post, and I will respond!
Thank you!
February 26, 2013 at 10:56 am
Sounds perfectly do-able to me, though I have some questions:
1) Does the proc always output recordsets with the same meta data?
2) What do you want your output to look like? One Excel file or multiple? One tab or multiple?
3) Have you thought about coding your proc with more complex logic so that it returns the following resultset, for all combinations of group and process code?
GroupCode, ProcessCode, Res1, Res2, etc etc
This could then be fired directly into a spreadsheet. Whether that works depends on your response to (2).
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
February 26, 2013 at 11:05 am
Phil Parkin (2/26/2013)
Sounds perfectly do-able to me, though I have some questions:1) Does the proc always output recordsets with the same meta data?
2) What do you want your output to look like? One Excel file or multiple? One tab or multiple?
3) Have you thought about coding your proc with more complex logic so that it returns the following resultset, for all combinations of group and process code?
GroupCode, ProcessCode, Res1, Res2, etc etc
This could then be fired directly into a spreadsheet. Whether that works depends on your response to (2).
Im not sure if you are familiar with a lag grid, but that is what the final result looks like...Its an actuary term...These lag grids show paid claims for the previous 48 months for each group for each process code...The stored procedure we created, you pass in group and process code and will show what values are there...We then take that result to an Excel spreadsheet, and the programmer created an executable that will populate the entire spreadsheet with 0's where there is not a value...so the columns across are the year/month the claim was incurred, then going down we have the year/month the claim was paid...
1.) the proc returns the claim in which it was incurred and paid
2.) the final result will be one excel sheet that has each group each process code and the past 48 months of claims
3.) we have thought about it, but it works now with what we have so we were going to try to automate it with what we have
Im not sure if that helps or confuses more!
We are just not sure how to automate running the proc for each group for each process code in SSIS...
February 26, 2013 at 11:14 am
We are just not sure how to automate running the proc for each group for each process code...
There's more than one way that this might be done, but one which should definitely work is this:
1) Create a physical 'work' table which will hold the results that the proc outputs.
2) Modify your existing proc to append its results to this table.
3) Create a 'master' proc which does the following:
a) Truncate the work table.
b) Loop round all group-process combinations and call the child proc for each.
Your SSIS package now takes the work table as its input ...
Perhaps even better would be to modify your proc to do all of the work in one shot, but maybe that's too complex to code/handle - I'm in no position to assess that. But I did study to be an actuary years ago and know that nothing is simple in that field.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply