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 how to create a BIML custom task or component. For this purpose, we will use the 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 the CreationName attribute; for components, it is UserComponentTypeName. For the Rest API Task, it looks similar to this:

For a 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 a SQL Server 2017 SSIS package, the custom connector creation name becomes like this:

Create a BIML custom task with CustomTask

You will need to use the 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 the Tasks element will look like this:

Create BIML custom components using CustomComponent

Use the CustomComponent BIML element to generate these components in DataFlow:

• Sources
• Transformations
• Destinations

If you need a connection, then use the CustomSsisConnection element.

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

Below are DTSX and BIML snippets showing a Data Flow that contains a JSON Source using an HTTP Connection and is connected to a 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