Conversion from string to date in SSIS
I have been trying to read a flat file which has a birthdate field. The field is string and the format is "011594". I have been trying to convert it to a date field in the following format "01/15/1994". Using the forum I was able to write the following expression but I am still keep getting error messages. I also noticed few records aremissingbirthdateand few do not have the complete birthdate which means some birthdates are missing year and some are missingmonth. For example, some arecompletely missingand some are partially missing, like " " or "0312 ". Could this be the reason I was getting error message since the following expression does not include null and incomplete date of birth? (DT_DBDATE)(SUBSTRING([DateofBirth] ,1,2) + "/" + SUBSTRING([DateofBirth] ,3,2) + "/" + SUBSTRING([DateofBirth] ,5,2)) Here are the error messages: [Derived Column [1014]] Error: An error occurred while attempting to perform a type cast. [Derived Column [1014]] Error: The "component "Derived Column" (1014)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DOB" (2292)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (1014) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
April 2nd, 2008 12:34am

Yes, the incomplete and null dates could be the reason for the error messages you are receiving. You can use conditional split to remove the null and incomplete dates if you do not want to process them. Thanks, Bhargavi This posting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
April 2nd, 2008 2:01am

I was trying to use ISNULL(dateofbirth) for null value but what would I use for the incomplete dateofbirth in the conditional split?
April 2nd, 2008 7:27pm

For the incomplete data of birth you can use the LEN function to determine if the length of the incoming data is less than what you expect. For example, LEN([Date of Birth] ) < 6. Thanks, Bhargavi This posting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
April 2nd, 2008 7:36pm

I am using conditional split.Here are the conditional splits to different output but I am still getting error. Output1: Null DOB Records(for the records where dateofbirth is " ") condition # 1: DateofBirth == " " Output2: Incomplete DOB Records (for the records where dateofbirth is incomplete "0115 " condition #2: LEN(DateofBirth) < 6 Default output name is set to "Complete DOB Records" which is link to a "derived column" transformations to change the dateofBirth from "011594" to "1/15/1994". After running this I am getting the following error message: [Derived Column [1014]] Error: An error occurred while attempting to perform a type cast. [Derived Column [1014]] Error: The "component "Derived Column" (1014)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DOB" (2292)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (1014) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. I think I am doing something wrong in the conditional split. Thanks! Milto
April 2nd, 2008 8:47pm

You might try trimming the date in the both conditions ( TRIM(DateOfBirth)=="" ) to make sure it's catching all the rows that could cause an error.
Free Windows Admin Tool Kit Click here and download it now
April 3rd, 2008 4:06am

Have you used the Data Conversion control from the toolbox to convert your data type?
April 3rd, 2008 8:48am

MrProud the reason why Milto is posting the question and not use Data Conversion transform is because it will not work. The transform does not support a date format of ddmmy. Posting incorrect or misleading answers seems to becoming too frequent of late, please think or better still test your answers. Add the trim function to your tests as John suggests, that is a common issue, the spaces you cannot immediately see. For the rows that are still causing an error, perhaps set error rows to redirect so you can examine them in detail. Select Configure Error Output in the Derived Column editor, then set the error action to Redirect row. Use a Data Viewer to examine the rows quickly.
Free Windows Admin Tool Kit Click here and download it now
April 3rd, 2008 11:16am

Hi all. In that conditions, I almost always use the script component to perform total control over the possible format problems with the dates. The script code that I would use will be: Try Row.ConvertedDate = DateTime.Parse(Row.Date.Substring(0, 2) + "/" + Row.Date.Substring(2, 2) + "/" + Row.Date.Substring(4, 4)) Catch Row.ConvertedDate = DateTime.MinValue End Try Of course, you should declare the output column in the script component. With this approach you can even perform more advanced actions in the Catch statement, in order to do some custom transformations or to log the error entries into a log provider. Regards.
April 3rd, 2008 1:06pm

Samuel I agree that you can generally do more in code than with expressions, but for this scenario you could write an expression that did this. It could fully validate the date parts, and return a default value all in the expression syntax. It would be quite a large expression but my real point is this would still be much faster that a Script Component. In many cases the performance differential is of little importance, but for large loads it will become apparent. People often choose code over expressions, because they find it easier and more visible and that is fine, but you do need to be aware of and accept the performance decrease this incurs.
Free Windows Admin Tool Kit Click here and download it now
April 3rd, 2008 3:39pm

I used the following expression instead of (DT_DBDATE) in the Derived Column transformation whichI also addedto convert the datebefore I do the Conditional Split. (DT_DBTIMESTAMP)(SUBSTRING(DateofBirth,1,2) + "/" + SUBSTRING(DateofBirth,3,2) + "/" + SUBSTRING(DateofBirth,5,2)) Thank you all very much for your help.
April 4th, 2008 6:27pm

Thanks a lot. This worked for me too....
Free Windows Admin Tool Kit Click here and download it now
May 27th, 2011 7:39pm

hello i try all the obove and nothing work for me , itry to export string date from csv file and insert it into date column in database but it always get error in the drived column please some one helps me
September 7th, 2011 10:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics