Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is this possible to accomplish thru SSIS? Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:01 PM
Points: 187, Visits: 423
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!
Post #1424168
Posted Tuesday, February 26, 2013 10:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 5,317, Visits: 12,353
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).



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1424187
Posted Tuesday, February 26, 2013 11:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:01 PM
Points: 187, Visits: 423
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...
Post #1424188
Posted Tuesday, February 26, 2013 11:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 5,317, Visits: 12,353
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1424191
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse