Skip to content

SQL Server, SSIS and Biml Data Types

Biml (Business Intelligence Markup Language) - SQL Server, SSIS and Biml Data TypesThe 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 ServerSSISBiml
bigintDT_I8Int64
binaryDT_BYTESBinary
bitDT_BOOLBoolean
charDT_STRAnsiStringFixedLength
dateDT_DBDATEDate
datetimeDT_DBTIMESTAMPDateTime
datetime2DT_DBTIMESTAMP2DateTime2
datetimeoffsetDT_DBTIMESTAMPOFFSETDateTimeOffset
decimalDT_NUMERICDecimal
floatDT_R8Double
geographyDT_IMAGEObject
geometryDT_IMAGEObject
hierarchyidDT_BYTESObject
image (*)DT_IMAGEBinary
intDT_I4Int32
moneyDT_CYCurrency
ncharDT_WSTRStringFixedLength
ntext (*)DT_NTEXTString
numericDT_NUMERICDecimal
nvarcharDT_WSTRString
nvarchar(max)DT_NTEXTString
realDT_R4Single
rowversionDT_BYTESBinary
smalldatetimeDT_DBTIMESTAMPDateTime
smallintDT_I2Int16
smallmoneyDT_CYCurrency
sql_variantDT_WSTRObject
text (*)DT_TEXTAnsiString
timeDT_DBTIME2Time
timestamp (*)DT_BYTESBinary
tinyintDT_UI1Byte
uniqueidentifierDT_GUIDGuid
varbinaryDT_BYTESBinary
varbinary(max)DT_IMAGEBinary
varcharDT_STRAnsiString
varchar(max)DT_TEXTAnsiString
xmlDT_NTEXTXml

(* 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.

OLE DB connection using SQL Server Native Client 11 provider (SQLNCLI11)

SQL Server, SSIS and Biml Data Types using OLEDB (SQLNCLI11) - Updated 2018 Version
Previous comparison chart from 2017 | Original comparison chart from 2014

OLE DB connection using Microsoft OLE DB Provider for SQL Server (SQLOLEDB)

SQL Server, SSIS and Biml Data Types using OLEDB (SQLOLEDB) - Updated 2018 Version
Previous comparison chart from 2017 | (Original comparison chart from 2014)

ADO.NET connection using SqlClient Data Provider

SQL Server, SSIS and Biml Data Types using ADO.NET (SqlClient) - Updated 2018 Version
Previous comparison chart from 2017 | (Original comparison chart from 2014)

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.

↓ Click to view mapping problems in previous versions of Biml

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

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Thanks!

Click to Show / Hide Comments