Skip to content

SQL Server, SSIS and Biml Data Types

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!

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.

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

Comparison table showing SQL Server, SSIS and Biml Data Types using OLEDB (SQLNCLI11)

Previous comparison chart from 2017 | Original comparison chart from 2014

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

Comparison table showing SQL Server, SSIS and Biml Data Types using OLEDB (SQLOLEDB)

Previous comparison chart from 2017 | (Original comparison chart from 2014)

ADO.NET connection using SqlClient Data Provider

Comparison table showing SQL Server, SSIS and Biml Data Types using ADO.NET (SqlClient)

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.

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

Share or Comment?

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 🤓