Change month number to month in SSIS
I have a source file with 50 columns. One of the columns TransDateTime
, and the value in it has a format 22-MAY-2017 02:31:15.00
. Now, before loading this source file into the final destination table, I want it to be formatted 22-05-2017 02:31:15.00
so that I can use the datatype datetime
for this column.
I have seen ways to convert the number of months to a month if the column just contains the month value as shown below.
I'm not sure how to get around this scenario in my case. Any solutions?
source to share
Use the script component. Convert the input column to the format you want, for example C#
:
DateTime.Parse("22-MAY-2017 02:31:15.00").ToString("dd-MM-yyyy HH:mm:ss.ff");
For example, if your input column is MyDate and Out column, then OutPutCol:
OutPutCol = DateTime.Parse(Row.MyDate).ToString("dd-MM-yyyy HH:mm:ss.ff");
You can check the code here .
source to share
If the data type is the destination column datetime
, you don't want to reformat the date string. but you want to convert this string to date value.
You can add a Script COmponent
dataflow with a column of type inference to a task DT_DBTIMESTAMP
and mark the TransDateTime
Column as input. and use the following code: (i used VB.NET)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Output0Buffer)
Row.OutDate = DateTime.ParseExact(Row.TransDateTime,"dd-MMM-yyyy HH:mm:ss.ff",New System.Globalization.CultureInfo("en-GB"))
End Sub
If the target column is of type string, you need to follow the same steps, but the output column must be of type DT_STR
and you need to use the following code:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Output0Buffer)
Row.OutDate = DateTime.ParseExact(Row.TransDateTime,"dd-MMM-yyyy HH:mm:ss.ff",New System.Globalization.CultureInfo("en-GB")).ToString("dd-MM-yyyy HH:mm:ss.ff")
End Sub
source to share