Execute SQL Task With Full Result Set Stores Data As What?

Howdy all,

I have an Execute SQL Task that may return a result set. If it returns a result set, I'd like to log a failure in my package with the results visible.

I have logging turned on and that's working great. I've read about assigning results to a user variable of type Object and that's great. I can shred my results, thanks Jamie, with a Foreach loop no problem. Within that loop, I've got some VB that manipulates the values and will call Dts.Events.FireError as appropriate. However, VB is frowned upon here so my boss has asked that I push the VB logic into a Control Flow item.

I've built custom components already so I've got some familiarity with the process. Where I'm stuck at is figuring out _what_ the actual object type is in my code. The Connection manager is Native OLE DBSQL Native client. My Execute SQL Task uses a connection type of OLE DB with a Full result set. Results are stored in a variable named ErrorResultSet. Within the Execute method, I currently have this code set up in an attempt to pick apart the object and discover the available methods.





Code Block
Variables _variableCollection = null;
if (variableDispenser.Contains("ErrorResultSet"))
{
variableDispenser.LockForRead("ErrorResultSet");
}
variableDispenser.GetVariables(ref _variableCollection);

// Iterate through the variables that we were
// able to lock. Assigning values to entities as
// available.
foreach (Variable _en in _variableCollection)
{
switch (_en.Name)
{
case "ErrorResultSet":
Object _rs = _en.Value;
System.Type _type;
_type = _rs.GetType();
System.Data.DataSet _realResults;
_realResults = _rs as System.Data.DataSet;
// My expectation is that the cast of _realResults would
// not fail.
break;
}
}
// unlock before we go
_variableCollection.Unlock();
return DTSExecResult.Success;







At this point, my assumption is that the unboxed type of the recordset is not in the System.Data.DataSet inheritance chain as the cast failed. Anyone have insight into what it is? I can't seem to get any hits on google for what it's using behind the scenes in the Foreach ADO Enumerator.


Beyond the immediate question, anyone have thoughts on how else I can solve the problem? I had thought perhaps the task could raise an event if it returned rows but it didn't seem to have that functionality. Even if that had worked, telling the logging provider to capture the result set into the log might have been too much for native functionality. Another option I was thinking about would be to continue using the Enumerator and my custom component is a pure rewrite of the current Script task with the obvious downside being that I'd lose the generic-ness I was hoping to get with being able to hit my dataset.