SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Andy Leonard
Andy Leonard
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4893 Visits: 1122
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
russella0-96423
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 255
Thank you for posting the article. You've no idea the amount of tears and tantrums you've saved me Smile

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
Andy Leonard
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4893 Visits: 1122
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
Baswaraj Mulge
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 66
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
Andy Leonard
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4893 Visits: 1122
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
Baswaraj Mulge
Baswaraj Mulge
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 66
Thanks very much Andy.... Smile
mxwebb
mxwebb
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 102
The was great into.!!!!!!!!!

Need a little though. I have doing same thing except I need to add a where clause to SQL in the Excute SQL Task!

How does that work?
I tried:
Select AdjustSQLCode from _ut_PCA_Client_Import_Format_Cycle_Adjust
WHERE FormatID = @FormatID

Mapping Paramter:
Variable Name = User::FormatID Direction = input DataType = Long Parameter Name = 0
Simon E Doubt
Simon E Doubt
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2449 Visits: 895
Terrific article, Andy - many, many thanks.
This got me started with understanding how to shred SQL data using the Object variable type. This understanding will now help me implement all kinds of great functionality at work.
Thanks again.
- Simon
rana.narpal20
rana.narpal20
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 10
i didnt unserstand from where we have to configure the store procedure so that its performance can increase twice from earlier..plz im cinfused..if im calling the store procedure from C# file..how can i do this.i will be realy thankful to u
Old_D
Old_D
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 86
good article, can't wait to try it ThanksWink
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search