|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, December 16, 2008 3:00 AM
Points: 1,
Visits: 21
|
|
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?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, October 20, 2009 8:39 AM
Points: 1,
Visits: 12
|
|
| 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.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Wednesday, November 18, 2009 6:08 AM
Points: 11,
Visits: 81
|
|
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
|
|
|
|
|
Forum 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
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, September 16, 2009 7:03 AM
Points: 57,
Visits: 5
|
|
You are very right. Oledb Destination should be used for OLEDB connection.
Thanks
Avinash
Kindest Regards,
Avinash
avin_barnwal@hotmail.com
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Saturday, November 14, 2009 12:17 PM
Points: 92,
Visits: 229
|
|
|
|
|
