Post reply

convert Datetype(in flat file) to SQL datetime using derived column in SSIS

  • March 15, 2011 at 9:35 pm

    #235166

    Hi All,

    I am fairly new to SSIS and want to get the datetime using derived column from flatfile date

    flat file date type is : Jan 7 2011 11:07:49:000AM

    covert into datetime using derived column.

    Please help for the conversion expression required to build in for derived column to achieve the solution.

    Thanks in Advance

    Parul

  • March 16, 2011 at 12:20 pm

    #1299570

    It may be possible with expressions, but my limited knowledge of them has me imaging an extermely ugly and complex one.

    So, instead of using Derived Column you can do this with a Transformation Script Component. Here is my VB.net code. Once you run your data through this you can use a Data Conversion component to convert the column to one of the datetime types.

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim dt As DateTime

    Dim provider As System.Globalization.CultureInfo = System.Globalization.CultureInfo.InvariantCulture

    Dim rowData As String = Row.Column0

    Dim format As String = "MMM d yyyy hh:mm:ss:ffftt" ' format matches example in original post

    dt = DateTime.ParseExact(rowData, format, provider)

    rowData = dt.ToString("yyyy-MM-dd HH:mm:ss.fff") ' SSIS datetime format

    Row.Column0 = rowData

    End Sub

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply