SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers

Question

SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers

Andy Leonard

SSChampion

Points: 10144
More actions
August 20, 2008 at 11:01 pm

#191698

Comments posted to this topic are about the item SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Viewing 15 posts - 1 through 15 (of 63 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Good article Andy. Wish I'd had it a month ago when I was first trying to figure out how to use Execute SQL Task with a result set. Took me probably an hour of searching and experimenting. This article makes it clear. The biggest issue I had was that I had no clue that ResultName was supposed to be the ordinal position of the result set returned by the SQL Statement. I started out by entering and actual name in here, and why not? It does say ResultName. Why not call it what it is, Result Set Ordinal? After some searching and pulling out some hair, I finally found this out. Your article would have been a big help.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Anipaul SSC-Insane Points: 24681 More actions · Answer 2

Anipaul

SSC-Insane

Points: 24681

August 21, 2008 at 4:13 am

#861107

Nice Article...

SATISD9X Ten Centuries Points: 1226 More actions · Answer 3

Very nice article.

Infact the one about ADO.Net ConnectionType is also great.

Will this still work throughout all other RDMBS. e.g. ORACLE, SYBASE etc?

-Satish

Don Staten SSC Eights! Points: 957 More actions · Answer 4

Thanks, Andy, for taking the time to put this article together. Sure was great to get a hands-on demo from you yesterday... then seeing the article today. How timely 😉

This one is definitely getting bookmarked!

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 5

Great article, thanks... you should do more on SSIS

Thomas

Thomas LeBlanc, MVP Data Platform Consultant

Russel Bell Old Hand Points: 305 More actions · Answer 6

Thanks for the excellent treatment of a very common task that is not intuitive in SSIS. It seems relatively straightforward once you point out the ability to retrieve the resultset into an object variable, the kind of enumerator to use, and how to parse it out into variables. 😛

todd_dawson Ten Centuries Points: 1240 More actions · Answer 7

I keep getting a compile error on the VB.NET script saying that a declaration was expected on the 'PublicSub','sMsg','MsgBox' and 'Dts'.

The 'End Sub' is telling me it must be preceded by a matching 'Sub'.

I know nothing about the syntax from VB.NET but all I did was copy and paste your code over the code that was already there. Replacing the exsisting 'Main' function.

Holding my mouse over the SQL execute task in BIDS tells me that the task is configured to pre-compile the script, but no binary code is found. Please visit the IDE....

You need to correct your code and place a space in your 'Main' function to equal:

Public Sub Main()

This will help save some confusion for some of us non-code junkies. 🙂

Otherwise, very nice article.

Andy Leonard SSChampion Points: 10144 More actions · Answer 8

Thanks for all the kind words! There will be more SSIS articles from me.

Todd, sorry you had trouble with the typo but I'm glad you found it and fixed it - and I really appreciate that you posted the fix!

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Paul Williams SSCommitted Points: 1930 More actions · Answer 9

Hi Andy,

In the article it states "The Full result set option returns an ADO.Net dataset object.....".

According to BOL :

"If the task uses a native connection manager, including the ADO, OLE DB, Excel, and ODBC connection managers, the returned object is an ADO Recordset.

If the task uses a managed connection manager, such as the ADO.NET connection manager, then the returned object is a System.Data.DataSet."

Thus, as an OLE DB connection is being used in the SQL Task, is a ADO Recordset object being returned rather than an ADO.net dataset which you allude to later in the article ?

Paul R Williams.

Andy Leonard SSChampion Points: 10144 More actions · Answer 10

Hi Paul,

You are correct and I was not - thanks for pointing that out. OLEDB providers return ADO Recordsets. To get an ADO.Net Dataset you have to use an ADO.Net provider in the connection manager.

Thanks!

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

russella0-96423 SSC-Addicted Points: 448 More actions · Answer 11

Thank you for posting the article. You've no idea the amount of tears and tantrums you've saved me 🙂

Once you get a good working example SSIS really falls into place - which makes me wonder why they're so rare.

Anyway this really helped me out and got my project moving again.

Cheers,

Andy

Andy Leonard SSChampion Points: 10144 More actions · Answer 12

Hi Andy,

Thank you for your kind words. I can't tell you how much they mean to me.

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Baswaraj Mulge SSC Journeyman Points: 85 More actions · Answer 13

Hi Andy,

Thanks for the information.. I m trying to with OLEDB and ADO.net Connection to execute Execute SQL task but OLEDB is taking 5.11min and ADO.NET is taking 5.28min.

Could you tell me the reason why...

Thanks in advance...

Regards,

Baswaraj.

Andy Leonard SSChampion Points: 10144 More actions · Answer 14

Hi Baswaraj,

The point I was trying to make in the article (and I may not have done a good job) is that I use ADO.Net to call stored procedures that return scalars and small datasets. I find ADO.Net is faster for those operations.

OLEDB is still my preferred provider for large data loads.

Hope this helps,

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics