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.
4 Answers
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.
After searching for this topic, i found a post on MSDN website where a comparison is made based on 4 perspectives:
- Support across SSIS components
- Performance
- 64-bit considerations
- 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:
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.
Here is another review and one new reason is listed:
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
Related