Null value handling in SSIS package

Hi All

I am facing this problem while loading data from text file into Table.

Scenario is -

There are chances of having spaces for null values in text file.

when i m trying to rum my SSIS package this is getting failed.

How can i avoid this problem? i want null values to be inserted if ther is spaces for that field in text file.

Thanks,

Anshu

Hi,

You can use a derived column transformation and check to see if your input column value has all spaces... trim and followed by len and if len == 0, set the column value to null.

Hope this helps...

NULL Entry in Lookup

Hi,

In my Integration Scenario I have a source field 'CompanyTypeCode' which is nullable in 'tblCompany' and There is a specific lookup Table 'tblCompanyType' to map the codes with descriptions for this field. When I want to transform this table, The NULL Entries fail in Lookup and I ran into problem. Please suggest a clean solution for bypassing the lookup when the entry in source is NULL. (If I bypass the lookup, the flow changes since there is not an extra column from the lookup component any more, therefore I must have another OLEDB Destination if the code is NULL).

Regards,
Sassan

Sassan,

You can still have the extra column if the lookup fails. What you do is in your error path from the lookup add a derived column for the description and put an "Unknown type" string in there. That way both the pipelines will have the same number of data columns.
You can then 'union all' the clean and error outputs from the lookup to a single destination.

I don't think you should bypass the lookup, but if you want to do it, how about using a conditional split before the lookup to re-route the NULL rows?

Hope that helps.