August 3, 2010 at 7:18 am
Hi,
I have a flat file source and sql server 2005 destination and i am importing data from flat file into sql server,everything works fine except the date data type in sql server.
i have tried many ways using derived column and script task but ended up with errors:
recently i came across a forum and used this in derived column:
(DT_DATE)(SUBSTRING((DT_STR,8,1252)columnname,5,4) +
SUBSTRING((DT_STR,8,1252)columnname,3,2) +
SUBSTRING((DT_STR,8,1252)columnname,1,2))
but i got the error:
[Derived Column 1 [243]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "component "Derived Column 1" (243)" failed because error code 0xC0049064 occurred,
and the error row disposition on "output column (1897)" "request_date"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.
i am attaching small portion of my source file below:
and the data type in sql server is datetime.[dt_str] should be converted into datetime.i have asked the same question in my previous forum and created a staging table as per the suggestions by expertise ,when i want to load the data from staging table to destination table again i got the problem with date field because there are 6 date fields and two of them have nulls and the date time data type is not accepting nulls.i have come across a forum asking for how to convert nulls into a valid date but didn't see any replies.
any help is appreciated and needed ,i have been doing this for 2 weeks and couldn't find a solution..
can anyone please help me ..its urgent...........
August 3, 2010 at 8:31 am
I've not opened your source file to check your incoming data format, but this is how I do it with the flat-files I get sent every month
(DT_DATE)SUBSTRING(date,1,4)) + "-" + (SUBSTRING(date,5,2) + "-" + SUBSTRING(date,7,2)
e.g "YYYY-MM-DD"
What you've written is instead "YYYYMMDD", which doesn't work.
Hope that helps
August 3, 2010 at 9:15 am
where should i specify input column ,i have replaced date with my input column and i got error.
can you please give me full syntax.
August 3, 2010 at 9:53 am
itskumar2004 (8/3/2010)
where should i specify input column ,i have replaced date with my input column and i got error.can you please give me full syntax.
I'm not actually at my work computer right now, so I can't double check this.
It's something like this: -
(DT_DATE)(SUBSTRING((DT_STR,8,1252)columnname,1,4) + SUBSTRING((DT_STR,8,1252)columnname,5,2) + SUBSTRING((DT_STR,8,1252)columnname,7,2))
-EDIT-
Just logged onto my machine and checked out one of my packages, especially for you 😉
This is from a working package: -
(((SUBSTRING(OPCS4ProcedureDate,5,4) + "-" + SUBSTRING(OPCS4ProcedureDate,3,2) + "-" + SUBSTRING(OPCS4ProcedureDate,1,2)) == "--") ? "" : (SUBSTRING(OPCS4ProcedureDate,5,4) + "-" + SUBSTRING(OPCS4ProcedureDate,3,2) + "-" + SUBSTRING(OPCS4ProcedureDate,1,2)))
August 3, 2010 at 11:27 am
Thanks for your help skcadavre .
This is what i did :
Derived REQUEST_DATE<add as new column>(((SUBSTRING(REQUEST_DATE,5,4) + "-" + SUBSTRING(REQUEST_DATE,3,2) + "-" + SUBSTRING(REQUEST_DATE,1,2)) == "--") ? "" : (SUBSTRING(REQUEST_DATE,5,4) + "-" + SUBSTRING(REQUEST_DATE,3,2) + "-" + SUBSTRING(REQUEST_DATE,1,2)))Unicode string [DT_WSTR]10
and i got this error:
[OLE DB Destination [2005]] Error: There was an error with input column "Derived REQUEST_DATE" (2092) on
input "OLE DB Destination Input" (2018). The column status returned was:
"The value could not be converted because of a potential loss of data.".
source data looks like this ,it contains 6 date columns:
Request_date 6/29/2003 4:55:12 AM
scheduled_date 6/29/2003
promise_date null
line_date 6/29/2003
update_date null
extract_date 6/30/2010 3:45:22 PM
This is how i want :
Request_date 2003-06-29 04:55:12.000
scheduled_date 2003-06-29 00:00:00.000
promise_date 1899-12-30 00:00:00.000 ----default date sql server uses if the date is null
line_date 2003-06-29 00:00:00.000
update_date 1899-12-30 00:00:00.000
extract_date 2010-06-30 03:45:22.000
I have tried your expression for the column request_date but i got error,there are nulls in the some of the date fields .
can anyone please tell me what expression i need to use to convert into date time data type.
August 4, 2010 at 8:21 am
itskumar2004 (8/3/2010)
I have tried your expression for the column request_date but i got error,there are nulls in the some of the date fields .can anyone please tell me what expression i need to use to convert into date time data type.
Last go I'm afraid, so I hope we get it this time.
I think the problem is that your data contains 1 or 2 digits for the month (and probably for day), which is confusing the statements. The below statement takes into account the possibility of source data with 1 digit for month, but not for day and doesn't include the time. It does take into account NULL and empty dates. You'll need to finish it off for yourself to fit the rest of your requirements.
ISNULL([REQUEST_DATE]) || [REQUEST_DATE] == "" || [REQUEST_DATE] == " " NULL(DT_DBDATE) : (SUBSTRING(SUBSTRING([REQUEST_DATE],1,2),2,1)=="/"?(DT_DATE)(SUBSTRING([REQUEST_DATE],6,4) + "-0" + SUBSTRING([REQUEST_DATE],1,1) + "-" + SUBSTRING([REQUEST_DATE],3,2):SUBSTRING([REQUEST_DATE],7,4) + "-" + SUBSTRING([REQUEST_DATE],1,2) + "-" + SUBSTRING([REQUEST_DATE],3,2))
Hope that helps.
August 9, 2010 at 2:15 pm
Hi skcadavre ,
Thank you very much for all the replies.
I have tried everything which didn't work and i know the reason the problem is not with the syntax,the data in the date columns are very bad .
Ex:7/29/0201,
6/4/0210,etc..........
This is the case for only history data which we are going to load only once ,and fortunately the oracle guy created a table for us and i am pulling all the data using linked server and everything works fine...
Thank You
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply