SQL Server 2008 SSIS SQL Provider not found

Question

I am migrating an SSIS package from a 32bit server to 64bit server, both running SQL Server 2008. The problem is the SQL Server connection string - when I use SQLOLEDB as the provider, it says 'data source name not found and no default driver specified'. I've checked the ODBC panel on the 64bit server, I can see the SQL Native Client driver installed. I've tried executing the SSIS package as both 32bit and 64bit through SQL Agent, no success with either.

Does anyone have pointers on what to try/check next?

Edit - here is the connection string:

Data Source=##server##;User Id=##userid##;Password=##password##;Initial Catalog=##Database##;Provider=SQLOLEDB;

The SSIS package uses the connection string to make a connection to import data, was working until we moved it to 64 bit.

Huh, can't say I generally use SQLOLEDB provider but when I created a connection on this machine, 2008 R2, it used SQLOLEDB.1 Any chance that the provider value got mangled in the migration? — billinkc
Weird I cannot mark your comment as the answer. Tried in both IE and Chrome, not getting the 'Check' mark option. — James Dean

billinkc billinkc · Accepted Answer · 2015-05-05T18:30:37

Unsure of why the migration requires it, but creating a fresh connection manager on SQL Server 2008 R2 with an SQLOLEDB provider resulted in the ConnectionString specifying SQLOLEDB.1

SQL Server 2008 SSIS SQL Provider not found

1 Answers