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

  • 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

  • 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.

  • Nice Article...

  • 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?


  • 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!

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


    Thomas LeBlanc, MVP Data Platform Consultant

  • 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. 😛

  • 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.

  • 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

  • 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 dataset which you allude to later in the article ?

    Paul R Williams.

  • 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.



    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • 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.



  • 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

  • Hi Andy,

    Thanks for the information.. I m trying to with OLEDB and 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...



  • 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 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