Search code examples
sql-serverdatetimessisunix-timestampderived-column

How to convert Datetime to Timestamp in SSIS

I'm new in SSIS. I'm importing an Excel Sheet to Transform and record in another Data Source. In the Transform process I need to convert some imported DateTime to their timestamp value

I always find Unix TimeStamp to DateTime in google search.

I'm using a derived column component.

I've tried this three expressions without any success:

DATEADD("ss",0,[DateToConvert])

DATEDIFF("ss",(DT_DBTIME)"1970-01-01",[DateToConvert])

(DT_DBTIMESTAMP)[DateToConvert]`

I Expect the output of 1571097600 for 15/10/2019

Thanks in advance

Solution

  • You are almost there, try the following expression, convert to DT_DATE or DT_DBTIMESTAMP instead of DT_DBTIME:

    DATEDIFF("ss",(DT_DATE)"1970-01-01",[DateToConvert])

    OR

    DATEDIFF("ss",(DT_DBTIMESTAMP)"1970-01-01",[DateToConvert])