Welcome to EMC Consulting Blogs

We have located a misleading statement on this subject in Books Online and correcting it promptly. The column values of a Single row resultset are all returned as strings. An XML resultset can be returned as a string or as an object. A Full resultset is return as an object. Please encourage your readers regularly to use the "Send Feedback" link available on every page of BOL! Your comments quickly reach the right person and contribute to better documentation. Thanks, -Doug

This is exactly what I was looking for. I just couldn't figure out how to link a recordset from my query into a ForEach loop. Having the package to reference helped immensely. I was trying to go through the private newsgroups and even searched @ SQLIS.com without any luck on using a recordset as opposed to looping through files.

Thanks for the article.

Very useful blog. Thank you.

Jamie - Good article, but do you know how when you run a SProc in Mgmt Studio, you get a ResultSet + a "Message"?
How do you capture this "Message" text into an SSIS variable (either Object or String type)?
Doesn't matter whether it's via ODBC or OLE DB or Native SQL connection, as long as I can get the text out somehow.
THNX

>>We have located a misleading statement ... The column values of a Single row resultset are all returned as strings <<

Ah-ha! it's still in BOL no wonder I was getting cast errors with numerics.
Thank you for the sanity check.

i'm having trouble getting it running.

the error is from the foreach-container.

it cannot convert somehow the index-0 (which is an identity) to a string, int32,int64.

it just tells me the types are different.

This is great, but it doesn't work for an MDX resultset.  

Even thought my resultset has only one row, it requires an object variable to output to.  But then the looping container tries to match the forst row, which I can only assume is a header row.  

Is there any way to 'skip' headers?

Good stuff!!!

Awesome.  This was very helpful.

Excellent.  I'm just getting into SSIS development, and your blog has repeatedly saved me hours.  The BOL is sorely lacking in "How-To" with regards to tasks like this, and you do a great job filling in the blanks!

Can we output the resultset to a file (instead of having a message box).

I was successful in doing it but had to open the file, write, close. Lot more IO.

Any simpler way or elegant way of doing it.

Can we output the resultset to a file (instead of having a message box).

I was successful in doing it but had to open the file, write, close. Lot more IO.

Any simpler way or elegant way of doing it.

Yes, you can output the query results using a data-flow.  I have a follow-up question, is there a quick way to append those results to an existing file?  I'm trying to send query results in the body of an email and would like a header in the message body before the results are listed.

Hi Jamie,

I tried to follow the above example exactly but still I am getting this error

Error: Variable "User::ADOVar" does not contain a valid data object

I have found this code ujseful and many thanks!!..Is there a way to select folders (not files)  with the foreach loop . I need to select and delete folders that are older than a specified date. I know we can delete with the file task. How do I select the folder checking the date and pass it to the file task???

Would you happen to have any ideas?

Thanks again!!

If you are building asynchronous custom components for SSIS then at some point you will need to use the various Set

thanks

Hi, is there a way to then chuck the results of an SQL Task into an Email (Send Email Task) and send it?

Thanks for the useful post!

I&#39;ve just been made aware that as a result of a recent migration of our blog infrastructure some

i have tried this one but getting the error as mentiond by User::rsDetails does not contain a valid data object.Could anyone guide me for this?

Same error as above (Valid data obj)

ny solution experts ??????

For the not valid data object error, make sure you have set correct variable in the  foreach loop container settings in Collection tab at "ADO object source variable". And that it's not empty at run time. Such mistake caused this kind of error in my case.

Thanksss

Hello Jamie,

         I want to move data from 5 tables in database1 to corresponding 5 tables in database2 and in a single package.How to do that?

Real nice post.

Greatly apreciated

Real nice post.

Greatly apreciated

Real nice post.

Greatly apreciated

Is this the only way to get a value from a resulotset?

I dont want to make a for each loop because I only retrive a single row.

Is it possible to get a value from the resultset without having to use the for each?

Maybe something like User::FullResultSet::0 ???

How is that push done?

Hi,

I am using ADO enumerator for the first time in Foreach loop. It’s very similar to example shown above. I am using Execute SQL Task with OLEDB connection (connection to SQL Server 2000) to process the shredded recordset. I have properly mapped the Parameters in Execute SQL task to the Foreach variables. Now when I use the parameter in the sql command, something like,

UPDATE Person

SET Email = NULL

WHERE PersonID = ?

Throws following error:

<Query text> failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The interesting thing is if I change the Execute SQL Task connection to ADO.Net connection and change the query syntax to .net compatible, everything runs fine. I am not sure what’s going on with OLE DB connection.

The way I understand is this: The full recordset returned by the query before the foreach loop stores the recordset in Ado.net by default and then when I try to use it in Execute SQL task via parameters there is some datatype mismatch.

Am I right in thinking this way? Just curious about it as i spent lot of time fixing this problem.

Thanks,

Mukti

Good stuff!!!

Good stuff!!!

thanks blogs.conchango.........

thanks

thanksss

Jamie,

Instead of looping through one by one and waiting for each to finish before iterating and setting the next connection, i want to set the connection and execute my Execute Sql task one by one without having to wait for each Execute sql task to finish.  I guess i'm trying to execute my Execute SQL task Asynchronous within my Foreach Loop.  Is this something that can be done within ssis?  If so, can you head me in the right direction?

thanks.

Hey I have down this Example.

My issue is I need to replace a field in the SQL with a value from a Variable I have already set in the package.

Here is what I have.

I have Execute SQL Task that runs pulls back 6 records each having and update statement. I need to execute each update statement but before I do I need to replace the @CycleName in the SQL update statement ("Update @CycleName ").

Having issue for one I think its an object.

hope that made since

please email me reply mxwebb@msn.com

thank youu