import data from dynamic excel to sql server 2008 r2 using SSIS without script task

Question

I have to import data from excel(number of columns will change every time)to sql server 2008. I have done script task for dynamically pulling the data. but the server has ace oledb and jet oledb registry errors. So is there any other way to import data from dynamic excel using SSIS without script task?

Why would you want to use SSIS for something this custom? SSIS is for nice, repeatable, well-defined inputs and outputs sort of tasks. What sort of system is gracefully being fed constantly-changing data documents anyway? — Kyle Hale
You said "server has ace oledb and jet oledb registry errors". Other data sources make use of connection manager, which will throw same exceptions in different environments, so "any other way" probably won't help. Is server 64bit? — OzrenTkalcecKrznaric
Don't you want to solve your ACE/OLEDB errors? I agree with @Kyle. Do not use SSIS to import a dynamic Excel spreadsheet. — Nick.McDermaid

Edmund Schweppe Edmund Schweppe · Accepted Answer · 2013-10-27T16:46:42

Depending on the version of Excel being used to generate the spreadsheets, you may be able to use the Open XML SDK 2.5 for Office libraries. You will still need a Script Task or Component (or a full-blown custom SSIS component), but you may be able to avoid the ACE and Jet engine errors.

Fixing the registry errors does seem like the easier approach, though.

import data from dynamic excel to sql server 2008 r2 using SSIS without script task

1 Answers