none
"External table is not in the expected format." and Proxy permissions

    Question

  • Hello,

    I am running SQL 2008 R2 64-bit on Windows Server 2008 R2 64-bit, without MS Office installed, but the "Microsoft Office Access database engine 2007 (English)" is installed.  I am trying to upload an Excel xlsx file (saved with Excel 2010) to a SQL local database via an SSIS package that is launched via a SQL Job run as a domain account by way of Proxy.  The connection manager is: "Microsoft Excel 2007", the highest version I could pick, even though I have Office 2010 installed on the dev machine that built the SSIS package.  

    If this domain account is in the local Administrator's group (Windows group), the job succeeds.  Other than that, the job fails, blaming:

    Error: 2010-08-18 20:41:07.93
       Code: 0xC0202009
       Source: SPORT_Import_Finance Connection manager "ExcelFinanceMapping"
       Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".

    I don't want the domain account to be an Administrator on the server, but I don't know how else to make this job succeed.

    Thoughts?

    Bassim

    Thursday, August 19, 2010 3:59 AM

Answers

All replies

  • Hi Bassim. The SQL Agent account has permissions to open the Excel file? This seems that the execute account do not have access to open the file. To check it follow these steps:

    • Go to SQL Server configuration manager and review the SQL Agent execute account
    • Localize the excel file, and add the previous step account with read permissions.
    • Try run the package.

    Tell us what´s up later.


    Víctor M. Sánchez García (ES) (BI) Hope this help. if this answer your question, please mark as it.
    Thursday, August 19, 2010 6:43 AM
  • Hi atharky,

    No, the SQL Agent user does not have permission to open the file, which is on a share on another server.  Originally, the SQL Agent was a different domain account.  I changed SQL Agent account to be Local System, just to see if that would change things, and it didn't. 

    The Proxy account has Full Control of the remote Excel file, Full Control of the remote NTFS folder, and Full Control of the remote share.  I don't think it has to do with the file location or access to it, since when the local permissions of the Proxy account are elevated to Administrator, the job succeeds.  I have tried copying the file locally, and changing the config file to point to it locally, and I see the same behaviour: success when the Proxy account is a member of the Administrators group, and failure otherwise.  I think there is a problem accessing a local resource, perhaps some local storage area or local conversion utility.

    I forgot to mention that the package was developed on a 32-bit Windows 7 machine running SQL 2008 R2 32-bit.  Not sure if that makes any difference, since the job's execution options are set to run in 32-bit mode.  From the job log:

    Microsoft (R) SQL Server Execute Package Utility
    Version 10.50.1600.1 for 32-bit
    Copyright (C) Microsoft Corporation 2010. All rights reserved.

    The strange thing is, I am now successfully importing a different Excel file from the same share, with the same Proxy account, when that Proxy account's permissions on the local server are not elevated.  The second file has much fewer tabs with data that are being imported (4 tabs vs. 12 tabs).  This could suggest that the original file is corrupt, but that would not explain why the job succeeds when the permissions are elevated.

    The only thing I keep coming back to is some kind of resource that the Proxy account needs to access locally to process the data.

     

    Thursday, August 19, 2010 12:36 PM
  • If this helps, this is the full Job Log details:

    Date  8/19/2010 8:19:07 AM
    Log  Job History (SPORT_Import_Finance)

    Step ID  1
    Server  abc
    Job Name  SPORT_Import_Finance
    Step Name  SPORTImportFinance
    Duration  00:00:04
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    Microsoft (R) SQL Server Execute Package Utility
    Version 10.50.1600.1 for 32-bit
    Copyright (C) Microsoft Corporation 2010. All rights reserved.

    Started:  8:19:07 AM
    Error: 2010-08-19 08:19:11.73
       Code: 0xC0202009
       Source: SPORT_Import_Finance Connection manager "ExcelFinanceMapping"
       Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".
    End Error
    Error: 2010-08-19 08:19:11.73
       Code: 0xC020801C
       Source: ImportExcelFileToStage map_RevBreakout_src [1]
       Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ExcelFinanceMapping" 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: 2010-08-19 08:19:11.73
       Code: 0xC0047017
       Source: ImportExcelFileToStage SSIS.Pipeline
       Description: component "map_RevBreakout_src" (1) failed validation and returned error code 0xC020801C.
    End Error
    Error: 2010-08-19 08:19:11.73
       Code: 0xC004700C
       Source: ImportExcelFileToStage SSIS.Pipeline
       Description: One or more component failed validation.
    End Error
    Error: 2010-08-19 08:19:11.73
       Code: 0xC0024107
       Source: ImportExcelFileToStage
       Description: There were errors during task validation.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started:  8:19:07 AM
    Finished: 8:19:11 AM
    Elapsed:  4.406 seconds

    Thursday, August 19, 2010 3:25 PM
  • Hi again. Perhaps you should review the content of these workbook to check that sheet isn't corrupted, try to save in another workbook. Other side to check is extended properties in connection string, like IMEX that allows read mixed data types as text.

    Anyway the second error message about "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER", seems that are not open the book. Explain a bit whats do the package, variables, expressions on components settings...

    Also can see another post about this: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/44748215-6799-4708-a52c-bd04809953d8/

    Regards.

     


    Víctor M. Sánchez García (ES) (BI) Hope this help. if this answer your question, please mark as it.
    Friday, August 20, 2010 6:50 AM
  • One thing. The excel was save by 2010 client version and the server has only installed 2007 Runtime? You should install the last version of Office Runtime (2010) ON SERVER WHERE YOU´RE EXECUTING THE PACKAGE.

    http://www.microsoft.com/downloads/details.aspx?FamilyID=57A350CD-5250-4DF6-BFD1-6CED700A6715&displaylang=ru


    Víctor M. Sánchez García (ES) (BI) Hope this help. if this answer your question, please mark as it.
    • Proposed as answer by Víctor M Friday, August 20, 2010 11:48 AM
    • Marked as answer by bmatuk Saturday, August 21, 2010 12:24 PM
    • Unmarked as answer by bmatuk Saturday, August 21, 2010 12:24 PM
    • Marked as answer by bmatuk Saturday, August 21, 2010 12:25 PM
    Friday, August 20, 2010 6:53 AM
  • Thanks Victor,

    I did consider a corrupt Excel file, and recreated it by starting with a blank workbook, and copying each tab's data into notepad and then copying from notepad into the new tab in order to remove any special formatting/characters, but that didn't seem to resolve the issue.

    I am also not dynamically creating the connection strings - I am using XML configuration files.

    Thanks!

    Saturday, August 21, 2010 12:13 PM
  • Hi Victor,

    The package is using the Office 12 ACE provider, and, since the package succeeds when run as admin, I don't it is a provider issue.

    What I ended up doing was making a copy of the entire solution, and removing all of the other packages from the projects, as well as the shared data connections that didn't pertain to this particular package.  I saved the project and solution under a different name.  My intention was to confirm that the package fails, and then slowly remove each data flow task (pump from a tab) until the package succeeded, or I was left with no more data pumps.  That would tell me if the issue was related to a specific tab or piece of data.

    The problem is that the job succeeded without changing anything in the package.  I don't like having unexplained reasons for things suddenly working, but my time lines are tight, so, for now, I'll just consider myself lucky... I guess...

    I really appreciate your time for helping me investigate this issue, and the suggestions that you posted were very insightful and valuable.  Since I never really resulted in a reason for the error, I'll mark this response as the answer in gratitude for you time and effort :-)

    • Marked as answer by bmatuk Saturday, August 21, 2010 12:24 PM
    • Unmarked as answer by bmatuk Saturday, August 21, 2010 12:25 PM
    Saturday, August 21, 2010 12:24 PM
  • Hi Bmatuk. But was solved installing Office 2010 runtime on server?


    Víctor M. Sánchez García (ES) (BI) Hope this help. if this answer your question, please mark as it.
    Saturday, August 21, 2010 12:25 PM
  • Hi Victor,

    No, I did not install this runtime. 

    Thanks!

    Monday, August 23, 2010 2:21 PM
  • Hi Victor,

    It seems that it is not just this package that was suffering from this same issue.  I had two other packages in the same solution that were behaving strangely after minor changes.  Both of these packages populated Access 2007-2010 databases (.accdb) from SQL server.

    The jobs would report success, but the work would not actually get done.  I finally installed the Office 2010 database runtime, but that didn't solve the issue.

    I copied the solution twice, opened each copy and removed all other packages except the one package, saved the solutions, and had them deployed.  Without changing the packages, they both suddenly started working.

    From now on, if there is any strange behaviour, I will try reducing the solutions to single package projects first :-)

    Tuesday, August 24, 2010 12:45 PM