[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1101
  • Last Modified:

How do I validate date format to be mmddyyyy during data conversion in SSIS

I am importing a excel file and tryinng to convert data types to match the database types.
I have a date field in the source file. I used DB_TIMESTAMP during data conversion.
But when I run the package, it throws error on the rows where the date field is empty (ie, there is 'NULL' in the source file)
Also how do I make sure that the date is in mmddyyyy format?
Please help!
  • 2
1 Solution

use an expression while you are converting i.e. you should do this control and conversion in a derived column component.

FIELD_NAME == "" ? (DT_STR,8,1254)NULL(DT_STR,4,1254) : FIELD_NAME

here 1254 is the code page and you should change it to your code page. this expression returns the date values and NULL for the empty ones.
Use a conditional expression: «boolean_expression» ? «when_true» : «when_false»
Here is the expression, assuming your column from the file was Column 0. You also need to make sure the Destination table accepts NULL values in the date column.
LEN(TRIM([Column 0])) > 1 ? (DT_DBTIMESTAMP)[Column 0] : NULL(DT_DBTIMESTAMP)  
dorsunAuthor Commented:
Thank you this worked
dorsunAuthor Commented:
Hi tigin44,

Your solution worked in the derived column. Later I had to use data conversion to convert to database type.
The derived column type is string Unicode String [DT_WSTR]
In data conversion I want to convert to DBTIMESTAMP. Since the data type of the column in the database is of datetime.
When I run the package, I get an error 'Data conversion failed while converting column birth_date_dc(derived column) to column birth_date_db (database)

Please help me out in this,


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now