CREATE PROCEDURE dbo.usp_populate_SSISDataTypes
AS
SET NOCOUNT ON ;
WITH SSISDataTypes
AS (SELECT [DataTypeId],
[SSISDataType],
[DataTypeName],
[SQLCompatibleDataType],
[SQLCompatibleDataType1],
[SQLCompatibleDataType2],
[SQLCompatibleDataType3],
[SQLCompatibleDataType4],
[Description]
FROM ( VALUES ( 2, 'DT_I2', 'two-byte signed integer', 'smallint', 'smallint', NULL, NULL, NULL, 'A two-byte, signed integer.'),
( 3, 'DT_I4', 'four-byte signed integer', 'int', 'int', NULL, NULL, NULL, 'A four-byte, signed integer.'),
( 4, 'DT_R4', 'float', 'real', 'real', NULL, NULL, NULL, 'A single-precision floating-point value.'),
( 5, 'DT_R8', 'double-precision float', 'float', 'float', NULL, NULL, NULL, 'A double-precision floating-point value.'),
( 6, 'DT_CY', 'currency', 'money', 'smallmoney', 'money', NULL, NULL, 'A currency value. This data type is an eight-byte signed integer with a scale of 4 and a maximum precision of 19 digits.'),
( 7, 'DT_DATE', 'date', 'datetime', NULL, NULL, NULL, NULL, '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.'),
( 11, 'DT_BOOL', 'Boolean', 'bit', 'bit', NULL, NULL, NULL, 'A Boolean value.'),
( 14, 'DT_DECIMAL', 'decimal', 'numeric(30,4)', NULL, NULL, NULL, NULL, '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.'),
( 16, 'DT_I1', 'single-byte signed integer', 'smallint', NULL, NULL, NULL, NULL, 'A one-byte, signed integer.'),
( 17, 'DT_UI1', 'single-byte unsigned integer', 'tinyint', 'tinyint', NULL, NULL, NULL, 'A one-byte, unsigned integer.'),
( 18, 'DT_UI2', 'two-byte unsigned integer', 'int', NULL, NULL, NULL, NULL, 'A two-byte, unsigned integer.'),
( 19, 'DT_UI4', 'four-byte unsigned integer', 'bigint', NULL, NULL, NULL, NULL, 'A four-byte, unsigned integer.'),
( 20, 'DT_I8', 'eight-byte signed integer', 'bigint', 'bigint', NULL, NULL, NULL, 'An eight-byte, signed integer.'),
( 21, 'DT_UI8', 'eight-byte unsigned integer', 'bigint', NULL, NULL, NULL, NULL, 'An eight-byte, unsigned integer.'),
( 64, 'DT_FILETIME', 'file timestamp', NULL, NULL, NULL, NULL, NULL, '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.'),
( 72, 'DT_GUID', 'unique identifier', 'uniqueidentifier', 'uniqueidentifier', NULL, NULL, NULL, 'A globally unique identifier (GUID).'),
( 128, 'DT_BYTES', 'byte stream', 'varbinary', 'binary', 'varbinary', 'timestamp', NULL, 'A binary data value. The length is variable and the maximum length is 8000 bytes.'),
( 129, 'DT_STR', 'string', 'varchar([max_length])', 'char', 'varchar', NULL, NULL, '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.)'),
( 130, 'DT_WSTR', 'Unicode string', 'nvarchar([max_length])', 'nchar', 'nvarchar', 'sql_variant', 'xml', '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.)'),
( 131, 'DT_NUMERIC', 'numeric', 'numeric([precision], [scale])', 'decimal', 'numeric', NULL, NULL, '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.'),
( 133, 'DT_DBDATE', 'database date', 'date', 'date', NULL, NULL, NULL, 'A date structure that consists of year, month, and day.'),
( 134, 'DT_DBTIME', 'database time', 'time', NULL, NULL, NULL, NULL, 'A time structure that consists of hour, minute, and second.'),
( 135, 'DT_DBTIMESTAMP', 'database timestamp', 'datetime', 'datetime', 'smalldatetime', NULL, NULL, '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.'),
( 145, 'DT_DBTIME2', 'database time with precision', 'time([precision])', 'time(p)', NULL, NULL, NULL, 'A time structure that consists of hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 7 digits.'),
( 146, 'DT_DBTIMESTAMPOFFSET', 'database timestamp with timezone', 'datetimeoffset([precision])', 'datetimeoffset(p)', NULL, NULL, NULL, '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.'),
( 301, 'DT_IMAGE', 'image', 'image', 'image', NULL, NULL, NULL, 'A binary value with a maximum size of 231-1 (2,147,483,647) bytes.'),
( 302, 'DT_TEXT', 'text stream', 'text', 'text', NULL, NULL, NULL, 'An ANSI/MBCS character string with a maximum length of 231-1 (2,147,483,647) characters.'),
( 303, 'DT_NTEXT', 'Unicode text stream', 'ntext', 'ntext', NULL, NULL, NULL, 'A Unicode character string with a maximum length of 230 - 1 (1,073,741,823) characters.'),
( 304, 'DT_DBTIMESTAMP2', 'database timestamp with precision', 'datetime2([precision])', 'datetime2', NULL, NULL, NULL, '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.') )
AS SSISDataTypes ([DataTypeId], [SSISDataType], [DataTypeName], [SQLCompatibleDataType], [SQLCompatibleDataType1], [SQLCompatibleDataType2], [SQLCompatibleDataType3], [SQLCompatibleDataType4], [Description]))
MERGE [dbo].[SSISDataTypes] AS t
USING SSISDataTypes AS s
ON t.[DataTypeId] = s.[DataTypeId]
WHEN MATCHED AND (s.[SSISDataType] <> t.[SSISDataType]
OR s.[DataTypeName] <> t.[DataTypeName]
OR (s.[SQLCompatibleDataType] IS NULL
AND t.[SQLCompatibleDataType] IS NOT NULL)
OR (s.[SQLCompatibleDataType] IS NOT NULL
AND t.[SQLCompatibleDataType] IS NULL)
OR s.[SQLCompatibleDataType] <> t.[SQLCompatibleDataType]
OR (s.[SQLCompatibleDataType1] IS NULL
AND t.[SQLCompatibleDataType1] IS NOT NULL)
OR (s.[SQLCompatibleDataType1] IS NOT NULL
AND t.[SQLCompatibleDataType1] IS NULL)
OR s.[SQLCompatibleDataType1] <> t.[SQLCompatibleDataType1]
OR (s.[SQLCompatibleDataType2] IS NULL
AND t.[SQLCompatibleDataType2] IS NOT NULL)
OR (s.[SQLCompatibleDataType2] IS NOT NULL
AND t.[SQLCompatibleDataType2] IS NULL)
OR s.[SQLCompatibleDataType2] <> t.[SQLCompatibleDataType2]
OR (s.[SQLCompatibleDataType3] IS NULL
AND t.[SQLCompatibleDataType3] IS NOT NULL)
OR (s.[SQLCompatibleDataType3] IS NOT NULL
AND t.[SQLCompatibleDataType3] IS NULL)
OR s.[SQLCompatibleDataType3] <> t.[SQLCompatibleDataType3]
OR (s.[SQLCompatibleDataType4] IS NULL
AND t.[SQLCompatibleDataType4] IS NOT NULL)
OR (s.[SQLCompatibleDataType4] IS NOT NULL
AND t.[SQLCompatibleDataType4] IS NULL)
OR s.[SQLCompatibleDataType4] <> t.[SQLCompatibleDataType4]
OR s.[Description] <> t.[Description])
THEN
UPDATE SET
t.[SSISDataType] = s.[SSISDataType],
t.[DataTypeName] = s.[DataTypeName],
t.[SQLCompatibleDataType] = s.[SQLCompatibleDataType],
t.[SQLCompatibleDataType1] = s.[SQLCompatibleDataType1],
t.[SQLCompatibleDataType2] = s.[SQLCompatibleDataType2],
t.[SQLCompatibleDataType3] = s.[SQLCompatibleDataType3],
t.[SQLCompatibleDataType4] = s.[SQLCompatibleDataType4],
t.[Description] = s.[Description]
WHEN NOT MATCHED
THEN
INSERT ([DataTypeId],
[SSISDataType],
[DataTypeName],
[SQLCompatibleDataType],
[SQLCompatibleDataType1],
[SQLCompatibleDataType2],
[SQLCompatibleDataType3],
[SQLCompatibleDataType4],
[Description])
VALUES
(s.[DataTypeId],
s.[SSISDataType],
s.[DataTypeName],
s.[SQLCompatibleDataType],
s.[SQLCompatibleDataType1],
s.[SQLCompatibleDataType2],
s.[SQLCompatibleDataType3],
s.[SQLCompatibleDataType4],
s.[Description]) ;