none
oledb destination access mode of OpenRowset Using FastLoad

    Question

  • Hi we run STD 2008.  Is it just a coincidence or is there some relationship between the distributed type of query we generally refer to as OpenROwset and the one choice in ssis oledb destination access mode (advanced editor) called "OpenRowset Using FastLoad".  

    db042188

    Thursday, October 17, 2013 6:18 PM

Answers

All replies

  • OpenRowset is temporary linked server in other words, SSIS uses it to make ad-hoc connections to get data.

    It happened to be baked into the OLDEDB type of a connectivity protocol.

    Forgot to add about the fast load: it is akin to the BULK INSERT, again a property of OLEDB and this term is SSIS specific.


    Arthur My Blog


    Thursday, October 17, 2013 6:38 PM
    Moderator
  • thx Arthur.  As far as we know OpenRowset (and Linked Servers)  are not allowed here due to the security risk and are prevented via some server setting(s) whose name I forget.  Are you saying that whatever prevents it otherwise is ignored by ssis?  We run ssis here with that access mode all the time.

    db042188

    Thursday, October 17, 2013 6:46 PM
  • An OLEDB connector would attempt to run OPENROWSET().

    They turned off perhaps as per http://support.microsoft.com/kb/327489 (option 1).

    If so SSIS may be not be able to connect via OLEDB.


    Arthur My Blog

    Thursday, October 17, 2013 7:29 PM
    Moderator
  • Thx Arthur.  We are a bit confused at your reply.  We tried that setting (server side, the one at the link) both on and off on two different servers and ssis has no problem using an oledb connector on either with the client side ssis property set to OpenRowset Using FastLoad.   So we still want to know if ssis (when using oledb in this way) is getting the best of both worlds, ie using open rowset but somehow bypassing the server restriction. 

    db042188


    • Edited by db042188 Friday, October 18, 2013 4:09 PM clarification
    Friday, October 18, 2013 3:20 PM
  • It appears to be bypassing, the easiest would be to use the SQL Profiler and reconfirm that the OLEDB connection indeed issues the OPENROWSET commands.

    Arthur My Blog

    Friday, October 18, 2013 5:28 PM
    Moderator
  • Thx for trying Arthur Z.   Let's see if other contributors know for sure.  Maybe we'll get to the profiler test first.  If so we'll post the results here. 

    db042188

    Monday, October 21, 2013 3:09 PM
  • Hi db042188,

    The OPENROWSET AccessMode of an OLE DB Destination doesn’t use the OPENROWSET statements.

    When the AccessMode of an OLE DB Destination is set to OPENROWSET, it actually does the insert by calling the sp_cursoropen, sp_cursor, sp_cursorclose procedures which are only internally used by SQL Native Client.

    When the AccessMode of an OLE DB Destination is set to OPENROWSET Using Fast Load, it actually uses BULK INSERT for the insert operations. By default, the FastLoadMaxInsertCommitSize is set to 0 which means everything is issued in a single commit. We can change the FastLoadMaxInsertCommitSize value when the number of rows being inserted are huge (say a million/millions). A value of 0 might cause the running package to stop responding if the OLE DB destination and another data flow component are updating the same source table. To prevent the package from stopping, set the Maximum insert commit size option to 2147483647.

    Here is a very good blog posted by Jamie about this topic:
    http://consultingblogs.emc.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx

    Here is another blog you can refer to:
    http://www.sqlbiinfo.com/2011/12/ssis-fast-load.html

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    • Marked as answer by db042188 Thursday, October 24, 2013 5:31 PM
    Thursday, October 24, 2013 7:58 AM
    Moderator