Pages

▼

Wednesday, July 27, 2011

Quick Hit: Using XLSM Files With SSIS

If you've tried to use an Excel 2010 XLSM file - a "macro-enabled" spreadsheet - with Integration Services, you'll have seen the incorrect (and badly worded) message that the "File path contains invalid Excel file.  Please provide file with .xls or .xlsx extension."  It looks like the SSIS team just didn't know that Excel makes other flavours of files.
How Do You Get Around This Limitation?
  1. Create an Excel Connection Manager to refer to ANY other spreadsheet with a "valid" XLS or XLSX extension.  (You don't have to save your XLSM file "as" an XLSX.)
  2. Select the Excel Connection Manager in your Connection Managers pane.
  3. Open the Properties Window (F4).
  4. Change the "Server Name" property to the full pathname of your XLSM file.
Done!
Posted at

9 comments:

  1. AkabelleApril 9, 2013 at 1:11 PM

    Thank you, thank you, thank you!

    ReplyDelete
  2. Rama IyerJune 26, 2013 at 4:34 AM

    Hi Todd,
    I have an xlsm file that I need to use as an destination. I tried what you have suggested above, but I am getting the following error.

    Error at Export_AllPeriods_XX [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Cannot update. Database or object is read-only.".

    Error at DFT - Copy Header Rows to Excel Sheets [Excel Destination - Header [840]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    I can verify that no other process is using the file.
    Please help!!

    Thanks,
    Rama

    ReplyDelete
  3. Todd McDermidJune 27, 2013 at 1:13 AM

    Hi Rama,

    Are you sure the file is not marked Read-Only? I can't think of any other reason why you'd get that error. The post was about how to trick SSIS into reading XLSM files... but I don't see why it wouldn't work the same for writing.

    Have you tried copying the file, or creating a brand new one in a different location, and trying to write to that?

    ReplyDelete
    Replies
    1. AnonymousJuly 21, 2015 at 2:55 AM

      I keep getting the error

      An OLE DB record is available. source: "Microsoft access Database Engine" Hresult: 0x8004005
      Description: " Cannot update. Database or object is read-only.";

      I made sure that my files are not read-only!

      Can you help plz Todd?

      Delete
    2. Todd McDermidJuly 21, 2015 at 8:41 AM

      Are you reading that file earlier in the Data Flow? That would be locking the file...

      Delete
  4. AnonymousOctober 21, 2013 at 10:55 AM

    How would you extract data if the file was Read-only?

    ReplyDelete
  5. UnknownOctober 25, 2013 at 1:47 AM

    Hi Todd Can you help me plzz,



    I am creating a ssis package in which i am reading data from xlsm(Macro enabled) file( Sheet1) and then loading a table.

    My problem is that i need to validate header from my XLSM file in the following manner


    these 3 header checks:
    1.The first line in the file is not a header
    2.Header count is zero
    3.Header count exceeds 1


    I have created a oledb connection and able to read data from sheet 1 but i dont have any idea how to validate header .

    ReplyDelete
    Replies
    1. Todd McDermidOctober 25, 2013 at 9:14 AM

      It's not going to be easy to do. What I'd recommend is that you continue using the Excel Source. That will require setting your connection manager up to NOT read headers.
      Then use multiple Data Flows to collect information about the sheet, and validate it. The first data flow can use a "SELECT * FROM [Sheet1$A1]" style select to look for data in the first row - you would then know how to validate that information, not me. That data flow should set an SSIS variable that indicates whether the file is valid (so far). Use a precedence constraint that examines that variable to pass control onto the next data flow (if the file is valid up to that point), to check for the next row's values. Once you've done all those individual checks to satisfy yourself the file is in good shape, then read the data you're interested in.
      If that process doesn't work, you will need to resort to using OpenXML API to read the file. Do NOT use script tasks to validate it! Read other articles here for why not - you'll only be causing yourself more grief if you try to use scripts.

      Delete
  6. AnonymousMarch 22, 2014 at 4:13 PM

    This is pathetic Microsoft. This (and Windows 8) are the reasons developers are fleeing. This is still a bug in SQL Server 2008 R2? My God. Microsoft, you really need to get back to your roots.

    ReplyDelete