Getting Data Flow "decision" info into OLEDB destination

  • I've attached a picture of the tip of my Data Flow iceberg. It's basically a SELECT source, a myriad conditional splits, and a destination. All destinations are the same table, the point is to simply decide what status to assign these objects based on the splits it took to get there.

    The statuses (statii?) are getting assigned correctly, but there's a need to get the information about how the decision was reached into the same table. I was hoping to use some System::variable at this point ([name] of the destination object? ... of the last split decision?) and just insert that into the table as well, but I don't see any way to do that.

    The only 2 ideas I've got so far are

    1) Seed the available "rule" values in the original SELECT and draw from them in the destination mapping.

    2) Derive the information manually just prior to each destination "leaf" node by inserting a derived column task just prior to the leaf with a hardcoded "rule" value.

    The first is going to slow our flow down significantly (methinks) since there are almost 100 (albeit integer) values that would need to flow through the entire DF, the latter is going to incur a garboon of work.

    Does anyone have any other ideas I should try? THANK YOU SSC!

  • #2 is my usual method, though I would have an alteration I'd recommend. With everything going to the same final destination include a 'union all' component and have it all dumped at once via a batch rather than multiple connections to the same db. It makes overall control easier in the end I've found.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Number 2 is my preferred path as well. Keep it simple..


  • Thanks for the nods fellas. I'm headed down that path right now.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply