Getting null values for date and String columns(SSIS)

Question

Getting null values for date and String columns(SSIS)

M_GREAT_4_SQL

Right there with Babe

Points: 752
More actions
December 26, 2009 at 6:19 am

#136149

Hi,
I am using SSIS to extract data from excel sheet(Sample is attached). There are two columns.
1) First column contains date values in dd/MM/yyyy format but for first value (31/12/2009) I am getting null values. I know excel treat MM/dd/yyyy format as a base data but stilll I hope that there should be machenism to extract exact data in any format.
2) Another column contains alpa numeric values but for String characters SSIS getting null values. Is there any mechanism available to get the exact value from excel sheet.
FYI I am using SSIS 2005.
Thanks in advance,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kbatta SSCrazy Points: 2274 More actions · Answer 1

Excel determines the data type of each column by looking at the first 8 rows. If some cells look like numbers and some look like text, it will choose one type and throw away the other values.

In the file you attached just adding IMEX=1 to the connection string will force columns with mixed types to be treated as text and all values will be kept. Here is the connection string I used. You can build it up with variables.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Attachment4793.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

But if the first 8 rows do not show a mixture of types, this will not work.

One way around this is to also define the Excel file as not having headers. Since headers are normally text, this combined with IMEX=1 will force all values to text.

In the data flow task right after the Excel source put a script component that keeps track of the row number.

Then have a conditional split that sends the header row (row 1) one way and the rest of the rows to where they will be processed.

If you use this approach, it is a good idea to do some edits on the column headers in case someone changes the file format on you.

This does mean that the date will come in as a string and you will need to convert it to date format.

wwei SSC Eights! Points: 961 More actions · Answer 2

Hi kbatta, Thank you for this post. It helps me too.

M_GREAT_4_SQL Right there with Babe Points: 752 More actions · Answer 3

Thanks for the help..It is working perfectly fine...

but I have another question which I have posted already in the forum and may be this information will help me to resolve that issue.

The issue is with Unicode string in excel file, I have to get complete string but I am getting maximum 255 charecters. The excel column is having Unicode String values. Do I need to put something in the connection string mentioned by you.

Thanks,

M_GREAT_4_SQL Right there with Babe Points: 752 More actions · Answer 4

I also used MaxScanRows=1 in connection string, but still not able to solve the problem(Client does not allow to do registry entry setting).

I went through with this blog also but could not get the solution.

http://blog.lab49.com/archives/196

Issue is what if I get the data issue in after 20th Row. for Example from 21st row I am getting some values exceeding 255 charecters.

Thanks,

M_GREAT_4_SQL Right there with Babe Points: 752 More actions · Answer 5

Correction I used MaxScanRows=0

Even for the current post, there is a problem.

If I am getting same type of values for first 8 rows and for next rows (if there is a data type mismatch )I will get null values.

Thanks,

M_GREAT_4_SQL Right there with Babe Points: 752 More actions · Answer 6

I am sorry...

I repeated almost the same thing which u already made clear in your reply....

my question is to get values for cell which is having more than 255 charecters and It comes after 20th Row.

Is there any work around or one need to perform manual activities.

Thanks,