DTS Invalid Connection error.

Question

DTS Invalid Connection error.

chris_rutherford

SSChasing Mays

Points: 650
More actions
December 19, 2002 at 5:47 pm

#158401

Hello,
I have SQL Server 2000, we are using MDAC 2.70.9001.0. I've created a new package to get some data from a DB2 7 database. On my machine it works fine however when it's run on the server I get the following error...
Error Source : Microsoft OLE DB Provider for SQL Server.
Error Description : [DBNETLIB][ConnectionOpen (Invalid Instance()). Invalid Connection.
All I'm doing in the DTS job is getting data from a view in the DB2 DB. I've tested the ODBC connections and they are all successful. In the transform data task pipe I can even preview the data that will be copied across. I've also ran the Import Data wizard and had it import the data from the DB2 database into the table that the DTS job should have done.
Any assistance would be greatly appreciated.
Cheers,
Chris Rutherford

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

Are you sure the server has the same connectivity as your workstation. The DTS package will execute on the machine that launched it. So when you're previewing the data and running the Import Data wizard, you are actually connecting from your workstation.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

chris_rutherford SSChasing Mays Points: 650 More actions · Answer 2

Hello,

Thanks Phill for your reply. I've logged onto the server itself to verify that the server can see the DB2 database through ODBC, indeed it can.

Cheers,

Chris

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Have you tried to execut it on the server by hand? If works then is a permissions thing if not then something about the server is different in how it makes connections or settings.

chris_rutherford SSChasing Mays Points: 650 More actions · Answer 4

Hello,

Thanks for your reply. I've tried to execute the package from within EM, the command prompt and within a job but I get the same error. However it does run when I use the import data wizard and reference the same ODBC data source and view. Still can't figure this one.

Cheers,

Chris

Antares686 SSC Guru Points: 125444 More actions · Answer 5

When you created the package, was it on your local machine you did this. If so open the SQL connection and verify that it is valid for the box. You may have an alias setup in client network utility on you local box that does not have a match on the SQL server or is not pointed to the same place. Verify that that connection as defined is valid on the server.

chris_rutherford SSChasing Mays Points: 650 More actions · Answer 6

Hello,

I did create it on my local machine. I went in and created it on the server and it works perfectly now, don't know why I did not think of that earlier. Many thanks for your help, much appreciated.

Cheers,

Chris