I've tried changing the job settings to run 32 bit, still doing the same. I don't think OLEDB 12.0 is installed. Can't install it as admin/security guy is off, and their very strict on security.
I created the job to pull the data from an Excel sheet, load it to a table and create the job in the agent.
Another interesting thing that happened was on another server I had same issue, however changing it to run 32 bit worked, why not on this server?
Any thoughts on how to get it to run much appreciated.
Thanks
Use tab delimited data, less stress, early retirement
I always remember those files gave me loads of hassle with mapping etc. Is that simple enough with Excel though?
Have now created this as an agent job and it keeps failing saying the file is open or you do not have permissions to open it, strange because I just do with the import/export job!
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.7462.6 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:15:47 Error: 2020-03-25 12:15:48.17 Code: 0xC0202009 Source: IMPORT_DATA Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\server\data\excel file.xls'. It is already opened exclusively by another user, or you need permission to view its data.". End Error Error: 2020-03-25 12:15:48.17 Code: 0xC020801C Source: Data Flow Task 1 Source - Sheet1$ [55] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2020-03-25 12:15:48.17 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: Source - Sheet1$ failed validation and returned error code 0xC020801C. End Error Error: 2020-03-25 12:15:48.17 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2020-03-25 12:15:48.17 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:15:47 Finished: 12:15:48 Elapsed: 0.469 seconds. The package execution failed. The step failed.
Don't know why it's mentioning data flow task as this is not build from a SSIS project?
How did you create this agenda job,? From an import wizard? And did it fail at any point in time when you were building it
Yes, from import export and save the job as package into integrations services, then built schedulable job from that. It all ran fine until I create the job then failed.
Dos anyone know why I am now getting this:
The only thing I done was clear the job entries from the sysssispackages table as I want them all cleared out and start again.
Thanks
Andrew
That is why its mentioning ssis. Those wizards create ssis packages
1 Like
Never mess with those tables directly. Use the ssis catalog node to delete them there
1 Like