SSIS unpivot returns different results when run via a SQL agent job

Question

I hope someone can help me. I have an SSIS package which grabs data from an excel (.xls) file, unpivots it from a rubbish format into a 'proper' format, transfers it to a SQL server 2008 R2 db, and runs some stored procedures. I thought I had it working but testing has thrown up some interesting results. The Excel file has years as separate columns, from 2000 - 2050, plus a whole bunch of other fields.

The really annoying thing is this: when I kick off the SSIS package manually it works as expected. If I run it in BIDS debug mode it works as expected. However if I run it via my SQL agent job it omits all years after 2027. These records are simply not appended. This is causing me some frustration!

Some troubleshooting using the logging functionality has led me to the following information:

OnWarning,EDI-SQLINTDEV01,WOODMAC\ServicePFF,DataFlow Append Temp Data,
{18E3EDB7-DBFE-49E3-8187-7B5CFA4684F6},{48A81BE7-AEDB-4313-A88D-537C5F14693F},
14/08/2014 15:56:24,14/08/2014 15:56:24,-2147192632,0x,
The external columns for component "Excel - Gen Capacity" (2121) are out of 
synchronization with the data source columns. The external column "2028" needs to be updated.
The external column "2029" needs to be updated.
The external column "2030" needs to be updated.

(etc. all the way to 2050)

If I run the package manually these entries are not included in the output log.

Can anyone help me?

It's probably relevant to mention that the filepath of the excel file is stored in a user variable, and is different every time, and is retrieved from a SQL query.

EDIT:

I've made a bit more progress with troubleshooting. I know why it starts failing at 2028. It's because there were some empty cells beneath the column headers and 2028 was the first year where there were 8 or more of these. So, bizarrely, if there are 7 or fewer null rows beneath the column header, it will simply ignore the nulls and append all the data for that column. However if there are 8 or more nulls, it seems to ignore the entire column regardless of how much data there is further down. No records are unpivoted where there are 8 or more empty cells below the column heading!!!

How do I get round this? There must be a property setting somewhere.

Answer 1

I think I've fixed it. Thought I'd share the answer in case anyone else comes across this exact same issue. All it took was the addition of IMEX=1 to the Excel connection string.

I stumbled across this post which explains more: Why SSIS always gets Excel data types wrong, and how to fix it!

• Tom V: It's not silly to answer your own question, it's actually good practice. You can also mark it as accepted. See stackoverflow.com/help/self-answer