Hello,

I'm new to SSIS and I need help.
I am trying to upload an Excel xlsx file to a SQL 2008 database using 2008 BIDS to create the package.
When running the package through SQL Server Agent Jobs I get the following error, although the excel file is successful imported.

An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".

I am executing this on a 64-bit server ('Use 32 bit runtime' for the job step is checked).

Any one could help? Thanks!

What version of Excel was the file created with? Can you post the connection string from the Connection Manager you set up for the file?

1. Click on the Connection Manager

2. In the Properties Window, double-click on the ConnectionString property and copy it.

3. Post it here.

Hello,

The file was created with Excel 2007.

The connection string is: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\""+ @[MyImport::File_FullName]+"\";Extended Properties=\"Excel 12.0;HDR=YES\";"

The Connection Manager has the property DelayValidation set to true. The Excel Source has ValidateExternalMetadata set to False.

maybe the SSIS package is running on the machine/server where Excel is not installed due to which SQL Agent is able to find OLEDB component for Excel.

Please note that when you deploy and run SSIS package using SQL Agent, it means the package is running on that remote server only.

regards

joon

@Joon: Excel doesn't have to be installed on the server. The ACE OLE DB driver should be sufficient.

@luizemstefan: make sure the Excel file is really an excel file, so not HTML or something like that.

For example:
http://forums.asp.net/t/993765.aspx

Hello Koen,

Thanks for tip!
To be sure, I created a new Excel file with only a few records -  unfortunately I'm still getting the error. Strange enough, is that I have no error while running in BIDS and having the excel name specified in connection string. By running the package through SQL Server Agent Jobs I have this error, although all the datas are correctly imported.

I'm planning to rewrite the package, try to get the Excel content through a query, also test the IMEX property... Hopefully tomorrow I'll be back with some good news!

I doubt the query and IMEX are going to help, unfortunately. They help with reading the data, but as you said, that already works.

I have seen many issues with Excel, but this is a first.

Hello Koen,

You were right: It didn't work. But happy me, I could import the datas when the same excel was saved as CSV. 

I really don't know what I made wrong. Today, after recreating the packages, asking for new files, I got the following error:

Fehler: 2012-06-28 11:01:13.79

   Code: 0xC0202009

   Quelle: t_AdmHierarchy_v1-0 Verbindungs-Manager 'Excel-Verbindungs-Manager'

   Beschreibung: SSIS-Fehlercode 'DTS_E_OLEDBERROR'. OLE DB-Fehler. Fehlercode: 0x80004005.

Ein OLE DB-Datensatz ist verfgbar. Quelle: 'Microsoft Office Access Database Engine' HRESULT: 0x80004005 Beschreibung: 'Das Microsoft Office Access-Datenbankmodul kann die Datei '' nicht ffnen und nicht in die Datei schreiben. Sie ist bereits von einem anderen Benutzer exklusiv geffnet, oder Sie bentigen eine Berechtigung, um die Daten anzeigen und schreiben zu knnen.'.

Fehlerende

Fehler: 2012-06-28 11:01:13.79

   Code: 0xC020801C

   Quelle: Import File v1 Excel-Quelle [1062]

   Beschreibung: SSIS-Fehlercode 'DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER'. Fehler beim Aufrufen der AcquireConnection-Methode ber den Verbindungs-Manager 'Excel-Verbindungs-Manager' (Fehlercode: 0xC0202009). Mglicherweise wurden bereits Fehlermeldungen verffentlicht, die weitere Informationen zum Fehler beim Aufrufen der AcquireConnection-Methode beinhalten.

I got this error, although the file was not open...

Many thanks for your help.

Is this in BIDS? Maybe restart BIDS and make sure the package is running in 32-bit.

Nope. in SQL Server Agent Jobs. In BIDS the package ran successfully.

Hi luizamstefan,

The package runs successfully in BIDS  but failed in SQL Server Agnet, right? Please feel free to let me know if I misunderstand. In that case, please consider the following conditions

1. The user account that is used to run the package under SQL Server Agent differs from the original package author.
2. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.

You can find the detailed information in this KB article:
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step
http://support.microsoft.com/kb/918760 

You can check SQL Server Agents activity logs, Windows Event logs and SSIS logs to get more clues. Also the tool Process Monitor is helpful to track the cause of registry or file access related issues.  For more information about the issue, please see: http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e13c137c-1535-4475-8c2f-c7e6e7d125fc

Thanks,
Eileen

• Edited by Eileen Zhao Tuesday, July 03, 2012 8:36 AM
• Marked as answer by Eileen Zhao Monday, July 09, 2012 2:31 AM

Hi,

   So you've set up a SQL Server Agent job, and now you can't write to the file system. This is expected behavior, since SQL Server Agent uses SQL Server credentials to launch the job, not Windows credentials. So, what you'll have to do is this:

1. Under the Security folder in the Database Server in SQL Server Management Studio, find the Credentials folder.
2. Right click Credentials and hit New Credential.
3. Set up the Credential as you.
4. Expand SQL Server Agent, and right click Proxies, and hit New Proxy.
5. Set up the Proxy as you, using the Credential you just created.
6. Enable the Proxy to have the SSIS subsystem (if it's admin, check all of them).
7. Go edit the step on your job that calls the SSIS package.
8. Change the Run As field from SQL Server Agent to the Proxy that you just set up (if you did it right, it will be the only other choice in the dropdown).
9. You should be good to go!

and also check out the links::

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/9540dfaa-107f-4388-80b8-3f20557be96c/

http://forums.asp.net/p/1624894/4175291.aspx

Thanks,

Munna

• Marked as answer by Eileen Zhao Monday, July 09, 2012 2:31 AM

Hello,

We had a similar problem using Excel 2007 xlsx format loading into a sql2008 dbms, using an SSIS package.  The solution turnout to be solved when "Write Access" to the shared folder was granted to the developer account and the SQLAgent service account.

http://www.sqlserverbox.com/ssis-package-in-sql-server-agent-job-fails.html http://sqlism.blogspot.com/2012/08/ssis-package-in-sql-server-agent-job.html SSIS Package in SQL Server Agent Job Fails  Error: Argument "xyz" for option "connection" is not valid. The command line parameters are invalid. The step failed. Solution: If your SQL Server is 64 bit In SQL Server JOB-->Properties-->Steps---> In the Execution Option Tab -- Check "Use 32 bit runtime"  In the Data Sources Tab -- Uncheck the checkboxes of the connection managers if already checked.  Schedule the Job and run it.