Bulk Load Failure

I have numerous jobs that use the Bulk Load object to transfer data. Once or twice a day, one of the jobs will fail with the folowing error:

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [73]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".

Error: 0xC0202071 at Data Flow Task, SQL Server Destination [73]: Unable to prepare the SSIS bulk insert for data insertion.

After receiving this error, any job that uses the bulk load object that attempts to run will fail with the same error. After restarting the SQL Server service, all jobs will run ok.

I can find virtually nothing on this particular error so if you have seen it before please let me know. This has become a maintenance nightmare!

I have the same thing happening. I can't find anything either. I hope someone can help us with an answer. It is a maintenance pain.

|||

I am working with Microsoft Support to try and determine the cause of the problem. When it has been resolved I will post it here. One thing you can do to slow down the occurance of the problem is to add the parameter -g512 or -g1024 to your Sql Server startup. Addint this parameter slowed the occurances from 1-2 a day to 1 every other day.

I hope this helps.

-Mark.

|||See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.|||

Kaarthik Sivashanmugam wrote:

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.

I've posted to a couple of the forum threads that discuss this topic. I am getting this exception, too. However, I am using OLEDB connections and they work sometimes and sometimes they don't. I can't figure it out. Here is a specific of what I am receiving. Maybe someone will see something in the error that I am overlooking.

My file is there. I can see the columns, data in the file source connection. So, it isn't that my file doesn't exist. But, where is this Global\DTSQLIMPORT file? Why would this run sometimes and then decide not to run only to run again some other time with no changes having been made?

Thanks in advance.

[SQLDest TE [45]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS 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.".

|||

Mark,

I tried adding the -g1024 parameter to my startup. My SQL Server 2005 is on a two-node cluster. After saving the change, I attempted to restart SQL Server. It would not restart. So, I tried removing the startup parameter that I added and then restarting again. It still won't restart. And, the parameter reappears after every attempt to restart the service.

I am hoping you or someone has an idea of how to get back to where I was. Good news is that this is not my production environment.

Thanks,

sk

|||I ended up having to remove the parameter from the registry. Now, I can restart SQL Server.|||

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: 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 SSIS 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.".

I can see the file data in the tasks at design time.

Any help is appreciated.

|||

Pam B wrote:

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: 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 SSIS 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.".

I can see the file data in the tasks at design time.

Any help is appreciated.

Are you scheduling the package? If so, check that the user account the SQL Server service uses has access to the file.|||

Sorry about the needless post (but maybe this will help someone else out there) I kept looking and found a post where someone said that if you are not running your SSIS package on the Server with the DB to use the OLE DB Destination instead of the SQL Server Destination. I changed this to an OLE DB Destination and it worked! Hope this helps someone.

Pam

|||I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.
|||

Ramvarun wrote:

I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.

Why not?|||

Hi,

I am using Data Flow task and the OLEDB destination. It worked when i truncate the existing content in table and load it again. But when i tried to load a table with data already present in it, then i get the error

[tTaskName] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS 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.".

Tried SQL Destination also, but no use. Is there any solution? I do not have access to restart the SQL server also :-( So i am not able to check.

Regards,

|||Pam thank you that worked. Changing the stock control from SQL Server Destination to OLE DB Destination.|||Thanks Kaarthik. Solved my problem immediately.