October 5, 2011 at 8:19 am
Okay,
I need to move advantage database files from the adavantage server to SQL Server.
Old way - linked server that connects to the advantage database files, using openquery select * into sql server.
New way - SSIS packages create data flows to transfer data using ole db source and sql server destinations.
I have set up an ole advantage connection and it works fine, all is going great.
But one of the files we have to restrict the amount of data we are importing. So I have reverted to the linked server, but I am using it in the ole db source as a t-sql command. I then created a sql server destination and it executed successfully in BIDS and also executing the package on its own.
But when I run the package as a job, either from file system or in sql server, it fails and gives the following errors.
Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 1:01:32 PM Error: 2011-10-04 13:01:34.17 Code: 0xC0202009
Source: Import Register SQL Server Destination [1254] Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0" 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.".
End Error Error: 2011-10-04 13:01:35.15 Code: 0xC0202071 Source: Import Register SQL Server Destination [1254]
Description: Unable to prepare the SSIS bulk insert for data insertion. End Error Error: 2011-10-04 13:01:35.17
Code: 0xC004701A Source: Import Register SSIS.Pipeline
Description: component "SQL Server Destination" (1254) failed the pre-execute phase and returned error code 0xC0202071.
End Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 1:01:32 PM Finished: 1:01:35 PM Elapsed: 3.12 seconds.
he package execution failed. The step failed.,00:00:03,0,0,,,,0
My reasoning is we aren't doing a bulk insert operation so the import to the SQL server destination fails, so I change to ole db destination and it works. Or it is a users permissions thing.
Funny thing is the sql destination works when I execute it in BIDS and using the execute package utility.
Any ideas or help would be appeciated, no biggy if we can't figure it out.
October 5, 2011 at 10:14 am
It sounds like the login running the SQLAgent doesn't have the access levels you do to the other system. I'd start there.
Primarily because of this component in the error:
"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
I'm not sure why portions would work with that, but that seems the most likely culprit.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2011 at 11:50 am
Try running your query in SSMS with EXECUTE AS the user instance that is failing. That might give you a better error message.
October 6, 2011 at 7:52 am
Dan,
To run the job from ssms this is the way I started it, it starts successfully, I know this because in my ssis package, I have a couple of execute sql tasks that are performed on the tables before I start bulk inserting,
of course those two sql task complete succesfully and the data flow fails, so in order for me to rerun the ssis package again I have to recreate indexes that I dropped.
As far as execute as a login, this is what I used, it says job started successfully.
john is a system admin.
EXECUTE AS LOGIN = 'john';
exec sp_start_job N'goimportdata' ;
GO
USE msdb ;
GO
EXEC dbo.sp_help_job
@job_name = N'goimportdata',
@job_aspect = N'ALL' ;
GO
although all names and names of jobs have have been changed to protect the innocent and no tables were harmed in the creation of this ssis package. 🙂
October 7, 2011 at 2:42 pm
Well,
I have done it again managed to make a bonehead mistake.:hehe:
Connection Manager, I believe when I first started created the package I had trouble with the name of the connection, especially the adavantage db connections, the name was the file dir.
That wasn't the problem, I had sql authentication and did not save the password for the sql connections.
Rookie mistake.
Works now , thank you who tried to help the impossible.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply