I have an Excel (xls) file as a source and it has cell protection turned on to prevent editing the wrong cells. It's an official form from a national care authority, so I can't change that. I don't even have the password.
When I try to read that file in SSIS it gives me an error:
|Error at Package.dtsx [connection manager "xxxxx"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured. Error code: 0x80004005|
An OLE DB record is available. Source "Microsoft JET Database Engine"Hresult: 0x80004005
Description: Could Not Decrypt File
Error at xxxxx [Excel Source]"SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "xxxxx" failed with error code 0xC0202009.
|SSIS 2005 - 2012: Could not decrypt file|
The error doesn't occur when the file is still opened in Excel, but that's not really an option on a server.
You either have to remove the cell protection or convert the XLS file to XLSX (The ACE OLE DB provider doesn't have the same problem as the JET OLE DB provider). Also see/vote for this Microsoft Connect Bug report.
But can you do this automatically if you can't (or don't want to) ask that to the people who provided the excel files or if you have a whole bunch of those files? Here are a couple of options I have examined:
1) Interop Excel in Script Task
There is a Microsoft library (Microsoft.Office.Interop.Excel) available that can do that for you, but the BIG downside is that it requires an Office installation! Even if you download the Redistributable Primary Interop Assemblies (PIA), you still need Office installed. PIA is only a .Net wrapper that lets you communicate in .Net to the COM dll's from Office.
Serverside Office installation is a bad practice and Microsoft doesn't support/recommend an Office installation on a server.
2) Third party / open source dll's in Script Task
There are a lot of third party and open source dll's for excel. A .Net colleague of mine tried a whole bunch them, but they either don't work for the old (xls) excel files or they don't have an option to convert to xlsx or to remove the cell protection.
3) Cozyroc Excel Task
The COZYROC SSIS+ Library has an Excel Task that can do the conversion to XLSX for you. It's not for free, but I think it's worth it. Hiring an external BI/ETL consultant for two days is probably more expensive. Moreover you get a whole bunch of other cool tasks and transformations with it and you can test them freely within BIDS or SSDT before you buy them. This is my solution with the Cozyroc tasks.
|(with a little help from Cozyroc Consultant Ivan Peev)|
Note: Although the Cozyroc Excel Task works like a charm. I still think this is a bug that Microsoft should fix. Cell protection is an edit prevention and I 'm not editing...
Tip: You need to know the password to remove the cell protection in Excel. If you don't have it, use this vba macro to get it.