SSIS Derived Column Expression for Converting Unix int Epoch Dates to datetime

 
For source systems that use milliseconds since the beginning of the “Unix Epoch” for dates (some Unix, Java etc.), I was having some trouble converting them in SQL Server Integration Services with a Derived Column.  The goal, as always, is to end up with a usable datetime data type rather than a (still useful for humans but a pain for systems), string.
The expression I came up with is below, there may be a much more elegant way to do this, please feel free to correct me:

SSIS Expression:
DATEADD("DD", ((DT_I8)(<YOURUNIXDATECOLUMN>/1000/60/60/24)), (DT_DBTIMESTAMP)((DT_WSTR, 25)("1970-01-01 00:00:00.000")))

For good measure, here it is in PL/SQL and T-SQL

PL/SQL

SELECT TO_DATE(
        (
        TO_DATE ('1970-01-01 00:00','YYYY-MM-DD HH24:MI') + 
        (YOURUNIXDATECOLUMN/1000/60/60/24) 
        )
    ,'YYYY-MM-DD HH24:MI')AS DATEFROMUNIX

FROM YOURTABLE 

T-SQL 

T-SQL's DATEADD makes it easy to work with the date without having to convert the number.

Add Milliseconds directly

SELECT DATEADD(ms, (YOURUNIXDATECOLUMN), '1970-01-01 00:00:00') AS DATEFROMUNIX FROM YOURTABLE  

SELECT DATEADD(DD, (YOURUNIXDATECOLUMN/1000/60/60/24), '1970-01-01 00:00:00') AS WholeDaysDATEFROMUNIX FROM YOURTABLE 

If you convert a number you know should be relatively recent and it doesn't budge from 1970, it's probably in seconds.

Mapping SSIS Data Conversion Transform Data Types to SQL Data Types

 

SQL Server Integration Services Data Conversion Transform Data Types don’t match up to SQL Server Data Types, which makes sense since SSIS isn’t just SQL to SQL.

I found this MSDN article really helpful, albeit, noting the caveat stated on the MSDN page: “These mappings are not intended to represent strict equivalency”

Pulled from the above article, my top 8 are below.

• int – DT_I4
• datetime – DT_DBTIMESTAMP (I don’t get it either)
• nvarchar – DT_WSTR (give length separately)
• varchar – DT_STR (give length separately)
• datetime2 – DT_DBTIMESTAMP2 (use for Oracle DATE/TIMESTAMP compatibility, dates less than 1753 etc)
• bit – DT_BOOL
• float – DT_R8
• bigint – DT_I8

Can’t Believe I Didn’t Know This One

I’m endlessly frustrated by working with date and datetime types in T-SQL and PL/SQL, so I often end up complicating myself into a corner working with them. 

I’m running the risk of another “duh” comment by posting something so simple, but the syntax in T-SQL and PL/SQL to get the date of a year, or any number of days from today’s date is just:

T-SQL:  SELECT GETDATE() - 365;

PL/SQL: SELECT TO_DATE(CURRENT_DATE) - 365 FROM DUAL