SQL Server, SSIS and Biml Data Types

This post was first published on May 27th, 2014, and was last updated on July 16th, 2017.

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

(Pssst, if you want to link to this post, you can use this prettier link: https://www.cathrinewilhelmsen.net/BimlDataTypes. Thanks!)

There are some problems mapping certain data types automatically between SQL Server, SSIS and Biml. Different providers and Biml methods will produce different data mapping results. I have written about the problems and differences I have encountered below.

All columns are sortable:

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.)

Different providers and Biml methods will produce different data mapping results

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

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

For each of these providers, I have compared three Biml methods for importing metadata from SQL Server: GetDatabaseSchema(), ImportDB() and ImportTableNodes(). For each of these Biml methods, I have compared the output of TableNodes.GetBiml() and GetDropAndCreateDdl():

OLE DB connection using SQL Server Native Client 11.1 provider (SQLNCLI11)
SQL Server, SSIS and Biml Data Types using OLE DB (SQLNCLI11.1) - Updated 2017 Version
(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 OLE DB (SQLOLEDB) - Updated 2017 Version
(Original comparison chart from 2014)

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

Based on these comparisons, I recommend using an OLE DB connection manager, the SQL Server Native Client provider (SQLNCLI11.1) and the GetDatabaseSchema() Biml method.

If you are using an OLE DB connection manager, the SQL Server Native Client provider (SQLNCLI11.1) 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

The following data types were fixed in the July 2017 Biml release, when Varigence fixed the issue where custom types from SQL Server were not being imported correctly:

  • 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))

Problems with certain data types

There are some problems mapping certain data types automatically between SQL Server, SSIS and Biml. I have experienced the following problems in SSIS packages generated from Biml when using an OLE DB connection manager, the SQL Server Native Client provider (SQLNCLI11.1) and the GetDatabaseSchema() method:

geography / geometry
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. 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.”

hierarchyid
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. 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.”

image
Note: The image data type will be removed in a future version of SQL Server. Use varbinary(max) instead, it is handled properly in Biml and SSIS.

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. 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.”

sql_variant
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.”

Fixed problems with certain data types

rowversion / timestamp
Fixed in July 2017 Biml release! :)
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. 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.”

smalldatetime
Fixed in July 2017 Biml release! :)
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. 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.”

smallmoney
Fixed in July 2017 Biml release! :)
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. 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.”

References

Matija Lah (@MatijaLah) wrote 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) created a SSIS to SQL Server Data Type Translations conversion chart that I frequently used while learning SSIS.

SQL Server Data Types: Data Types (Transact-SQL)
SSIS Data Types: Integration Services Data Types
Biml Data Types: DbType Enumeration

Who is Cathrine Wilhelmsen?

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

18 thoughts on “SQL Server, SSIS and Biml Data Types”

Thanks! That was helpful.

Thanks! Very helpful, especially for the case sensitive BIML stuff!

Pingback: SQL Server, SSIS and Biml data types | Clint Huijbers' Blog

Awesome – definitely on going into my invaluable favourites list.

I for hierarchy data types https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73794d31-4055-4e45-8d92-61b5cf52ae95/copying-a-table-with-a-hierarchyid-column

Spatial data types seem to be unsupported by SSIS :( gonna have a go with image to see what happens or maybe binary.

Pingback: Automate Your SSIS Design Patterns for Fater Development | Sam Vanga

Hi there Cathrine,

I get the following problem when using .GetDropAndCreateDdl() in a ExecuteSQL task ( DirectInput) to create the tables before wanting to do a dataflow between source and destination.

[Execute SQL Task] Error: Executing the query ”
————————————————-…”
failed with the following error:
“The operation failed because an index or statistics with name ‘Account_Dimension_DWH_Account_DWH_Partitioning_Key_PK’ already exists on table ‘dbo.Account_Dimension’.”.
Possible failure reasons:
Problems with the query,
“ResultSet” property not set correctly,
parameters not set correctly,
or connection not established correctly.

This seems to happen because the .GetDropAndCreateDdl() method includes the Primary Key as a Constraint in the table create, but then includes it in the creating of indexes part of the GetDropAndCreateDdl() output into the SQL task.

Any ideas perhaps if there is another method that I can use to easily create the tables beforehand ?

Trying to use another post you did here : https://www.cathrinewilhelmsen.net/2014/12/08/biml-extension-methods-importtablenodes-and-importdb/ to copy certain tables from Production to DEV.

Would appreciate any help you might be able to give, can also send full DDL as generated from BIML in email to you.

Kind regards,
Hendrik

I know this is a pretty old post, but hope this might help other users:

I guess you are not excluding foreign keys when you read your source tables. If you take a look at this page:
http://bimlscript.com/walkthrough/Details/3121
and look at 1-2-CreateTableMetadata.biml (how it connects to the database), you will note that it excludes foreign keys like this:

var importResult = sourceConnection.ImportDB(“”, “”, ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews)

I the script 1-x-DeployTargetTables.biml you will see how they also use the GetDropAndCreateDdl command.

Hope this will help others in the same situation.

Great post Cathrine!. I have this link bookmarked!

I am facing similar issue for DT_CY where the two inputs to the Union All transformation is DT_CY but the output is NUMERIC 34. Did you manage to find any work around to this bug?

Thanks.

Excellent work!
It do helps a lot.

Thanks very much.

Hi Cathrine,

I am trying to modifying the output column datatype of oledb source as SSIS is getting wrong datatype from vertica. I am trying to do exactly same function as we right click on oledb source and choose “show advance editior” and then “input output properties” and then select “output” and select the column and change datatype to WSTR from NTEXT for the selected column.

please let me know how can we define output column in BIML for oledb source. I could not find any example on google for this.

also if anyone knows about any reference where we can find all the possible attributes and sub nodes we can define in BIML … it would be great help as its hard to find examples for BIML a reference manual would be highly appreciated.

THanks,
Furrukh Baig

Hello Catherine,

Thank you very much for your excellent article!

Is it me or does the BIML interpreter have trouble with the SQL datatype Time(7) (I guess, any time with precision) ? What would the BIML datatype for that be?

In SSIS it is mapped as (DT_DBTIME2,7).

I’m having this same problem at the moment.

I too am having lots of problems with the union all component mapping DT_DBTIMESTAMP and DT_DBTIMESTAMP2 to DT_TEXT and DT_NTEXT respectively. I basically can’t use a union all in the dataflow if I have any timestamps.

Thank you so much. I was pulling my hair with data mapping between BIML and SSIS data types while trying to load Flat Files. I used “Xml” for text & ntext sql types, and the generated packages now WORK. I got the idea for the workaround after studyng the SSIS & BIML data type mapping in this article.

These tables and connection provider comparisons are invaluable. Thank you very much.

Pingback: Clint Huijbers' Blog

Hi Cathrine,

Thank you so much for this, it is a great mapping/summary and has saved me so much of trouble throughout my journey,

What you do for the community is simply awesome

Much appreciated,

Kind regards

Chris

Share Your Thoughts?