Skip to content

Tag: Biml

Package Configurations and Connection Managers in Biml

Biml Wheel.

At work we wanted to start using Biml to speed up development in our existing projects without making too many changes at once. I decided to start by writing Biml files that generates SSIS packages exactly like the ones we already have so we can implement changes faster, and step one was to figure out how to create Package Configurations and Connection Managers in Biml:

  • Create an XML configuration file Package Configuration
  • Create the Connection Manager specified in the XML configuration file
  • Create SQL Server Package Configurations that use the Connection Manager specified in the XML configuration file
  • Create Connection Managers specified in the [SSIS Configurations] table in SQL Server
  • Create a Parent package variable

I recommend reading Understanding Integration Services Package Configurations by Rafael Salas (@RafSalas) and Getting Started with BimlScript by Paul S. Waters (@pswaters) if you’re not already familiar with the basics of SSIS Package Configurations and Biml.

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