aaron.reese (3/21/2014)
Firstly, Excel and SSIS will put you in a world of pain. Do you really need to use Excel as a source or destination. If you have another option I would seriously consider it.Secondly, Are you getting the error in your development environment or when you deploy the package and run in dtsExec.
Which version of SQL are you on
Which version of BIDS/SSDT are you using
Which version of Excel is on your development machince
Which version of Excel was used to create the file
Which version of Excel is on your SSIS server
Is your development machine 32 or 64 bit
Is your SSIS server 32 or 64 bit
Is any part of the spreadsheet protected
Sadly, yes I know its a pain in the ***. I have no choice... I am pulling data into our SQL Server from an external data source that only sends us excel files. The annoying part is they are constantly changing the damn sheet names and I want to automate this and not have to open the stupid things and change the name on the sheets before I run my packages. Luckily at least the name of the Excel workbooks are the same every time. I have to import about 13 different files.
To answer your questions:
1) SQL 2008 R2
2) Visual Studio 2008
3) Office 2010
4) No idea - but the files are .xls
5) Office 2010
6) 64 bit
7) Hmm... I'm assuming my SSIS is 64 bit since I have 64 bit SQL? Excel can only use the 32 bit SSIS though right?
8) No
I have not even got to the point of trying to run it from SQL Server... I'm just in BIDS trying to get the damn script to even build. Getting to run I know will be another story. The packages run just fine in SQL right now without the script task that I'm trying to add.