Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Excel source null column issue Expand / Collapse
Author
Message
Posted Monday, February 23, 2009 3:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 3:19 AM
Points: 8, Visits: 43
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

Post #662443
Posted Monday, February 23, 2009 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 3:19 AM
Points: 8, Visits: 43
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.
Post #662615
Posted Monday, February 23, 2009 9:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:02 AM
Points: 216, Visits: 424
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
Post #662696
Posted Monday, February 23, 2009 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 3:19 AM
Points: 8, Visits: 43
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.
Post #662707
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse