February 23, 2009 at 3:25 am
Hi,
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?
ConnectionString:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePath;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";Mode=Read;Persist Security Info=False;
ExcelFilePath:
Data Source=servername;Initial Catalog=database;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False
February 23, 2009 at 8:14 am
Got around this by putting in an additional excel source (formatted for excel 2003) and a union all task. Only one of the excel sources will successfully output the data in the correct format but we have too many rows so added a script component to filter out the rubbish.
This seems to work quiet well and also allows us to add further formats at a later date as and when we get them.
February 23, 2009 at 9:22 am
I had read your post and couldn't come up with any ideas to help you out, but it seems like you've come up with a good one! So does the SSIS package figure out which source is which? Maybe based on the file extension?
Rick Todd
February 23, 2009 at 9:28 am
It actually pulls in data from both sources, hence the union all task and the script. If you can imagine, one source pulls all the data successfully, the other is a column out so forename is now surname and age is now title, etc. The script task validates the data and only outputs valid rows therefore filtering out the incorrect source data. It's not very efficient to process 2 sources of the same file but gets around my issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply