I have been tasked with developing a SSIS 2005 solution that imports a list of names from an Excel 97-2003 worksheet. The worksheet will be sent out to members of the public to complete and then we'll import it to our SQL 2005 db. There will be approx 1000 of these per annum.
Issue: The first column of the worksheet is empty (we can't change the format), during testing we found that if we edit the worksheet in Excel 2007 (compatibilty mode) then the first column (which contains no values) does not get returned in the excel source preview, however, if you edit it in excel 2003 SP3 then the null column is returned, throwing out our columns.
We have tried changing the data access mode, originally we had a SQl command which pulled out the specific columns, but this returns an error after editing with 2003 re parameters?? we are now using Table or view mode which doesn't error but returns the null column hence we get a mismatch of data types and therefore no results.
Does anyone have any thoughts on how we either prevent this empty column being returned or how we deal with any other issues that may arise from the public saving this worksheet in whatever version of excel they're using?
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePath;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";Mode=Read;Persist Security Info=False;
Data Source=servername;Initial Catalog=database;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False