current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

I want to load in information from an excel spreadsheet to the database using a SSIS package, and the package works fine when the spreadsheet doesn't change.

One of the requirements for this task is to let users add comments on the spreadsheet. There is a column for the comments in the spreadsheet already, so the users just add comments to that column.

When they save the edited spreadsheet and I run the package, the Excel Source complains that the external columns are out of sync, and even more strangely, the number of columns that are shown in the Mapping is less than the original spreadsheet.

What causes this and how do I fix it so that I can run this package automatically as a scheduled job in SSIS?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Found the problem: My spreadsheet had a couple of small empty columns at the front. My guess is that when the user saves the information back, these columns get deleted by excel (?) so the columns shifted, causing it to de-sync with the package.

share|improve this answer
    
Does the user's excel version match to yours? I have come across this problem a lot of times. –  rvphx May 2 '12 at 14:09
    
Yes, the entire company uses the same version (2007). The spreadsheet was generated by a report though, don't know if that makes a difference. –  confusedKid May 2 '12 at 14:13
    
One thing that I have found in past was that my excel sheet always had extra rows and columns appended to it automagically. As soon as I loaded the package in BIDS, it would complain about the sync issue. So my guess is, the process that gets you the data somehow appends extra rows and columns. To check this anomaly, I always used the preview data function. –  rvphx May 2 '12 at 14:15
    
Right. After I moved my matrix in the report so that there was no space between it and the margin, the extra columns disappeared, and the de-sync issue stopped. –  confusedKid May 2 '12 at 14:43
    
Did you get a solution to this problem? Please mark an answer if you found one. –  rvphx May 2 '12 at 17:40
up vote 1 down vote

try to remove the automatic mapping to columns names.

On the excel source there is an option to select if the first row has columns names. After you configured the mapping, set it to false. It should solve the problem but your transformation will fail now because it will treat the first row as a data row, so either you configure the output error to avoid package failure, or somehow removes the first row from the excel file. You can add a conditional split to do that.

share|improve this answer
    
I just tried as you suggested, but BIDS still complains about the synchronization. –  confusedKid May 1 '12 at 16:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.