CODEWITHSUNDEEP
sql-serverssisoledbetlsql-server-2000
Sean
Sean

Reputation: 15144

Connecting to SQL2000 from SSIS

I can't get my SSIS package to connect to a SQL2000 database - I keep getting

SQL Server does not exist or access denied.

I've tried connection strings like the following:

<ConfiguredValue_1>Data Source=xxx;Initial Catalog=xxx;Provider=SQLNCLI10.1;User ID=xxx;Password=xxx;</ConfiguredValue_1>
<ConfiguredValue_2>Data Source=xxx;Initial Catalog=xxx;Provider=SQLOLEDB.1;Integrated Security=SSPI;</ConfiguredValue_2>
<ConfiguredValue>Data Source=xxx;Initial Catalog=xxx;Provider=SQLOLEDB.1;User ID=xxx;Password=xxx;</ConfiguredValue>

Actually, the SQLNCLI10.1 option gives:

The requested OLE DB provider SQLNCLI10.1 is not registered

For the Integrated Security option, I have set up a proxy & credential that the step is using, which is the same as my login.

However, I can connect to the database using Windows Authentication or a SQL username via an SSMS query window. What am I missing?

Upvotes: 2

Views: 1262

Answers (1)

Hadi
Hadi

Reputation: 37313

I will try to give some suggestions:

(1) Download SQL Server 2008 Native client

To download SQL Server Native Client 10 you should download and install the following package which is a part of the SQL Server 2008 feature pack:

Also try removing .1 from provider name: SQLNCLI10

(2) Use Microsoft OLE DB Driver for SQL Server

OLE DB Driver for SQL Server is a stand-alone data access application programming interface (API), used for OLE DB, that was introduced in SQL Server 2005 (9.x)

This provider can be used to connect to SQL Server 2000 instance.

Connection string example:

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

(3) Use ODBC connections

Another way to connect to SQL Server 2000 is using ODBC Driver for SQL Server

The Microsoft ODBC Drivers for SQL Server are stand-alone ODBC drivers which provide an application programming interface (API) implementing the standard ODBC interfaces to Microsoft SQL Server.

Connection string example:

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

(4) Using SQL Native Client 9.0 OLE DB Provider

Try using the version 9.0 of OLEDB provider which works only for SQL Server 7.0, 2000, 2005 version.

Connection string example:

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

References

Upvotes: 1

Related Questions