Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.
|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't allows to fire triggers (as I can see). I'm trying to use SSIS to communicate between two different applications and all logics in my destination database is based on triggers.
So it failes
WBR, Vadim