Error when running SQL Server 2005 SSIS package on SQL Server 2008

If you have upgraded from SQL Server 2005 64 bit to

SQL Server 2008 64 bit and you execute SSIS package that

you had in SQL Server 2005 you will get error something like this:

 Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.
The requested OLE DB provider SQLNCLI.1 is
not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000......................................................................................................

This is because the provider name for SQL Native Client (SNAC)
connections has changed in SQL Server 2008 and the SQL 2005
version of this provider is not installed with SQL 2008 by default.

Version Provider name:

SQL Server 2005 SQLNCLI.1

SQL Server 2008 SQLNCLI10.1

Meaning you have to change the connection string to use the
SQLNCLI10.1 instead of SQLNCLI.1

Here is the reference.

Tweet