How can I flatten a resultset in SSIS?

Question

Hello,

I have a requirement to "flatten" a resultset in an SSIS package - this is, take a result set consisting of a number of rows and produce a single string with the concatenated values.

The result set is returned to me via a stored procedure that for a number of reasons I cannot change. I do not want to duplicate the logic into another stored procedure, so the limitation is that I need to start off with the results of the stored procedure and work from there.

I am currently using an "Execute SQL Task" with an ADO.NET connection to run the stored procedure, and am returning the results into an SSIS variable of type Object.

My current thought (from googling, etc) is then to use an "Execute Script Task", using something like:

    Dim flattened As New StringBuilder()
    Dim adapter As New OleDbDataAdapter
    Dim dt As New DataTable
    Dim row As DataRow

    adapter.Fill(dt, Dts.Variables("DataSet").Value)

    For Each row In dt.Rows
        flattened.Append(row("Column").ToString)
        flattened.Append(DELIMETER)
    Next

    Return flattened.ToString

The above example currently returns me the error: "Object is not an ADODB.RecordSet or an ADODB.Record." I have tried quite a few different variations of the above to try and resolve this issue but have reached the point where my frustrations at debugging in the environment have hit the wall.

Any thoughts would be much appreciated! Thanks

Answer 1

Not sure if this is the best way, but something that I have found that works is:

Dim dt As DataTable = CType(Dts.Variables("DataSet").Value, DataSet).Tables(0)

And then just looping dt in my loop.

If anyone knows a better way please still answer and I will credit you the answer. If not then will leave this answer for future people with the same problem.

Thanks