0

I am trying to load data into MySQL from flat file using SSIS. Column in MySQL is datetime type and when I tried to load date normally it is saving as yyyy-mm-dd hh:mm:ss. But I want the datetime to be mm-dd-yyyy 23:59:59.

Chow
  • 103
  • 1
  • 3
  • 6
  • 1
    I think that's how databases store dates. If you want it in a different format then you probably need to look at some date --> string formatting functionality. [This may help](http://stackoverflow.com/questions/6922517/how-do-i-format-date-value-as-yyyy-mm-dd-using-ssis-expression-builder) – Sam Nov 27 '13 at 14:51
  • Why? Just let it be stored like that. Then when you need to export it format it on the way out. It's like a line of c# – TsSkTo Nov 27 '13 at 14:59
  • @Sam - I get it using string formating. But the problem is the column in MySQL has datetime format and I am having problem converting string to datetime again. – Chow Nov 27 '13 at 15:01
  • @TsSkTo I am uploading the data to already existed tables. And the value is like mm-dd-yyyy in them. There will be a mismatch in format when I just upload like this. – Chow Nov 27 '13 at 15:04
  • Well then, you have two options. You can use a `derived column` transformation with the "String functions" defined. I wouldnt recommend this as it is not really maintainable. The second option is a transformation script component. You will only have to add like two lines of code to make that work. Let me know if you need an example – TsSkTo Nov 27 '13 at 15:10
  • @TsSkTo That would be helpful if you show an example. Thanks!! – Chow Nov 27 '13 at 15:32
  • You are loading to `MySQL` or Microsoft's database product `SQL Server` which some folks confusingly try to designate `MSSQL`? – billinkc Nov 27 '13 at 15:49
  • @billinkc I am loading to MySQL not MS SQL SERVER. – Chow Nov 27 '13 at 15:55
  • @Chow if date is inserted in date type column it doesn't matter how formatted date is. You can do your own formatting when you select data from table, But if you want to see formated date direct in mysql database, there probably should be option on mysql database.... – Justin Nov 29 '13 at 07:41
  • @Justin I got permissions to modify the datatype of field.changed to varchar and directly imported the required format. – Chow Nov 29 '13 at 18:41
  • @Chow and works selecting record by date? something like this from 2012-02-23 00:12:46 to 2013-06-10 12:10:30 of course in your format. – Justin Nov 29 '13 at 19:22

1 Answers1

0

This is a simple flow to do the job.

enter image description here

When you add the Script Component, choose Transformation

enter image description here

Then In the "Input columns" pane select your date column(mine is ToDate) and make the usage type readWrite

enter image description here

Then you can modify the column within the script. Here's a helpful link to format the date: http://msdn.microsoft.com/en-us/library/8kb3ddd4%28v=vs.110%29.aspx

/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
///  string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
///  Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    /*
     * Add your code here
     */
    Row.ToDate = Row.ToDate; //Here you format your date.
}
TsSkTo
  • 1,412
  • 13
  • 25
  • I am getting this error "String was not recognized as a valid DateTime" the code is `Dim datestring, format As String Dim provider As CultureInfo = CultureInfo.InvariantCulture datestring = "10-31-2013 23:59:59" format = "MM-dd-yyyy hh:mm:ss" Row.Date1 = Date.ParseExact(datestring, format, provider)` – Chow Nov 27 '13 at 17:20
  • Yep. Actually I got permissions to modify the datatype of field.changed to varchar. And it worked with just derived column transformation. – Chow Nov 29 '13 at 18:41