Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

"Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT' could not be opened. Operating system error code 5(Access is denied.). Expand / Collapse
Author
Message
Posted Tuesday, May 20, 2008 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 2:03 AM
Points: 1, Visits: 22
I've created a set of SSIS packages which run fine in our test environment. The packages access a SQL server database on the local server. I can change the data source to use Windows or SQL Server authentication and both work.

We've transferred the packages to another server, and again are running them against a local sql server database. Using Windows authentication they work fine, but when I change them to use SQL Server authentication they fail with :-

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 5(Access is denied.). Make sure you are accessing a local server via Windows security.".

From the documentation I understand that the SQL Server Data Flow Destination needs permission to create Global Objects - but what needs that permission assigned? Or any other ideas how to cure the problem?
Post #503823
Posted Tuesday, June 10, 2008 2:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 10:14 AM
Points: 1, Visits: 16
If using SSIS and the package will be running on the sql server itself then you can use SQL Server destination but if the sql server is on a remote server, you'll have to use an Ole DB destination.
Post #514817
Posted Thursday, September 18, 2008 5:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 18, 2012 4:11 AM
Points: 27, Visits: 219
Hi There,

The issue I've picked up is that if you accidentally use a SQL Server Destination in your Data Flow Tasks and you have to change your Destination to an OLE DB one, you have to sit and re-map your columns from scratch - Which is an extremely huge pain if you have hundreds of columns to map.

What you can try to do is the following:

* Right Click on your SQL Server Destination and click on Properties
* Change your MaxInsertCommitSize from 0 to X amount of rows.

In my mind this sort of defeats the use of the SQL Server Destination because it now would commit rows to your Database every time it hits the X amount of rows you have specified, but at least you wouldn't have to go and re-map all of your columns from scratch.

I'm not sure what the Maximum amount of rows are that you can change this to - So have a look on Google and see if it helps.

Kind Regards,
Colin Macguire
Post #571654
Posted Tuesday, December 16, 2008 1:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 16, 2008 1:52 AM
Points: 1, Visits: 1
I resolved this issue ..
solution for this issue is ..instead of taking the sqlserver destination use oledb destination for oledb connection.

..regards
mallik
Post #620229
Posted Wednesday, September 16, 2009 5:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 1:47 AM
Points: 58, Visits: 15
You are very right. Oledb Destination should be used for OLEDB connection.

Thanks
Avinash



Kindest Regards,

Avinash

avin_barnwal@hotmail.com



Post #788851
Posted Monday, October 26, 2009 9:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 20, 2013 3:29 PM
Points: 167, Visits: 407
@ James Connor.

Thanks for the input. I too have solved the same issue by replacing " SQL Server Destination " with " OLE DB Destination ".

This behaviour has been explained well @ http://consultingblogs.emc.com/jamiethomson/archive/2006/08/14/4344.aspx


In 'Dead' Memories,
Vampire


--In 'thoughts'...
Lonely Rogue
Post #808782
Posted Tuesday, February 16, 2010 11:27 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:21 AM
Points: 912, Visits: 205
Users who execute packages that include the SQL Server destination require the "Create global objects" permission. You can grant this permission to users by using the Local Security Policy tool opened from the Administrative Tools menu. If you receive an error message when executing a package that uses the SQL Server destination, make sure that the account running the package has the "Create global objects" permission.






My Blog: http://dineshasanka.spaces.live.com/
Post #866769
Posted Thursday, February 25, 2010 2:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 20, 2013 3:29 PM
Points: 167, Visits: 407
@Dinesh Asanka

I have experimented your setting, and ran my package with "SQL Server Destination" created for a remote server.But my package failed again with the same error.

Here is what I did-

1). I have added my domain account in :
Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment\Create global objects

2). Executed the pkg that used the "SQL Server Destination" component in the "Data Flow Task". The Result :: Failed
Error Message:
[SQL Server Destination [1978]] Error: Unable to prepare the SSIS bulk insert for data insertion.
[DTS.Pipeline] Error: component "SQL Server Destination" (1978) failed the pre-execute phase and returned error code 0xC0202071.

3). Replaced "SQL Server Destination" with "OLE DB Destination" and Package succeded.

Is there any correction needed, in making my "SQL Server Destination" work as you have explained.

Barely 'Alive',
Vampire


--In 'thoughts'...
Lonely Rogue
Post #872545
Posted Friday, May 21, 2010 2:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 21, 2010 2:23 AM
Points: 1, Visits: 4
Yes Great Guys.. I was facing same probelm and the below solution have solve my probekl but i did not understand why it is happening, if i have a facility to choose the SQL Server Destination thay why i will go for OLE DB provider.
Post #925745
Posted Thursday, September 16, 2010 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 8:47 AM
Points: 1, Visits: 10
Has anyone resolved this problem OTHER than switching away from the SQL Server Destination? I've seen lots of posts about this and the "solution" seems to be to use OLE DB Destination which I understand is slower.


Post #987432
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse