SSIS, how to populate a running total in a variable while pulling a list of medical claims
I have a simple package that pulls a list of claims and outputs to a flat file (.csv). but I also want a running total of the tot_paid_amt, and to store that in a variable. I use BIxPress which tracks and reports on variables.
so the flat file has the list of claims, but I want to internally track the sum of tot_paid_amt and store that in a variable which BIxPress then captures and reports.
BIxPress is an add-on to BIDS and it helps with various development tasks, monitoring jobs, etc. (like tracking and reporting on variables).
so the flat file has the list of claims, but I want to internally track the sum of tot_paid_amt and store that in a variable which BIxPress then captures and reports.
BIxPress is an add-on to BIDS and it helps with various development tasks, monitoring jobs, etc. (like tracking and reporting on variables).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi William,
I was thinking that it would be more efficient to get the database server to do the work of getting the running total rather than getting SSIS to do this work.
Is it definitely required that you get the running total values into an SSIS variable?
I was thinking that the SQL I posted would extract all the required data/output and then you could just send that directly to a Flat File Destination(.csv) and the objective is complete - you get all your claim data with the running total into a flat file.
Let me know a bit more of what the objectives are and I can help further - for example there are approaches in SSIS where an Execute SQL Task can output an ADO recordset to a variable of type Object and then you can have a For Each Loop container which iterates through this recordset and outputs field values to variables on each iteration.
I was thinking that it would be more efficient to get the database server to do the work of getting the running total rather than getting SSIS to do this work.
Is it definitely required that you get the running total values into an SSIS variable?
I was thinking that the SQL I posted would extract all the required data/output and then you could just send that directly to a Flat File Destination(.csv) and the objective is complete - you get all your claim data with the running total into a flat file.
Let me know a bit more of what the objectives are and I can help further - for example there are approaches in SSIS where an Execute SQL Task can output an ADO recordset to a variable of type Object and then you can have a For Each Loop container which iterates through this recordset and outputs field values to variables on each iteration.
ASKER
I'm actually fabricating a test, so that I can fill a variable which is then captured by the BIxPress code. So I just pulled a simple list of claims into a CSV and thought I could sum up the tot_paid_amt into a variable along the way.
Overall, we are charged with loading data from point A to point B and capturing metrics (record counts, dollar amts). BIxPress is like a code generator, so it captures the record counts nicely, but I'm trying to get dollar amount totals as well. BIxPress does capture variable values as well, so that's what I'm trying to accomplish.
thanks.
Overall, we are charged with loading data from point A to point B and capturing metrics (record counts, dollar amts). BIxPress is like a code generator, so it captures the record counts nicely, but I'm trying to get dollar amount totals as well. BIxPress does capture variable values as well, so that's what I'm trying to accomplish.
thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent, thanks ! I reviewed the link and do want to try that, but I need a quick and dirty solution (for a demo this afternoon).
So I took your original suggestion with the additional select (that returns the same total amount for each claim row). So my export .csv file looks fine. Now I just want to pluck this total column and plop it into a variable, and BIxPress will then report on the variable.
How do I do that ? I tried a derived column to put the summed amount into @[User::varTotAmt], but that didn't work . . . Copy column didn't work either . . .
So I took your original suggestion with the additional select (that returns the same total amount for each claim row). So my export .csv file looks fine. Now I just want to pluck this total column and plop it into a variable, and BIxPress will then report on the variable.
How do I do that ? I tried a derived column to put the summed amount into @[User::varTotAmt], but that didn't work . . . Copy column didn't work either . . .
ASKER
ok, I found this easy way to populate a variable, and it worked ! For a test / demo, I think I'm good. But will still review your link, that seems like what I'm really trying to do.
Hi William,
Good to hear you got something together - I think I missed your earlier emphasis on the demo side of things - I could have given some temporary quick solution for demo purposes, like maybe even just using a script task to populate the variable.
Note that the restriction with populating a variable directly from a Execute SQL Task is that it has to be Single Row, which is logical - you cannot assign a field value once for multiple rows into a variable
So this is why in a lot of scenarios you will want to iterate through the result set and on each iteration do variable assignment and then potentially feed those variables into something else.
When you get passed the demo stage and are taking this further, please come back with questions
Good to hear you got something together - I think I missed your earlier emphasis on the demo side of things - I could have given some temporary quick solution for demo purposes, like maybe even just using a script task to populate the variable.
Note that the restriction with populating a variable directly from a Execute SQL Task is that it has to be Single Row, which is logical - you cannot assign a field value once for multiple rows into a variable
So this is why in a lot of scenarios you will want to iterate through the result set and on each iteration do variable assignment and then potentially feed those variables into something else.
When you get passed the demo stage and are taking this further, please come back with questions
ASKER
Bcunney, no worries, thanks for the tips . . . I was thinking of a script task as well.
... makes sense on populating a variable from Sql Task has to be one row . . .
I'm sure I'll have a need to come back, I do want to iterate through the rows, but for now I'm good :-)
... makes sense on populating a variable from Sql Task has to be one row . . .
I'm sure I'll have a need to come back, I do want to iterate through the rows, but for now I'm good :-)
ASKER
Also, for your suggestion, how do I take that value and put it into a variable ? (SSIS beginner here . . . )
thanks !