Here's a little problem that caused me a couple of hours of pain...
I've been working on an SSIS package that deals with some data in a Data Flow Task and transforms it to XML. Instead of using an in memory record set as the destination of the data flow and putting all the data into an XML string, I decided that I'd use a script task as the destination and write a few lines of VB (grr... =P) to write the results to an XML string and set a variable with the result. Sounds simple enough right? 3 hours later, I was still struggling to write the results to the variable until I stumbled on this little gem.
Given that the Me object has a Variables object which contains the variables that you have given the task access to, you'd think the way to set the variable would be:
Me.Variables.XmlResult = xmlString
Of course, this is wrong. Doing this gives you a lovely COM Exception stating (in a rather cryptic way) that you can't set the variable.
So, how should you do it...?
Dim variables As IDTSVariables90
Me.VariableDispenser.LockForWrite("XmlResult")Me.VariableDispenser.GetVariables(variables)
variables("XmlResult").Value = document.OuterXmlvariables.Unlock()
<sarcasm>How obvious is that...?</sarcasm>