Solved

Store Data Flow Totals in variables

Posted on 2010-09-02
8
295 Views
Last Modified: 2013-11-10
How can I store totals from my dataflow dataset into variables?

Currently, I collect my target dataset from SQL, manipulate it, apply conitional filtering, etc. Once the dataset is the way I want it in memory, I use Multicast to copy the dataset. On the copy, I use an Aggregate Tranform to get the count & a sum of a column. Now I need to load these 2 aggregate values in a variable.

If there is a better way to get these totals into variables other than splitting off the dataset using Multicast & an aggregate, let me know. One thing I can't do is handle all of this in the initial SQL script. The totals need to be based on the dataset in memory that was built in my Data Flow.
 
0
Comment
Question by:JeepGeekin
  • 5
  • 3
8 Comments
 

Author Comment

by:JeepGeekin
ID: 33588107
Also, I better mention that the dataset gets dumped in a text file at the end of this data flow. That's why I copied it to get my aggregrate totals.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33588873
you can use record set destination but..its not advisable

if you can specify the exact filtering conditions and agrregation level with your sql...We can suggest you a way to handle everything using Sql code.
0
 

Author Comment

by:JeepGeekin
ID: 33588945
I think the main thing I am doing that I cannot do in a SQL script is to merge data with a flat file. Then we do some cleanup after that before aggregating it.
0
 

Author Comment

by:JeepGeekin
ID: 33589046
What about using a Script component? I haven't used one yet, but I see a place for variable interaction.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 16

Expert Comment

by:vdr1620
ID: 33589177
Since you are actually trying to store the values of your dataset into variables ..so that they can be used in the next task in Control flow..Script Component wouldn't be of much help..

Where do you want to use these values stored in variable?
0
 

Author Comment

by:JeepGeekin
ID: 33589255
In a script task in the control flow. I need to make some decisions in this based on the totals.
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33589459
why do you want o store the values in the variable..i would suggest you to derive those decisions using script component instead of storing the values in variables and using them later on in script task

I would like to know what you are exactly trying to do .. to help you better
0
 

Author Comment

by:JeepGeekin
ID: 33591419
I got that to work. Good suggestion. Thanks vdr1620.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now