Right, so. I've got a problem. I need to have a 'tag count' included in the footer of an XML I'm shipping to a system. The problem comes in where there's sublevels in tags that throw off @@ROWCOUNT and running it twice (once for XML and once for count) could give me different results, the system has enough volume and the XML builder travels for 4+ seconds.
So, even creating an @@ROWCOUNT solution, SSIS as an OLEDB source doesn't let you do output parameters. You can ship it to an Execute SQL command at the Control Flow level, stuff the recordset into an object variable, and then grab that in a script source in the dataflow, but that is not only overkill, it's moronic... but if it's the only way it's the only way. The problem is that any rowcounts I get will include the sublevels.
Errr, I suppose I should explain that.
<Contact name="b" />
I need to get a count of <Contact without including the count of <Address, but if you @@ROWCOUNT after your FOR XML you get the full count. I need 3, the above would give me 6.
Now, usually what I'd do is simply multi-cast in SSIS at this point and strip the little bugger in a script task doing locational checks. Problem... VARCHAR(MAX) (or DT_Text) doesn't allow itself to be shoved into a string type in VB.NET... which I need because these things can get huge.
At this point I'm interested in knowing if anyone has even an idea of alternat ways to go. I'm tacking the footer on in another dataflow which is dependent on the XML inclusion, so Pre-processing of some form is an acceptable idea. I may just not know the right VB.Net variable type to use, so if that's the problem please, send me in the right direction. If it's obvious, my google-fu has failed due to keywords. For example, I can easily find things like this: http://blog.boxedbits.com/archives/6, but I can't find a workaround.
- 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]