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).
LVL 1
Alaska CowboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
One idea may be to simply get the SQL to do the running total when you pull the list of claims

SELECT a.claimid, a.claimvalue, (SELECT SUM(b.claimvalue)
                       FROM ClaimTable b
                       WHERE b.claimid <= a.claimid) RunningTotal
FROM   ClaimTable a
ORDER BY a.claimid;
0
Alaska CowboyAuthor Commented:
BCUNNEY, ok, that works to get the value, but I was looking for something a little more sophisticated (and efficient), can't I do something to get a running total ?

Also, for your suggestion, how do I take that value and put it into a variable ? (SSIS beginner here . . . )

thanks !
0
Barry CunneyCommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Alaska CowboyAuthor Commented:
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.
0
Barry CunneyCommented:
Hi William,
In that case set up an Execute SQL task with the original SQL I posted and then connect this to a For Each Loop container to assign recordset field values to SSIS variables.
Here is a very good article with an example of this approach
http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html

Please come back to me with specific questions if you hit any challenges
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alaska CowboyAuthor Commented:
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 . . .
0
Alaska CowboyAuthor Commented:
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.
0
Barry CunneyCommented:
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
0
Alaska CowboyAuthor Commented:
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 :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.