SQL Table/CTE for SQL Server, SSIS and Biml data types

Cathrine Wilhelmsen posted an easy lookup table for the data types within SQL Server, SSIS and Biml.

For loading the meta-data information based on source tables, I find it handy to provide all three data types in the result set of the query. So here’s an easy Common Table Expression / Table Value Constructor which might help you also.


WITH DataTypeMatrix (DataTypeSQL, DataTypeSSIS, DataTypeBiml)
AS
(
SELECT *
FROM
(
VALUES
('bigint','DT_I8','Int64')
,('binary','DT_BYTES','Binary')
,('bit','DT_BOOL','Boolean')
,('char','DT_STR','AnsiStringFixedLength')
,('date','DT_DBDATE','Date')
,('datetime','DT_DBTIMESTAMP','DateTime')
,('datetime2','DT_DBTIMESTAMP2','DateTime2')
,('datetimeoffset','DT_DBTIMESTAMPOFFSET','DateTimeOffset')
,('decimal','DT_NUMERIC','Decimal')
,('float','DT_R8','Double')
,('geography','DT_IMAGE','Object')
,('geometry','DT_IMAGE','Object')
,('hierarchyid','DT_BYTES','Object')
,('image','DT_IMAGE','Binary')
,('int','DT_I4','Int32')
,('money','DT_CY','Currency')
,('nchar','DT_WSTR','StringFixedLength')
,('ntext','DT_NTEXT','String')
,('numeric','DT_NUMERIC','Decimal')
,('nvarchar','DT_WSTR','String')
,('real','DT_R4','Single')
,('rowversion','DT_BYTES','Binary')
,('smalldatetime','DT_DBTIMESTAMP','DateTime')
,('smallint','DT_I2','Int16')
,('smallmoney','DT_CY','Currency')
,('sql_variant','DT_WSTR','Object')
,('text','DT_TEXT','AnsiString')
,('time','DT_DBTIME2','Time')
,('timestamp','DT_BYTES','Binary')
,('tinyint','DT_UI1','Byte')
,('uniqueidentifier','DT_GUID','Guid')
,('varbinary','DT_BYTES','Binary')
,('varchar','DT_STR','AnsiString')
,('xml','DT_NTEXT','Xml')
) AS F (DataTypeSQL, DataTypeSSIS, DataTypeBiml)
)

SELECT *
FROM DataTypeMatrix




Need to re-format the T-SQL code? Use the Notepad++ plugin ‘Poor Man’s T-Sql Formatter

 


 


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s