CODEWITHSUNDEEP
sql-serverssisetl
Apinya Chabaram
Apinya Chabaram

Reputation: 105

What should I choose between ADO.Net and OLE in SSIS?

Anyways I try to use this kind of two source destination in SSIS , but I can not find the difference of them about configuration. Then Can anyone share me know, what should I choose? and which one better for using in each situation or data.

Upvotes: 7

Views: 12064

Answers (4)

Mike
Mike

Reputation: 1667

Here is another review and one new reason is listed:

https://www.mssqltips.com/sqlservertip/3053/sql-server-integration-services-connection-manager-tips-and-tricks/

An extra advantage of the ADO.NET connection is when using parameters in an Execute SQL task. In OLEDB the parameters all appear in SQL as '?' But in ADO.NET You can reference each of them by name eg @ParameterName

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19184

Only OLE DB connections can be used as a source for SSIS Lookups. You cannot choose connections of type ADO.Net for lookup purposes.

Only ADO.Net supports newer SQL Azure authorisation methods such as Active Directory - Password. OLE DB is stuck with SQL authentication only

My advice is that if you plan to migrate to or use SQL Azure, don't use OLE DB

I also advise against using lookups if possible anyway

ADO.Net is certainly "newer" than OLE DB and is more in alignment with C#.... I have no citations, that's just my understanding.

.. and just to consolidate answers.

@Ferdipux makes a great point in his answer below:

ADO.NET connection managers can be used in C# code of Script Task/Transform without any additional actions. Just get it and call AquireConnection method.

Upvotes: 5

Yahfoufi
Yahfoufi

Reputation: 2544

After searching for this topic, i found a post on MSDN website where a comparison is made based on 4 perspectives:

  1. Support across SSIS components
  2. Performance
  3. 64-bit considerations
  4. Target data source & data type supportability

You can refer to this link for more information: OLE DB VS ADO.NET

Also there are some useful links that you can refer to:

  1. SSIS Data Flows – ADO.NET vs. OLE DB vs. ODBC
  2. Difference between ADO NET source and OLE DB Source in SSIS 2008?
  3. Performance Testing OLE DB vs ADO.NET in SSIS

Upvotes: 2

Ferdipux
Ferdipux

Reputation: 5246

Adding to Nick answer, ADO.NET connection managers can be used in C# code of Script Task/Transform without any additional actions. Just get it and call AquireConnection method.
OLEDB connection managers have to be converted to ADO.NET somehow; I do it with decoding its connection string.
If you are using plain SSIS and do not consider migrating to Azure, OLEDB connections are faster for date retrieval.

Upvotes: 0

Related Questions