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.aspxMCTS, MCITP - Please mark posts as answered where appropriate.

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.MCTS, MCITP - Please mark posts as answered where appropriate.

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: Under the Security folder in the Database Server in SQL Server Management Studio, find the Credentials folder.Right click Credentials and hit New Credential.Set up the Credential as you.Expand SQL Server Agent, and right click Proxies, and hit New Proxy.Set up the Proxy as you, using the Credential you just created.Enable the Proxy to have the SSIS subsystem (if it's admin, check all of them).Go edit the step on your job that calls the SSIS package.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).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

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: Under the Security folder in the Database Server in SQL Server Management Studio, find the Credentials folder.Right click Credentials and hit New Credential.Set up the Credential as you.Expand SQL Server Agent, and right click Proxies, and hit New Proxy.Set up the Proxy as you, using the Credential you just created.Enable the Proxy to have the SSIS subsystem (if it's admin, check all of them).Go edit the step on your job that calls the SSIS package.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).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

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.MCTS, MCITP - Please mark posts as answered where appropriate.

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

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.