locked
ADO.NET Connection Manager Questions RRS feed

  • Question

  • Is this connection recommended for connecting to SQL Server over OLE DB?

    Why can't ADO.NET connections participate in DTC transactions (SupportsDTCTransactions is false and grayed out)?

    Friday, August 5, 2011 12:50 PM

Answers

  • Regarding ADO.NET vs OLE DB on older SSIS:

    http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/1a9e3670-9685-4943-913b-123ecf248a9c/

    One advantage that OLE DB has over ADO.NET is that the Lookup component only works with OLE DB (if you are going to use that little bugger).
    But ADO.NET has less issues with 32 and 64-bit.

    Well, I didn't know ADO.NET had the bulk load option since R2 (and I use R2 *shame*), so I learned something today :)

    Since you're not on R2, I would definately go for OLE DB (avoid SQL Server Destination at all costs)

    ps: I monitor the SSIS forums on SSC :) (okay, I haven't visited them today)

     


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    • Marked as answer by Eileen Zhao Thursday, August 11, 2011 7:09 AM
    Friday, August 5, 2011 1:29 PM

All replies

  • Is this connection recommended for connecting to SQL Server over OLE DB?

    Why can't ADO.NET connections participate in DTC transactions (SupportsDTCTransactions is false and grayed out)?


    Regarding the first question:

    no, I would prefer using the OLE DB connection manager, used in conjunction with the OLE DB Destination.
    This destination adapter has the "fast load" option, which uses batches to load the data, resulting in drastic performance increase.

    ps: are you the Jack Corbett from SQLServerCentral? :)


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    Friday, August 5, 2011 12:58 PM
  • Koen,

    Thanks. 

    I just assumed that the newer ADO.NET connection would be preferred.  In R2 there is an option on the ADO.NET Destination to use Bulk Load which I assume (yup I know that that means) is similar to the "fast load" option on the OLE DB destination.  Unfortunately I'm not on R2 :-(

    Yes, I am that Jack Corbett.  I'm like the Highlander, there can be only one!  I'm posting over here for SSIS because more of the SSIS gurus like Todd McDermid and John Welch monitor these forums and not SSC.  Plus Steve doesn't have an Integration Services forum for SQL Server 2008.  I need to get on him about that.

    Friday, August 5, 2011 1:20 PM
  • Regarding ADO.NET vs OLE DB on older SSIS:

    http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/1a9e3670-9685-4943-913b-123ecf248a9c/

    One advantage that OLE DB has over ADO.NET is that the Lookup component only works with OLE DB (if you are going to use that little bugger).
    But ADO.NET has less issues with 32 and 64-bit.

    Well, I didn't know ADO.NET had the bulk load option since R2 (and I use R2 *shame*), so I learned something today :)

    Since you're not on R2, I would definately go for OLE DB (avoid SQL Server Destination at all costs)

    ps: I monitor the SSIS forums on SSC :) (okay, I haven't visited them today)

     


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    • Marked as answer by Eileen Zhao Thursday, August 11, 2011 7:09 AM
    Friday, August 5, 2011 1:29 PM