Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT' could not be opened....


"Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT' could not be opened. Operating system error code 5(Access is denied.).

Author
Message
Edward Welti
Edward Welti
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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?
James Connor
James Connor
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
mackie
mackie
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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
mail4malli
mail4malli
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
Avinash Barnwal
Avinash Barnwal
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 16
You are very right. Oledb Destination should be used for OLEDB connection.

Thanks
Avinash


Kindest Regards,

Avinash

avin_barnwal@hotmail.com



Lonely Rogue
Lonely Rogue
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 436
@ 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
Dinesh Asanka
Dinesh Asanka
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 223
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/
Lonely Rogue
Lonely Rogue
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 436
@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
BRIJWASI
BRIJWASI
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
tgarber
tgarber
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search