Hi, I am importing data from a tab in an Excel (xlsx) file and 2 of the columns I need are formulas. When the data is picked up by the package the values come in as 'NULL'. I am using IMEX=1 in the extended properties The Excel formulae are not referencing other cells on the sheet, the formula is [=DATE(2011,5,1)] I have tried to change the 'Output Columns' in the excel Source block to DT_WSTR which makes no difference I have also tried to change the External Columns in the Excel Source block to DT_WSTR but it complains that the error output doesn't match, and it won't let me change the data type on the error output! I do not really want to ask the client to type the date instead of using their stupid formula (why the hell wouldn't you just type the date out?!?!) Any help would be appreciated.Ed Clarke | www.atheonanalytics.com

Is there anything else in that column, or are there just dates in it? Try setting the registry setting TypeGuessRows for the JET provider to 0, it might help. ps: you cannot change the datatype of external columns, you can only change the datatype of the output columnsMCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Interestingly, if I open the xlsx file and save it as an xls then import that, SSIS reads the values fine???Ed Clarke | www.atheonanalytics.com

Interestingly, if I open the xlsx file and save it as an xls then import that, SSIS reads the values fine??? Ed Clarke | www.atheonanalytics.com Which provider were you using in the first place?MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Koen, thanks for the suggestion, I did read about this setting but I can't change the registry settings, or more specifically there is no point. I build SSIS packages which then get stitched into a bigger software platform which is implemented across multiple servers and I can't go changing the registries across all the servers.Ed Clarke | www.atheonanalytics.com

so for the xlsx I use: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=######.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1" and for the xls which worked I used: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=#######.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"Ed Clarke | www.atheonanalytics.com

If the registry setting is not set to 0, importing Excel files with SSIS using the JET provider can give unexpected results if a column contains mixed data types. Setting IMEX=1 alone is not enough. Maybe switch to CSV?MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

so for the xlsx I use: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=######.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1" and for the xls which worked I used: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=#######.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1" Ed Clarke | www.atheonanalytics.com Strange that there is a behavioural difference between the two providers. The column just contains dates which are populated by a formula?MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

I know IMEX=1 is not always the solution, but as I hinted in my first post this is one of those situations where it is next to impossible to get the client to change the file and I would quite like to find a workaround. I am taking the file from an external client rather than someone internally so unfortunately it is not easy to slap them around the head and tell them to produce a csv :) I am starting to get that sinking feeling that there isn't a solution to this one as it looks like there is some sort of bug in the provider which doesn't pick up the value of the formulaEd Clarke | www.atheonanalytics.com

Well, I tried it out on my computer, and I have no troubles importing a .xlsx file with the formula you provided. So I can't replicate the issue. But do you have other data types in that same column? Such as strings or integers?MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

There is a heading [Start Date] but as I have headers turned on I assume it is not reading that. The external columns of the excel source block is making the decision to set the data type of the columns in question as DT_DATE so it must be reading the values. Just in case this is influencing things I am running 64bit BIDS with 32 bit debug mode - I have 32 bit Office 2010 installed rather than 64 bitEd Clarke | www.atheonanalytics.com

There is a heading [Start Date] but as I have headers turned on I assume it is not reading that. The external columns of the excel source block is making the decision to set the data type of the columns in question as DT_DATE so it must be reading the values. Just in case this is influencing things I am running 64bit BIDS with 32 bit debug mode - I have 32 bit Office 2010 installed rather than 64 bit Ed Clarke | www.atheonanalytics.com I have the same set-up. And SSIS also choses DT_DATE in my packages. Strange strange...MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Perhaps try deleting those entire rows from your spreadsheet, or delete the entire column and redefine it by hand (no copy-paste). Don't just select cells and delete, right-click on the column or row header and delete the whole column or row. Sometimes formatting/etc "sticks" on cells even when you think they're clean... Talk to me now on

Todd, thanks for the suggestion. As I mentioned earlier, the package I am building gets stitched into a software platform which lets the client email a file into a specific address or sftp site, it then gets picked up and passed to SSIS and then loaded into SQL. This means that the file never gets touched by anyone and it's a 'lights-out' service so ideally I need to find a work around, or increasing it is looking like I will have no choice but to ask them to change the way the sheet is produced.Ed Clarke | www.atheonanalytics.com

Your other option (with which I am not familiar) is to use the OpenXML API to read the spreadsheet. That's not the COM API that's unstable in a server environment, it's a server-supported tech. Talk to me now on

Thanks for the suggestion Todd, that sounds like it's definitely worth a bit more investigation when I have time. Some good news from the client - they have just changed how the report is generated and it no longer uses a formula for the date columns - BONUS ;) Thanks for all the suggestions and I might post back if I get a chance to look at the OpenXML APIEd Clarke | www.atheonanalytics.com