How to create BIML custom task or component

Introduction

BIML or Business Intelligence Markup Language is a nice technology that lets you generate SSIS packages using a bit of scripting and parameters.  Check for more info if you are new to it:

• https://en.wikipedia.org/wiki/Business_Intelligence_Markup_Language
• http://bimlscript.com/

In this article we will cover the points how to create BIML custom task or component. For this purpose we will use ZappySys PowerPack Task or Data Flow component as an example. At the end of this article you will find sample BIML files you can download and fiddle around.

Use BIML to create SSIS packages that use PowerPack tasks or components

BIML Elements

BIML uses CustomTask, CustomComponent, and CustomSsisConnection elements to create custom SSIS connectors. In each of them you will need to specify CreationName or a similar attribute/element, which defines what kind of component you want to create. E.g. for LoggingTask it would look like this:

You will find the connector name in DTSX. For Tasks you have to specify CreationName  attribute, for components it is UserComponentTypeName. For Rest API Task it looks similar to this:

For different SQL Server SSIS package version you will need to use a different DLL name, e.g.:

• SQL Server 2008: ZappySys.PowerPack100
• SQL Server 2012: ZappySys.PowerPack110
• SQL Server 2014: ZappySys.PowerPack120
• SQL Server 2016: ZappySys.PowerPack130
• SQL Server 2017: ZappySys.PowerPack140

So to generate SQL Server 2017 SSIS package custom connector creation name becomes like this:

Create BIML custom task with CustomTask

You will need to use CustomTask element to create PowerPack Tasks in your Control Flow. So open your DTSX where you use a PowerPack Task, find out the CreationName and then copy child element in <DTS:ObjectData> element. (e.g. LoggingTask). You will need to escape it and use it in your BIML as well.

DTSX snippet

BIML snippet

A Logging Task under Tasks element will look like this:

Create BIML custom components using CustomComponent

Use CustomComponent BIML element to generate these components in DataFlow:

• Sources
• Transformations
• Destinations

If you need a connection then use CustomSsisConnection element.

Again, you will have to copy UserComponentTypeName property value and use it in BIML to initialize a component. For custom connection it is a bit different — just take CreationName attribute value from DTSX and use it in BIML element. Also, don’t forget to copy property values from DTSX and mirror them in BIML.

Below is DTSX and BIML snippets where a Data Flow contains JSON Source which uses HTTP Connection and is connected to Trash Destination.

DTSX snippet

BIML snippet

Resources

• BIML documentation
• CustomTask documentation
• CustomComponent documentation
• CustomSsisConnection documentation

Download sample BIMLs

• Sample BIML with PowerPack DataFlow Components
• Sample BIML with PowerPack DataFlow Components with Connection
• Sample BIML with PowerPack Task