Accessing Linked Server through Shared Server in SSIS

Question

I have a database connection manager set up to ServerX. On ServerX I have a linked server set up called ServerY.

I run this query on ServerX , it works

select * from 
[Serverx].[database1].[dbo].[Table1] A
left join [Servery].[database2].[dbo].[Table2] B
on A.[DNum]=B.[DNum]

I put it in a Execute SQL task in an SSIS package sitting on my PC not on ServerX. I run it and it gives this error:

Error at Task 10 h - Check for missing Deals [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "The OLE DB provider "SQLNCLI10" for linked server "Server_Y" does not contain the table ""database2"."dbo"."Table2"". The table either does not exist or the current user does not have permissions on that table.".

The query works on ServerX, but not on my PC using a remote database connection to the database on the Server or using an Execute SQL task in SSIS .

Am I missing something ? Help please ?

Pondlife Pondlife · Accepted Answer · 2012-07-25T19:20:32

As the error says:

The table either does not exist or the current user does not have permissions on that table

Because you can run the query on ServerX, you know that the linked server and the table on ServerY exist (assuming you typed the query correctly in your SSIS package), so the issue is most likely permissions.

It's very likely that the connection manager for ServerX in the package is incorrectly configured: it might have the wrong login credentials or it might even be pointing at the wrong server.

Accessing Linked Server through Shared Server in SSIS

1 Answers