SSIS problem with date format

Hi,

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.
AngieWillAsked:
Who is Participating?
 
Reza RadConnect With a Mentor Consultant, TrainerCommented:
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

DONE!

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

1.JPG
2.JPG
3.JPG
4.JPG
5.JPG
0
 
Reza RadConsultant, TrainerCommented:
>>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 ?
0
 
AngieWillAuthor Commented:
I am new in SSIS. Can you give me a sample thanks?

I attach the excel file.

Thanks
Sample.xls
0
 
AngieWillAuthor Commented:
Excellent solution
0
 
Reza RadConsultant, TrainerCommented:
Glad to help,
Regards,

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.