We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SSIS Loading Data from flat file connection

healthcheckinc
on
Medium Priority
393 Views
Last Modified: 2013-11-10
Im trying to use logic for a derived column. This is what I have...
[Column 53] == "00/00/0000" ? "01/01/1801" : [Column 53]
Its obviously wrong but I dont think by much.
Logic:
If [Column 53] = "00/00/0000" Then
   "01/01/1801"
ELSE
   [Column 53]
Comment
Watch Question

CERTIFIED EXPERT

Commented:
The expression looks correct if your Derived Column is a DT_WSTR.

Author

Commented:
Well I tried that but why couldnt I keep it as a date. I would be setting it to "01/01/1801" if it = "00/00/0000" else it would be the vaild date??

Author

Commented:
it didnt work for DT_WSTR either though...
CERTIFIED EXPERT

Commented:
Please post a sample set of the date values vound in the file.

Author

Commented:
They are either a valid date 02/04/2008 or invalid date 00/00/0000. Is a derived column the best thing to use? What would your syntax be in the expression for this?
CERTIFIED EXPERT
Commented:
Would a NULL date column be a good answer. If the file has zeros, return a NULL? OR do you want the date to be 1801?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
I actually just made the row a string and made it a blank string if it had all zeros...It was the datatype that was messing me up I guess.... Thats waht you get for letting SSIS Suggest your data types for yuo. Thanks for your help and I will give you the points anyway...

Author

Commented:
thanks
CERTIFIED EXPERT

Commented:
Cool
Here is the Derived Column code to return a NULL date value or the actual date value.
TRIM([Column 0]) == "00/00/0000" ? NULL(DT_DBDATE) : (DT_DBDATE)[Column 0]
First I get rid of all spaces, just in case. Then, if the value is zeros I set it to a NULL db_date value. If it is not zeros, I convert value to a db_date. In my example I tested, here are the values I get from a text file:
02/04/2008
01/23/2006
00/00/0000
02/04/2008
01/23/2006
00/00/0000
02/04/2008
01/23/2006
00/00/0000
Here is the output. First the Original column, then the Derived Column second delimited by a comma.
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,

As you can see, the zeros are now NULL. Good Luck. Hogg

Author

Commented:
thanks man....I will need you in the near future so I will talk to you again...
CERTIFIED EXPERT

Commented:
Actually, don't forget to get rid of the spaces in the last part either. Screen shot attached.
TRIM([Column 0]) == "00/00/0000" ? NULL(DT_DBDATE) : (DT_DBDATE)TRIM([Column 0])

derived-with-null-date.bmp
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.