April 28, 2010 at 8:50 am
I am attaching the text file for reference in this context, i need to convert the string data type column to datetime. the date which i am getting in text file is null in some columns and there is a dd-mm-yyyy format in some columns and so please let me know the conversion expression.
April 28, 2010 at 9:00 am
did you trying changing the data type in the text file to dt_date or dt_dbdate type ?? If you just want to convert datatype using an expression it would be (DT_DATE)([yourcolumnname]).. if that doesn't work.. please try to post some more info on what you have tried till now.. so that we don't post the same thing again asking you to try
April 28, 2010 at 9:14 am
I did try all the steps which you have mentioned along with changing the data type to all the datatype options present in the text file using the advanced editior and it doesnt help at all.
April 28, 2010 at 9:18 am
[Derived Column [9908]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [9908]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (9908)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "Derived Column 1" (10280)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
thats the error i am getting when i use (dt_date)([my column]) in the derived column expression
April 28, 2010 at 10:34 am
I was using a Data Conversion where I configured the column 0 with Database Timestamp (DT_DBTIMESTAMP).
Input column : column 0
Output Alias : Copy of Column 0
Data Type : (DT_DBTIMESTAMP)
Flatfilesource will be
column 0 : which has data as per your file
Rex Smith
April 29, 2010 at 6:06 am
did you try this solution?
Rex Smith
April 29, 2010 at 8:20 am
Try to convert it using the expression (DT_DBDATE)[ColumnName] in a derived column and Most importantly Set the Option to Ignore Failure in the Error Output Tab of Derived Column Transformation. That would bring in NULL as your value incase of "bad Date" value
April 29, 2010 at 8:48 am
Sorry. Missed this point
Set the Option to Ignore Failure in the Error Output Tab of Derived Column Transformation.
There is a configure Error Output button in Derived Column Transformation Editor.
In the Error dropdown , I configured to Ignore Failure.
Is this correct?
Even if I do this, I get this error message
Validation error. Data Flow Task 1: SQL Server Destination 1 [335]: The column "Derived Column 1" can't be inserted because the conversion between types DT_DBDATE and DT_DBTIMESTAMP is not supported.
I am using SQL Server 2005 SSIS.
Rex Smith
April 29, 2010 at 9:25 am
Rex do you have the same problem as SNookala.. If so, 1st thing change the data type of Column0 in the text file to String and then use the expression (DT_DBDATE)[Column0] or (DT_DBtimestamp)[Column0] and set it to Ignore Failure.. I have tried it and works fine.. If this doesn't help can you post your sample data
April 29, 2010 at 9:34 am
Or derive a string in the universal format YYYY-MM-DD hh:mm:ss and just pump that straight into your SQL date field.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 29, 2010 at 9:40 am
Works with dbtimestamp but not with dbdate which is what I am surprised.
Rex Smith
April 29, 2010 at 12:09 pm
Phil Parkin (4/29/2010)
Or derive a string in the universal format YYYY-MM-DD hh:mm:ss and just pump that straight into your SQL date field.
I have to go with Phil here. Datetime conversions in SSIS are a real nightmare, but in SQL Server it works like a charm. Sometimes it amazes me that the two products belong to the same company :-).
I usually do the conversion immediately in the OLE DB Source, or I postpone it till the destination database. It saves me headaches...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply