Teradata has a timestamp data type that appears to be equivalent to a SQL Server datetime. It is not. Attempting to use the SSIS Data Conversion task will likely fail with a truncation error.
While the solution below is extremely slow, it does work between Teradata 14.10 and SQL Server 2008 R2 and SQL Server 2012. It will still require a SSIS Data Conversion task to take the output and convert it to a DT_DBTIMESTAMP.
select CAST(CAST(<teradata_column> AS DATE FORMAT ‘YYYY-MM-DD’) AS CHAR(10)) || ‘ ‘ || TRIM(EXTRACT(HOUR FROM (<teradata_column>))) || ‘:’|| TRIM(EXTRACT(MINUTE FROM (<teradata_column>))) || ‘:’ || TRIM(CAST(EXTRACT(SECOND FROM (<teradata_column>)) AS INTEGER))