SSIS problem with date format

Posted on 2010-01-04
Last Modified: 2013-11-10

I want to import data from an excel file to a SQL Server 2005 database. The excel file contains a column with a date (for example: Tue Sep 22 00:00:00 2009). I must import this date in a datetime column in database. I try to format the column with 'ddd mmm d hh:mIs:ss yyyy' but doesn't work.
How can I import this value? directly change something in excel file? or use the Data Conversion or another in SSIS?

Thanks for your help.
Question by:AngieWill
    LVL 30

    Expert Comment

    by:Reza Rad
    >>I try to format the column with 'ddd mmm d hh:mIs:ss yyyy' but doesn't work.
    What do you mean exactly? did you receive any error?

    you can use change date format in excel  file, or data conversion transfromation , or use a script component transformation to convert your special date format.

    could you attach your excel file and I check it for you ?

    Author Comment

    I am new in SSIS. Can you give me a sample thanks?

    I attach the excel file.

    LVL 30

    Accepted Solution

    OK, look at attachments
    you must add a script component transformation between source and destination in dataflow
    when you add this , a dialog box ask from you what type of script component you want to use, select as transformation
    image 1 : data flow plan
    image 2: script component transformation input column
    image 3: script component transformation output column ( you must add a column)
    select visual C# as script language
    and copy code below in the appropriate method
    image 4: map output column of script component to oledb destination
    image 5: results


    public override void Input0_ProcessInputRow(Input0Buffer Row)
            string strValue = Row.ProjectStartDate;
            strValue = strValue.Substring(4);
            DateTime newDate;
            if (strValue[4] == " ".ToCharArray()[0])
                strValue = strValue.Substring(0, 4) + "0" + strValue.Substring(5);
            newDate = DateTime.ParseExact(strValue, "MMM dd HH:mm:ss yyyy", System.Globalization.CultureInfo.InvariantCulture);
            Row.DateOut = newDate;

    Open in new window


    Author Closing Comment

    Excellent solution
    LVL 30

    Expert Comment

    by:Reza Rad
    Glad to help,


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    734 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

    22 Experts available now in Live!

    Get 1:1 Help Now