Hi All, I am trying to use linked server queries in my SSIS packages and getting the following errors: Description: 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: "Cannot open the table "ar_customer" from OLE DB provider "MSDASQL" for linked server "mas90_link". Unknown provider error.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "The OLE DB provider "MSDASQL" for linked server "mas90_link" reported an error. The provider did not give any information about the error.". All I am trying to do is select * from linkservername...ar_customer in OLEDB source and pushing the data to a new table in OLEDBDestination. need your inputs on this. Thanks for the help.

Why don't you just access the remote server directly? Well, the error is probably due to the fact the linked server is not accessible from where you run the package and/or the account executing it does not have the rights to connect to it. A simple test would confirm this: run this query using the same account from the same machine using the SQL Server Management Studio. Arthur My Blog

have you tried changing the format to: select * from OPENQUERY(linkservername, 'SELECT * FROM ar_customer')

Why don't you just access the remote server directly? Well, the error is probably due to the fact the linked server is not accessible from where you run the package and/or the account executing it does not have the rights to connect to it. A simple test would confirm this: run this query using the same account from the same machine using the SQL Server Management Studio. Arthur My Blog I want to do the inserts once every 15 minutes so I used it in a package and am trying to use it as part of sql job scheduled to run once at every 15 minutes. WHen I run the same from SSMS I am able to get the result I want. SO I am not sure what I am missing.

have you tried changing the format to: select * from OPENQUERY(linkservername, 'SELECT * FROM ar_customer') Yes I tried this but it still errors out.

Why don't you just access the remote server directly? Well, the error is probably due to the fact the linked server is not accessible from where you run the package and/or the account executing it does not have the rights to connect to it. A simple test would confirm this: run this query using the same account from the same machine using the SQL Server Management Studio. Arthur My Blog I want to do the inserts once every 15 minutes so I used it in a package and am trying to use it as part of sql job scheduled to run once at every 15 minutes. WHen I run the same from SSMS I am able to get the result I want. SO I am not sure what I am missing. So did it ever worked in the package? Arthur My Blog

Hi Sapen2, Please try to use the OPENQUERY in an Execute SQL task and then check if it works for you, please refer to the similar thread: http://social.msdn.microsoft.com/Forums/en-SG/sqlintegrationservices/thread/571895eb-b53d-40a0-9f23-3845c8d17c6e Please feel free to ask if you have any question. Thanks, EileenPlease remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.