Dynamic Connection String for SSIS

The SQL Server Native Client provider is SQLNCLI* in an OLE DB Connection String.

·         2005 uses SQLNCLI.1

·         2008 uses SQLNCLI10

·         2012 uses SQLNCLI11

As of this posting, there is not a SQLNCLI12 included with the CTP2 release of SQL Server 2014.

These providers can generally talk to previous versions of SQL Server but version forwardness is less probable (with the exception of SQLNCLI11 communicating with 2014).

Root cause

You have an OLE DB Connection string using a provider that does not exist on that machine. Perhaps you built the package in a 2005 format but are running it on a 2008 instance. At any rate, your connection strings in the format of

Provider=SQLNCLI.1;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

For Oracle

Provider=MSDAORA;Data Source=;User ID=;Password=;Max Pool Size=50;Min Pool Size=10

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  
“Grow more trees to save the Earth”