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

Question

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.

Answer 1

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.