HI Problem is that I have a excel file with the column name ZIpcode and it has values like 90240 , 90234-23455 now the problenm is with this value '90234-23455' any zip code that has - in it show the value as Null when I preview in the Source editor . Any Suggetsionsarun

Add IMEX=1 in the Excel Connection String: http://sqlblog.de/blog/2009/04/ssis-excel-import-column-data-types/

It is showing me the correct values. Try this: Go to Advanced Editor for Excel Source --> Input Output Properties --> Excel Source Output (on the left pane) --> External Columns --> Click on ZipCode --> on the right pane check what data type it's showing. It should be Unicode String [DT_WSTR].

I tried that and got the error : The component is not in a valid state. The validation errors are: Error at Data Flow Task [Excel Source[1]]: The output column "zipcode" (16) on the error ouput has properties that do not match the properties of its corresponding data source column . Do you want the component to fix therse errors automatically" I tried both yes and No but still it shows the value as Null arun

Arun, what did you try? Sometimes metadata becomes a big issue after some changes in the components and starting from scratch works for me most of the times. (delete excel connection manager and source and start all over again). If you are working on a prod package make sure you have backup first.

I tried this Go to Advanced Editor for Excel Source --> Input Output Properties --> Excel Source Output (on the left pane) --> External Columns --> Click on ZipCode --> on the right pane check what data type it's showing. It should be Unicode String [DT_WSTR]. I also created new excel connection manager but it still shows the value as NULLarun

I wonder what is wrong. It works fine for me. Did you try what Christa suggested also? "Add IMEX=1 in the Excel Connection String: http://sqlblog.de/blog/2009/04/ssis-excel-import-column-data-types/" --by Christa

Yes I tried that also but not working for me .arun

That's weird. Ok forget about Source editor preview. Try this. After Excel source put Multicast transformation (kind of Trash. We are not going to use it or change anything in it). Right Click on the Flow (Green Arrow) --> Data Viewers --> Add --> OK --> OK. Then run it and check what Data Viewer shows you?

It shows NULL in place of 90234-23455arun

What number row is 90234-23455 in? e.g. Like in your excel first row has 92002, 2nd row has 90303 and so on. What number row is 90234-23455?

Row number is 4 in the excel sheetarun

If it was after row 8 then it should have been the cause but doesn't look like. hmm..sort that column such that that weird value is on top. Then make sure you have IMEX = 1 like this in the connection string: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\..\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1" Then try it.

The problem here is that the extended zip code (XXXXX-XXXX) isn't occurring frequently enough (or at all) in the first eight rows. When Excel reads your zip code column (it's not SSIS, it's Excel), it has to decide what data type to make it. Since it sees more standard zip codes (XXXXX) in the first eight rows than extended ones, it decides to treat the column as a number. Therefore, when it runs into an extended zip code, it can't put it into a number, so it refuses to read it - and passes a NULL. Using the IMEX=1 flag on the connection manager tells Excel that when it finds a column with mixed types (like it MIGHT in yours, if one of the first eight rows is an extended zip code), then it will read the column as a string. However, if you don't have an extended zip code in the first eight rows, then IMEX=1 will have NO EFFECT for you (yet). I think that's what's happening. So - you may have picked up that I keep saying "first eight rows". That's because (by default) the Excel driver is "lazy" :) It only looks at the first eight rows of the sheet to do this data type test. You can change this behaviour by finding the TypeGuessRows registry key. There may be several on your PC. (If/when you deploy this to a server, you'll have to muck with its registry too, unfortunately.) When you find it, you'll see it's value is 8. You can set it to anything from 0 to 16. A number from 1 to 16 tells it to scan than many rows to detect data types. A zero means read the whole file. Zero is probably what you want. In conjunction with IMEX=1, that will tell Excel to read the whole file, where it's sure to find an extended zip code, and then treat that "mixed type" column as a string. Talk to me now on