March 21, 2010 at 2:25 pm
I am a SQL Server Data Warehouse Developer with plenty of experience in t-sql and SSIS, but I currently have a problem that I have not been able to find a solution for anywhere. I need to implement a solution to programmatically load data from a password protected .xls file into a SQL database table. So far, I have considered the following:
SSIS:
-Data Flow Task using Excel connection manager
-Data Flow Task using ODBC connection
-Script task using .Net to read the data using Excel connection methods
-Script task using .Net with Jet provider
T-SQL:
-Openrowset using Jet provider
Other:
-vbscript using Jet provider or ODBC to store data and work with it from there
...but none of these methods seem to be valid options. It actually seems like all of them explicitly do NOT support reading password protected excel data.
Any help would be greatly appreciated.
thanks
March 21, 2010 at 3:43 pm
We know that it couldn't done with standard procedures to read XLS file with pass protected, but take a look this code and I hope it would help you.
March 21, 2010 at 7:23 pm
Thanks for the quick response. I was hoping not to have to write a c# console app to accomplish this task, but it is looking more and more like that will be the case.
One other approach that I may pursue is to take the password off of the file, and compress it in a password protected .zip file instead. With that approach, I could just use native SSIS tools to decrypt the .zip file and then use a standard excel connection manager to load the file.
March 22, 2010 at 2:02 am
" I need to implement a solution to programmatically load data from a password protected .xls file into a SQL database table. "
Have you considered to use VBA and ADO in Excel? For me that works in a situataion
you describe.
//Gosta
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply