SQL Server, SSIS and Biml Data Types

May 27, 2014

December 2020

Biml

Biml, SQL Server, SSIS

6 min

Biml Wheel.

The table below is a simplified mapping between SQL Server, SSIS and Biml Data Types. It does not include all possible mappings or all data types, but is meant as a quick reference while developing and learning Biml. I mainly created it as a cheat sheet for myself, but I hope other Biml developers will find it useful!

Can't remember this post's URL? Neither can I 😅 You can use this short URL instead: cathrinew.net/BimlDataTypes

Cheat Sheet for SQL Server, SSIS and Biml Data Types

SQL Server	SSIS	Biml
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
nvarchar(max)	DT_NTEXT	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
varbinary(max)	DT_IMAGE	Binary
varchar	DT_STR	AnsiString
varchar(max)	DT_TEXT	AnsiString
xml	DT_NTEXT	Xml

(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects.)

Comparing Providers and Biml Methods

Different providers and Biml methods will produce different data mapping results. I have written about the problems and differences I have encountered in the next section.

I have compared three providers and how they map data types from SQL Server to SSIS via Biml:

OLE DB connection using SQL Server Native Client 11 Provider (SQLNCLI11)
OLE DB connection using Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
ADO.NET connection using SqlClient Data Provider

(I will test and compare the now undeprecated Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) once it is fully supported by Biml.)

For each of these providers, I have compared two Biml methods for importing metadata from SQL Server:

GetDatabaseSchema()
ImportDB()

For each of these Biml methods, I have compared the output of:

TableNodes.GetBiml()
GetDropAndCreateDdl()

Based on these comparisons, I recommend using an OLE DB connection manager, the SQL Server Native Client provider (SQLNCLI11) and the GetDatabaseSchema() Biml method for importing metadata. This combination produces the most correct results.

Mapping Problems and Differences

If you are using the recommended OLE DB connection manager, the SQL Server Native Client provider (SQLNCLI11) and the GetDatabaseSchema() method, it is important to know that GetDropAndCreateDdl() creates SQL scripts with different data types than the original SQL Server tables:

binary → varbinary
numeric → decimal
rowversion → timestamp

Please also note that the data type sql_variant is not fully supported in SSIS. This causes the warning “The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated.” This is expected behavior in SSIS and cannot be fixed in Biml.

There were several other mapping problems in previous versions of Biml. If you are still using BIDS Helper, BI Developer Extensions or BimlExpress 2017, I strongly recommend that you update to BimlExpress 2018 where most of these problems were fixed.

Varigence fixed the following OLE DB data type mappings in the July 2017 Biml release:

geography (previously → sql_variant)
geometry (previously → sql_variant)
hierarchyid (previously → sql_variant)
image (previously → varbinary(16))
ntext (previously → nvarchar(16))
smalldatetime (previously → datetime)
smallmoney (previously → money)
text (previously → varchar(16))
timestamp (previously → varbinary(8))

Varigence fixed the following SSIS OLE DB source and destination data types in the July 2018 Biml release:

geography
geometry
hierarchyid
image

geography / geometry (Fixed July 2018)

The OLE DB Source in the generated SSIS package will use the correct data type DT_IMAGE for a geography or geometry External Column, but the incorrect data type DT_NTEXT for the Output Column. You will get the warning “The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated.”

hierarchyid (Fixed July 2018)

The OLE DB Source in the generated SSIS package will use the correct data type DT_BYTES for a hierarchyid External Column, but the incorrect data type DT_NTEXT for the Output Column. You will get the warning “The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated.”

image (Fixed July 2018)

The OLE DB Source in the generated SSIS package will use the correct data type DT_IMAGE for an image External Column, but the incorrect data type DT_NTEXT for the Output Column. You will get the warning “The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated.”

rowversion / timestamp (Fixed July 2017)

The OLE DB Source in the generated SSIS package will use the correct data type DT_BYTES for a rowversion or timestamp External Column, but the incorrect data type DT_NTEXT for the Output Column. You will get the warning “The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated.”

smalldatetime (Fixed July 2017)

The OLE DB Source in the generated SSIS package will use the correct data type DT_DBTIMESTAMP for a smalldatetime External Column, but the incorrect data type DT_NTEXT for the Output Column. You will get the warning “The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated.”

smallmoney (Fixed July 2017)

The OLE DB Source in the generated SSIS package will use the correct data type DT_CY for a smallmoney External Column, but the incorrect data type DT_NTEXT for the Output Column. You will get the warning “The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated.”

References

Matija Lah (@MatijaLah) has written a great and thorough post about SQL Server Integration Services Data Types. I recommend reading this article for a better understanding of data types and type systems in SSIS.

Devin Knight (@knight_devin) has created a SSIS to SQL Server Data Type Translations conversion chart that I frequently referenced while learning SSIS.

Microsoft Documentation

SQL Server: Data Types (Transact-SQL)
SSIS: Integration Services Data Types
.NET: DbType Enumeration
Mapping: SQL Server to .NET Data Type Mappings

About the Author

Professional headshot of Cathrine Wilhelmsen. Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓