Configuring Connection Manager for 6.5 database

Greetings once again SSIS friends,

I am trying to configure a connection manager for a database that is still using SQL Server 6.5 But it is not working properly. I put the server name, user name and pass word, I select the Native OLE DB\Microsoft OLE DB Provider for SQL Server as my provider.

When I click Test Connection it says the connection is fine but then the drop down list for the list of databse names hows nothing!

Am I using the wrong provider?! I'm confused!

You have to use datareader source component as you need ODBC connection. At least that was the only way I could get it to work; as far as I remember.|||

Hi mate,

I have tried using the DataReader Source but I can't seem to be able to configure it correctly. When I try and refresh the component I get the following error :

Cannot acquire a managed connection from the run-time connection manager.

Please advise.

Thanks.

p.s.

I did add a new ODBC Data source for my SQL Server 6.5 database but I still don't know how to configure the component.

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

Is this how you are doing it?

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Yes.. I had already done that

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

You are right. That's what I had wrong. I was using the naitive ODBC data provider which apparently only works if I am using a SQL Query Task but not when creating a connection manager. I did just that and now my data source works just fine.

Thanks for your help mate. Much appreciated!