locked
Which connection should be used RRS feed

  • Question

  • I am new to SSIS. Which connection is recomended and better to use with SQL 2005 database, Ole Db or ADO.NET connection?

     

    Do correct me if I am wrong. I am thinking that ADO.NET will be better while using with SQL Server as database and if the database is different then SQL server then Ole DB connection should be used.

    Wednesday, May 23, 2007 5:50 PM

Answers

  • I think you're looking for a blanket answer, but it may help you decide depending on what you consider to be better.  For example:

     

    1. Inserting Data: SSIS provides an OLE DB destination that exposes SQL Server's fastload functionality.  There is no out-of-the-box component dedicated to inserting data using ADO.Net.

    2. Component Support: Some components only support one connection and not the other.  For example, the lookup component only supports OLE DB.

    3. Metadata Change: The OLE DB Source adapter has better metadata management logic than the DataReader Source adapter.

    4. Scripting: ADO.Net is preferred when used in scripts (the script task and transform only support VB.Net).

     

    You should also look at the SSIS Connectivity White Paper.  There's a link to it in the Announcements section of this forum.
    Wednesday, May 23, 2007 9:40 PM

All replies

  • I think you're looking for a blanket answer, but it may help you decide depending on what you consider to be better.  For example:

     

    1. Inserting Data: SSIS provides an OLE DB destination that exposes SQL Server's fastload functionality.  There is no out-of-the-box component dedicated to inserting data using ADO.Net.

    2. Component Support: Some components only support one connection and not the other.  For example, the lookup component only supports OLE DB.

    3. Metadata Change: The OLE DB Source adapter has better metadata management logic than the DataReader Source adapter.

    4. Scripting: ADO.Net is preferred when used in scripts (the script task and transform only support VB.Net).

     

    You should also look at the SSIS Connectivity White Paper.  There's a link to it in the Announcements section of this forum.
    Wednesday, May 23, 2007 9:40 PM
  • Thanks for the information.

     

    I'll go through the connectivity document too.

     

    Appreciate your help.

     

    Thanks and regards,

    Ashish Basran

    Wednesday, May 23, 2007 10:11 PM