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

need to convert the string format to date from flat file to oracle tables uisng ssis

Hi I tried my best to convert the string to date format by pulling the flat file into the oracle tables.

data in csv file is in follwoing format:
Fri Oct 10 10:53:13 2008

Need to convert to Date format oct-10-08

I tried to put the derived column and wrote an expression for the following:

Substring([CREATIONTIME],5,3) + "-" + Substring([CREATIONTIME],9,2) + "-" + Right([CREATIONTIME],2).

i  then converted to DT_DBTimeshamp using data conversion (which is shown in the oledb destination column. it s failing because of type cast error...


Could you please guide me .. t

thanks..


0
srionline2k6
Asked:
srionline2k6
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Why use ssis and not sql loader?

If the column in Oracle is a date just use the format mask:
'Day Mon DD HH24:MI:SS YYYY'

0
 
srionline2k6Author Commented:
I need ot use ssis as part of ETL for loading into oracle.

So i am facing problem in Date conversion error

In the oracel it showing as Date datatype but in the oledb output column it is showing as DT_DBTIMESTAMP ..

Help me in this regard

0
 
slightwv (䄆 Netminder) Commented:
I'm not an ssis person.  In Oracle to convert strings to dates you tell it what format the string is in with a to_date function call.
0
 
srionline2k6Author Commented:


I need to convert  Source column (Creationdate) which is in string format    (Fri Oct 10 10:53:13 2008) coming from csv file to Destination Date format oct-10-08 which is an oracle column
0
 
slightwv (䄆 Netminder) Commented:
Oracle dates do not have a format.

It can implicitly convert strings to dates for you.  The default date format is: DD-MON-YY.

You can change this by setting the parameter NLS_DATE_FORMAT.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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