Post reply

Conversion of System.Object to collection

  • February 25, 2008 at 2:24 pm

    #154149

    Need assistance in looping an Object variable. Basically I have a SQL task populating the variable

    select field1 from table1 where field2 = 'N'

    This gets dumped into Variable1 of data type "Object" using the Result Set mapping.

    I need to then loop through this result set in the variable in a script task concatenating the results to a string variable (which is used in an email message body)

    I've tried the ctype(variable1, collections.arraylist) and a host of other conversions but to no avail.

    Any guidance, suggestions much appreciated.

    Cheers

  • February 25, 2008 at 3:01 pm

    #781795

    I found this link very helpful.

    http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

  • February 25, 2008 at 3:07 pm

    #781799

    Yeah - I already have it looping on the For..Next components elsewhere in the package, its just getting it to loop (or converting it to something that will allow a scripted loop) within a .NET script.

    If push comes to shove then I'll probably end up creating another external For...Next loop and putting my script in that. . .but it starts to look messy.

    Thanks for the response!

  • February 25, 2008 at 8:10 pm

    #781840

    Setting the ResultSet to XML in the Execute SQL Task is another option that would make it easy to use the results in a Script Task.

    Just add "FOR XML AUTO" to the end of the SELECT query. Then in the Script Task read through the XML to build an ArrayList or string.

    Script Task:

    ' Add Imports System.Xml and a reference to System.XML

    ' myXML and myCollection are package variables of type Object

    Dim myXMLDoc as XmlDocument = DirectCast(Dts.Variables("myXML").Value, XmlDocument)

    Dim nodes as XmlNodeList = myXMLDoc.SelectNodes("//table1[@field1]")

    Dim myList as New Collections.ArrayList

    For Each node as XmlNode in nodes

    myList.Add(node.Attributes.GetNamedItem("field1").Value.Tostring)

    Next

    Dts.Variables("myCollection").Value = myList

  • February 26, 2008 at 9:03 pm

    #782349

    Fantastic - thank you! 😀

    Its been a long few weeks!:crazy:

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

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