Brandie Tarvin (5/13/2013)
Thanks everyone for the responses. We're going to start checking them now. I'm glad I asked the question as this was not the direction I had been heading in with my thoughts.
Brandie, in SSIS are you familiar with using a Synchronous Tranformation Script Component? They are a godsend for issues like this where you're dependant on order for transformation. Also, plus side, they aren't stream interrupts.
Here's the short form: You fire up a script component midstream and add a column with GUIDs (easiest). Set the an internal variable to the first guid and then reset it on (1) lines. Carry through the old GUID on any other line.
Dump to database staging table.
Let me know if you need me to walk you through the code/setup. I'm hoping you're okay as unless you're familiar with them Script Transforms require a lot of screenshots. 😉
EDIT: Side note: If you want to get fancy, said script transform can also split the string into components based on values and send it to multiple streams. Might be overkill on the first pass, but that's how I usually set them up.
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