DECLARE @SQL VARCHAR(MAX) = 'DT_BOOL A Boolean value.$DT_BYTES A binary data value. The length is variable and the maximum length is 8000 bytes.$DT_CY A currency value. This data type is an eight-byte signed integer with a scale of 4 and a maximum precision of 19 digits.$DT_DATE A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a fixed scale of 7 digits.
The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE.
On the other hand, DT_DBTIMESTAMP is represented by a structure that internally has individual fields for year, month, day, hours, minutes, seconds, and milliseconds. This data type has larger limits on ranges of the dates it can present.$DT_DBDATE A date structure that consists of year, month, and day.$DT_DBTIME A time structure that consists of hour, minute, and second.$DT_DBTIME2 A time structure that consists of hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.$DT_DBTIMESTAMP A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 3 digits.$DT_DBTIMESTAMP2 A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.$DT_DBTIMESTAMPOFFSET A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.
Unlike the DT_DBTIMESTAMP and DT_DBTIMESTAMP2 data types, the DT_DBTIMESTAMPOFFSET data type has a time zone offset. This offset specifies the number of hours and minutes that the time is offset from the Coordinated Universal Time (UTC). The time zone offset is used by the system to obtain the local time.
The time zone offset must include a sign, plus or minus, to indicate whether the offset is added or subtracted from the UTC. The valid number of hours offset is between -14 and +14. The sign for the minute offset depends on the sign for the hour offset:
If the sign of the hour offset is negative, the minute offset must be negative or zero.
If the sign for the hour offset is positive, the minute offset must be positive or zero.
If the sign for the hour offset is zero, the minute offset can be any value from negative 0.59 to positive 0.59.$DT_DECIMAL An exact numeric value with a fixed precision and a fixed scale. This data type is a 12-byte unsigned integer with a separate sign, a scale of 0 to 28, and a maximum precision of 28.$DT_FILETIME A 64-bit value that represents the number of 100-nanosecond intervals since January 1, 1601. The fractional seconds have a maximum scale of 3 digits.$DT_GUID A globally unique identifier (GUID).$DT_I1 A one-byte, signed integer.$DT_I2 A two-byte, signed integer.$DT_I4 A four-byte, signed integer.$DT_I8 An eight-byte, signed integer.$DT_NUMERIC An exact numeric value with a fixed precision and scale. This data type is a 16-byte unsigned integer with a separate sign, a scale of 0 - 38, and a maximum precision of 38.$DT_R4 A single-precision floating-point value.$DT_R8 A double-precision floating-point value.$DT_STR A null-terminated ANSI/MBCS character string with a maximum length of 8000 characters. (If a column value contains additional null terminators, the string will be truncated at the occurrence of the first null.)$DT_UI1 A one-byte, unsigned integer.$DT_UI2 A two-byte, unsigned integer.$DT_UI4 A four-byte, unsigned integer.$DT_UI8 An eight-byte, unsigned integer.$DT_WSTR A null-terminated Unicode character string with a maximum length of 4000 characters. (If a column value contains additional null terminators, the string will be truncated at the occurrence of the first null.)$DT_IMAGE A binary value with a maximum size of 231-1 (2,147,483,647) bytes.$DT_NTEXT A Unicode character string with a maximum length of 230 - 1 (1,073,741,823) characters.$DT_TEXT An ANSI/MBCS character string with a maximum length of 231-1 (2,147,483,647) characters.'
DECLARE @SQL2 VARCHAR(MAX) = 'DT_BOOL bit
DT_BYTES binary varbinary timestamp
DT_CY smallmoney money
DT_DATE
DT_DBDATE date
DT_DBTIME
DT_DBTIME2 time(p)
DT_DBTIMESTAMP datetime smalldatetime
DT_DBTIMESTAMP2 datetime2
DT_DBTIMESTAMPOFFSET datetimeoffset(p)
DT_DECIMAL
DT_FILETIME
DT_GUID uniqueidentifier
DT_I1
DT_I2 smallint
DT_I4 int
DT_I8 bigint
DT_NUMERIC decimal numeric
DT_R4 real
DT_R8 float
DT_STR char varchar
DT_UI1 tinyint
DT_UI2
DT_UI4
DT_UI8
DT_WSTR nchar nvarchar sql_variant xml
DT_IMAGE image
DT_NTEXT ntext
DT_TEXT text' ;
WITH a AS (SELECT C1 AS SSISDataType,
NULLIF(C2, '') AS SQLCompatibleDataType1,
NULLIF(C3, '') AS SQLCompatibleDataType2,
NULLIF(C4, '') AS SQLCompatibleDataType3,
NULLIF(C5, '') AS SQLCompatibleDataType4
FROM UTIL.DBO.Parsedelimitedtablecolumns32(@sql2, ' ', '
')), b AS (SELECT C1 AS SSISDataType,
C2 AS Description
FROM UTIL.DBO.Parsedelimitedtablecolumns32(@sql, ' ', '$')),
c AS (SELECT [DataTypeId],
SSISDataType,
[DataTypeName]
FROM ( VALUES ( 11, 'DT_BOOL', 'Boolean'), ( 128, 'DT_BYTES', 'byte stream'), ( 6, 'DT_CY', 'currency'), ( 133, 'DT_DBDATE', 'database date'),
( 134, 'DT_DBTIME', 'database time'), ( 145, 'DT_DBTIME2', 'database time with precision'),
( 135, 'DT_DBTIMESTAMP', 'database timestamp'), ( 304, 'DT_DBTIMESTAMP2', 'database timestamp with precision'),
( 146, 'DT_DBTIMESTAMPOFFSET', 'database timestamp with timezone'), ( 7, 'DT_DATE', 'date'), ( 14, 'DT_DECIMAL', 'decimal'),
( 5, 'DT_R8', 'double-precision float'), ( 20, 'DT_I8', 'eight-byte signed integer'), ( 21, 'DT_UI8', 'eight-byte unsigned integer'),
( 64, 'DT_FILETIME', 'file timestamp'), ( 4, 'DT_R4', 'float'), ( 3, 'DT_I4', 'four-byte signed integer'),
( 19, 'DT_UI4', 'four-byte unsigned integer'), ( 301, 'DT_IMAGE', 'image'), ( 131, 'DT_NUMERIC', 'numeric'),
( 16, 'DT_I1', 'single-byte signed integer'), ( 17, 'DT_UI1', 'single-byte unsigned integer'), ( 129, 'DT_STR', 'string'),
( 302, 'DT_TEXT', 'text stream'), ( 2, 'DT_I2', 'two-byte signed integer'), ( 18, 'DT_UI2', 'two-byte unsigned integer'),
( 130, 'DT_WSTR', 'Unicode string'), ( 303, 'DT_NTEXT', 'Unicode text stream'), ( 72, 'DT_GUID', 'unique identifier') ) AS t3 ([DataTypeId], SSISDataType, [DataTypeName]))
SELECT c.DataTypeId,
a.SSISDataType,
c.DataTypeName,
a.SQLCompatibleDataType1,
a.SQLCompatibleDataType2,
a.SQLCompatibleDataType3,
a.SQLCompatibleDataType4,
b.Description
FROM a
FULL OUTER JOIN b ON a.SSISDataType = b.SSISDataType
FULL OUTER JOIN c ON c.SSISDataType = a.SSISDataType