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

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.
0
AngieWill
Asked:
AngieWill
  • 3
  • 2
1 Solution
 
Reza RadCommented:
>>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
 
Reza RadCommented:
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
 
AngieWillAuthor Commented:
Excellent solution
0
 
Reza RadCommented:
Glad to help,
Regards,

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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