question

paws27284 avatar image
paws27284 asked

SSIS loading null/blank datetime values to SQL Table

I am reading a flat file that contains blanks for some datetime fields. I have set up my table to allow nulls for the datetime column. However I am recieving the date value 1899-12-30 00:00:00.000 where I should recieve a blank. It is important that the date be blank or null because the data is loaded into a cube with a dimension based on dates and the rows with null dates should not be included. I have another database that contains Null in the datetime fields, but this is loaded from a different database, not a flat file. (Does this make a difference?) I am using SQL Server 2005 and Bids to load the files. I need to put either NULL or leave the value blank in the datetime column. Suggestions?
sql-server-2005ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
When you are loading the dates from flat file and want to handle empty strings as NULLs, then you have to read the date as string (the data type of the column should be DT_STR or DT_WSTR. Then you should put a Derived Column transformation into the data flow and you can put expression like **`[your_field] == "" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[your_field]`**. In case of reading from other table you do not need to handle this as the NULLs will be retained.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

paws27284 avatar image paws27284 commented ·
This worked great! thank you so very much!
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Welcome. If it worked, you can accept the answer so the question appears as answered in the lits.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
@Pavel +1 from here. Just wandering should TRIM function be applied on the column for safety reason if we have like " " as the string? In this case, the package will fail. I have learned it the hard way. :)(Obviously it is not the case with OP)
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
@Usman Butt sure if there could be non empty string full of spaces, then it will be necessary to trim it for comparison as SSIS behave differently than T-SQL as it does not remove spaces during comparison. Anyway it depends on the flat file structure whether there can be spaces or not.
0 Likes 0 ·
eq2home avatar image
eq2home answered
Thanks Pavel...your solution really helped and was quite easy to follow. Cheers
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.