August 9, 2012 at 1:45 pm
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.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
August 10, 2012 at 9:37 am
Does the ar_cusomter table have an XML data column? I found that moving data with an XML column between servers is not allowed when using linked servers. I don't think it would work with an OPENQUERY either.
August 10, 2012 at 3:57 pm
Why wouldn't you just create connections to each server in the SSIS package and then create a dataflow to move data between them? Why the linked servers?
August 10, 2012 at 4:58 pm
Are you able to manually do the select between servers with the fully qualified path?
But I agree with the above poster, why mess with linked servers, just create your source and destination connections.
HTH,
Rob
August 11, 2012 at 12:18 am
Thanks for the replies guys. I wouldnt mess with linked servers if database was a SQL Server database. The problem is sql queries wont work directly on MAS90 (providex) database. Hence I am using linked servers to get data from this database. Since I need to continuously fetch data (once every 15mins) I am using it in a package.
I am still wondering if linked server queries are supported in SSIS packages.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
August 11, 2012 at 9:13 pm
Yes; linked servers are supported in SSIS. Can you manually get the SELECT to work between the servers in SSMS? If so, then we can proceed from there. Is the SSIS running under different permissions than your manual SELECT?
HTH,
Rob
August 11, 2012 at 9:20 pm
robert.gerald.taylor (8/11/2012)
Yes; linked servers are supported in SSIS. Can you manually get the SELECT to work between the servers in SSMS? If so, then we can proceed from there. Is the SSIS running under different permissions than your manual SELECT?HTH,
Rob
Yes I am able to run the query from SSMS and get the data. Infact I can do an insert from SSMS too. If linked servers are supported in SSIS then I think it should be a permissions issue.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply