Many moons ago on 8th February 2005 I posted a blog entry explaining how one could navigate over a recordset that is stored in an Object variable, created there by a Recordset destination. That blog entry is here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx
As it transpired that blog entry became fairly redundant as new functionality appeared in the Foreach Loop container that allowed us to iterate over these recordsets using a Foreach enumerator (http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx).
Recently however people have started to question why, given the presence of a Recordset Destination, there isn't a Recordset Source. Well the main reasons are that:
- The Recordset Destination Adapter is not intended to be used for passing data between data-flows (that's what the raw file adapters are for)
- The structure of the recordset in the Object variable is not known at design-time so a Recordset Source Adapter would not be able to define its output metadata.
However, people are still asking for the implementation of a Recordset Source Adapter. A plausible reason for this may be that they don't like dropping data out to disk using a raw file - I can't think of any other possible reason.
So, given that the metadata of a Recordset stored in an Object variable is not known at design-time, there is no real need to build a custom adapter because we would still have to manually define the output metadata; therefore a scripted source component is more than adequate for our needs.
You may think that my method of doing this that I defined back in February 2005 would be adequate but frankly, it isn't! First of all it requires us to mess about with moving DLLs in order to use them and secondly the ADODB.dll doesn't exist anymore either, ADO functionality has been rolled into a different DLL. Thirdly it uses nasty, old and smelly ADO rather than shiny, new and snazzy ADO.Net. So all in all that's a very out of date blog post so we need a new method.
And here it is!!!
I'll assume that you know how a bit about building scripted source components so won't go into too much detail on that except to show the properties of our output:
Our Recordset destintion is stored in an Object variable called "Recordset" and was populated by a previous data-flow. The recordset contains two fields: str_col and int_col.
Here is the code that iterates over the recordset, adding values into the pipeline as it does so. We use an OLEDB Data Adapter that has the capability to convert an ADO recordset (which is a COM object) into an ADO.Net DataTable. Like so...
It really as simple as that - the conversion from our COM Recordset to a navigable ADO.Net DataTable is just one simple line of code.
I'd like to give a big thanks to Larry Pope for helping me with this. It was he who came up with the code that allows you to do this.
If you want to see this working for yourself you can download the demo package from here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060105Recordsets.zip
-Jamie
UPDATE, 19th March 2008: I've just been using this same bit of code myself and got the following error:
The fix is as simple as it suggests. Make sure you add a reference to System.Xml.
-Jamie
Update 2, 19th March 2008: I've just found out that If a NULL DT_STR value is loaded into a recordset it won't still be a NULL when you consume it in another dataflow. In other words, this method of using the recordset will silently change NULL values. So my recommendation is now...DON'T EVER USE IT!