There seems to be several different data types in SSIS that represent date and/or time. Is there a direct relationship between T-SQL date types and SSIS date types? Is any one of the SSIS date type preferred over the others?
asked
SSIS Date Formats
MS has a fairly complete listing of SSIS data types and how they map to data types in SQL Server and other database systems (Oracle, DB2, and Jet).
Here are some of the SSIS date data types that map directly to SQL Server date data types
DT-DBDATE -> date
DT-DBTIME2 -> time(p)
DT-DBTIMESTAMP -> datetime, smalldatetime
DT-DBTIMESTAMP2 -> datetime2
DT-DBTIMESTAMPOFFSET -> datetimeoffset(p)
I don't know that a particular SSIS date data type would generally be preferred over the others. I would say to prefer the SSIS data type that best represents the dates and/or times you are working with and that most closely matches the SQL Server data type you are trying to represent.
Does SQLL2008 SSIS really support DT_DBTIME2? I can only find DT_DBTIME as a data flow type, DT_DBTIME2 only exists as a cast type in the derived column component, but can't be used outside of it, for example in a SQL Server Desitination.
