I have a package that I used to execute via DTS in SQL Server 2000, that I am attempting to move to SSIS in SQL Server 2005.
The package takes the contents of an Excel Spreadsheet and copies it to a SQL Server table.
In DTS, I was able to copy the excel spreadsheet to a SQL table even if someone had the spreadsheet open.
In SSIS, if someone has the spreadsheet open, I get an error message and I cannot copy it to the SQL table.
This spreadsheet is open pretty much all day, but I need to copy the information multiple times per day.
Is there a way to copy from Excel to a SQL Server table even if the Excel Spreadsheet is open?