Server 'xyz" is not configured for Data Access error

[GreenFella]

I have run across a situation in DTS that is extremely frustrating. The package should be a simple one but there is an error which is holding me up.

I have an Excel file that I need to import into a SQL Server table. The Excel file is located on my C:\ drive. The database "123" is on server "abc". Those are my only two connections.

As part of the transformation I need to do a lookup on a seperate table within database "123". So, I have gone to the Lookups tab in the transformation task, given the lookup a name and selected the connection for the SQL Server table.

When I try to go build the query for this I am being given the error Server "xyz" is not configured for Data Access. Problem is, I haven't set up a connection for Server "xyz" anywhere, nor do I need to.

Why is DTS complaining about Server "xyz" when my only server connection is using Server "abc"?

Any help would be greatly appreciated.

TIA
Greenfell

 

[JayKusch]

SO when you clicked on the Lookups tab you were presented a box to enter the "Name" which would be the name of your Lookup query, "Connection" which should be set to the connection that is using your database 123 on server abc, Cache as default 0, and Query being the query you are developing.

Of interest is the "Connection" entry. Does it in deed point to the connection name you created in your DTS package that references the 123 DB on server abc?

Thanks

J. Kusch

 

[GreenFella]

Thanks for your quick response.

It does reference 123 DB on server abc.

There is nowhere (that I can see) that refrences server xyz in my DTS package.

 

[JayKusch]

Check the actual connection object in your DTS package that is "suppose" to be pointing to serverABC and make sure it is pointing to the correct server.


Maybe it is set to serverXYZ and not local, assuming you are running the package on serverABC.

Thanks

J. Kusch

 

[GreenFella]

I was referring to the acutal connection object. Which is set up as the destination connection for the transformation.

Is there another connection property in DTS that I am missing? How can you check to see if it is pointing to local or not?

 

[JayKusch]

Check your Transformation task and make sure its destination is pointing correctly to your destination connection.

Thanks

J. Kusch