May 20, 2008 at 9:37 am
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?
June 10, 2008 at 2:15 pm
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.
September 18, 2008 at 5:29 am
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
December 16, 2008 at 1:54 am
I resolved this issue ..
solution for this issue is ..instead of taking the sqlserver destination use oledb destination for oledb connection.
..regards
mallik
September 16, 2009 at 5:53 am
You are very right. Oledb Destination should be used for OLEDB connection.
Thanks
Avinash
October 26, 2009 at 9:21 am
@ 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"> http://consultingblogs.emc.com/jamiethomson/archive/2006/08/14/4344.aspx
In 'Dead' Memories,
[font="Comic Sans MS"]Vampire[/font]
--In 'thoughts'...
Lonely Rogue
February 16, 2010 at 11:27 pm
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:
February 25, 2010 at 2:31 am
@dinesh-2 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',
[font="Comic Sans MS"]Vampire[/font]
--In 'thoughts'...
Lonely Rogue
May 21, 2010 at 2:23 am
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.
September 16, 2010 at 10:33 am
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.
September 16, 2010 at 1:33 pm
tgarber (9/16/2010)
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.
The OLE DB Destination isn't that slow if you use the fast load option. Performance wise, it comes close to the SQL Server Destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 9, 2010 at 6:20 pm
🙂 Yes. It worked for me as well. Now i understand why SQL Server Destination won't work from remote machine... read this article if you wanna know http://consultingblogs.emc.com/jamiethomson/archive/2006/08/14/4344.aspx
November 9, 2010 at 6:20 pm
🙂 Yes. It worked for me as well. Now i understand why SQL Server Destination won't work from remote machine... read this article if you wanna know http://consultingblogs.emc.com/jamiethomson/archive/2006/08/14/4344.aspx
August 17, 2012 at 9:57 am
Can you confirm if the SQL server authentication login you are using belongs to the Admin group or has admin rights, if not try with admin rights and it should work.
I too had the same issue and i did not change to OLEDB rather executed the package with admin account
Thanks.
August 19, 2012 at 11:55 pm
rasika.segan (8/17/2012)
Can you confirm if the SQL server authentication login you are using belongs to the Admin group or has admin rights, if not try with admin rights and it should work.I too had the same issue and i did not change to OLEDB rather executed the package with admin account
Thanks.
I sure hope the original poster solved his issue 4 years ago 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply