SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excel source null column issue


Excel source null column issue

Author
Message
John Higginbotham
John Higginbotham
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
John Higginbotham
John Higginbotham
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Rick Todd
Rick Todd
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 441
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
John Higginbotham
John Higginbotham
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search