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

Posted on 2011-10-13
Last Modified: 2012-05-12
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


Question by:srionline2k6
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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'


    Author Comment

    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

    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Author Comment


    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
    LVL 76

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now