Accessing DataReader data in Script Task

I have a Data Flow task that sends its results to a DataReader destination. My Data Flow is then connected to a Script Task. I'm trying to figure out how to access the data stored in that DataReader from my Script Task, but having no luck. Any help would be greatly appreciated.

OK, I've figured out how to do this, but using a Recordset, not a Datareader.

1. Pass whatever data you want to process in your Data Flow to a Recordset destination, and store it in an SSIS variable. In my case, the variable is called "ConsumerIDSet", and it's storing only one row called "ConsumerID".

2. In the Control Flow, add a Script Task. Connect the output of your Data Flow to the input of the Script Task.

3. Make sure the variable you're storing the Recordset into is availabe to the Script Task (add it to the ReadOnlyVariables list).

4. Open the VSA script designer, and make sure to add a reference to ADODB (Project...Add Reference...select ADODB).

Here's the script:

Public Sub Main()
Dim dbConnect As New OleDbConnection("[connect string]")
Dim dbCommand As New OleDbCommand
Dim consumerID As String
Dim rs As ADODB.Recordset

rs = CType(Dts.Variables("ConsumerIDSet").Value, ADODB.Recordset)

Try
dbConnect.Open()

Do While Not rs.EOF
consumerID = rs("ConsumerID").Value.ToString

dbCommand.CommandText = _
"UPDATE tblname SET extracted = 1 WHERE consumerid = " + consumerID
dbCommand.Connection = dbConnect
dbCommand.ExecuteNonQuery()

rs.MoveNext()
Loop

Dts.TaskResult = Dts.Results.Success

Catch
Dts.TaskResult = Dts.Results.Failure

Finally
dbConnect.Close()

End Try
End Sub

I'm not an experienced developer, so that might not be the best way to do it, but it works. BTW, I'm connecting to an Oracle database which is why I'm using OleDbConnection, so you'll have to adjust your connection type.

I'm still curious as to whether this can be done with a DataReader, so if you have any suggestions, let me know!

|||You might try cracking it with a ForEach loop.
http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx
K|||The DataReader Destination is designed to expose the result of the whole package as data source to some external (to the package) application, say Reporting Services, or your custom application. It is not really designed for reading data from inside of the package itself.

As you've found out the RecordSet Destination is designed exactly for your goal - for saving the data into record set that you can then use inside the same package. In addition, as Kirk wrote, we provide other ways to interact with data collected by RecordSet Destination, e.g. for each loop can iterate over it.

If you explain the bigger picture, we might be able to provide better suggestion on the design of the package.|||I was using the ForEach loop, and running the script task inside that. But that meant opening and closing a database connection on each iteration of the loop. It makes more sense to me to do the loop inside of the script itself.

Regarding the bigger picture...I've been asked to dig in to SSIS as a possible replacement for our current ETL tool, which is closed, proprietary, and expensive. We do quite a bit of interaction with Oracle, mostly involving extracting and formatting consumer data. My job is to learn SSIS quickly so we can figure out how best to apply it to our current processes. It's a bit early to say exactly what I'm trying to accomplish--I'm not sure yet! As I learn it better and figure out how we're going to use it I may have more specific scenarios.

Thank you for the help!|||Let us know how we can help.
K