The Basics of Biml – the Execute SQL Task

A couple years ago, I wrote a series of guest posts about the Basics of SSIS for my friend Pinal Dave’s [Blog | @pinaldave] popular blog, SQL Authority. In that series, I pointed out that I use only a handful of SSIS Control Flow tasks when designing SSIS packages. You can find the series here:

In this post, I’m going to demonstrate and discuss building the Execute SQL Task using Biml.

SQL SERVER - The Basics of the SSIS Execute SQL Task - Notes from the Field #044 ssise1

“So how does one build an Execute SQL Task using Biml, Andy?” I’m glad you asked. First you have to build an SSIS package in Biml. But before you build an SSIS package in Biml, you need to populate the Biml Relational Hierarchy. To play along at home grab the 14-day free trial of Mist here and download the World Wide Importers sample database here. Then go learn how to populate the Biml Relational Hierarchy by reading this post: The Basics of Biml – Populating the Biml Relational Hierarchy. You’ll be glad you did.

The Prerequisites

I’m going to continue building out the demo Mist project named BimlBasics – the one I started in The Basics of Biml – Populating the Biml Relational Hierarchy. (I told you you’d be glad you read that post!)

Create an SSIS Package

You can create an SSIS package using the Mist GUI. But for this exercise, let’s build one from the ground up using Biml. In Logical View, right-click Library—>Utilities and then click Add BimlScript:

BimlBasics_ExecSql_2

I named my BimlScript file “TruncateAndLoad_People.biml”:

BimlBasics_ExecSql_3

If I double-click the file in Logical View, the BimlScript Input Editor displays my file contents:

BimlBasics_ExecSql_4

If you position your cursor at the end of line 1 and press the Enter key a new, properly-indented line begins. Type “<” to begin adding Biml to your file. Note the IntelliSense provides a list of options available at this level of the Biml object model:

BimlBasics_ExecSql_3

Double-click Packages to begin creating a Packages collection:

BimlBasics_ExecSql_4

Type the “close tag” symbol – “>” – and Biml automagically generates the close tag: </Packages>:

BimlBasics_ExecSql_5

Position your cursor between the opening and closing Packages tags and press the Enter key. A new properly-indented line appears:

BimlBasics_ExecSql_6

Type the “open tag” symbol (“<”) and IntelliSense provides a list of valid Biml objects. Type of click Package to proceed:

BimlBasics_ExecSql_7

Type a space at the end of the word “Package” followed by the letter “N.” Note the Name attribute is highlighted by IntelliSense:

BimlBasics_ExecSql_8

Enter “Package1” as the Name attribute value:

BimlBasics_ExecSql_9

Close the Package tag:

BimlBasics_ExecSql_10

Press Enter to start a new, properly-indented line. Use IntelliSense (or type) to add a “<Tasks>” tag:

BimlBasics_ExecSql_11

Inside the Tasks tag, add an “<ExecuteSQL>” tag:

BimlBasics_ExecSql_12

Add the ConnectionName attribute:

BimlBasics_ExecSql_13

Note IntelliSense supplies valid values – WorldWideImporters, in this case – to the attribute:

BimlBasics_ExecSql_14

Supply a Name attribute for the Execute SQL Task and close the tag:

BimlBasics_ExecSql_15

Inside the ExecuteSQL tag, next a DirectInput tag:

BimlBasics_ExecSql_16

In Logical View, right-click the Application.People table, hover over Copy SQL Script, and then click Select SQL:

BimlBasics_ExecSql_17

Position your cursor between the DirectInput tags and paste the clipboard contents:

BimlBasics_ExecSql_18

Look at all that well-formatted T-SQL! How neat is that?

BimlBasics_ExecSql_19

We’ve been ignoring this message at the top of the BimlScript Input Editor for a while now:

BimlBasics_ExecSql_20

Click it.

Observe the Preview Expanded BimlScript window:

BimlBasics_ExecSql_21

That right there is your Package1.dtsx Biml.

Next Steps

You do not yet have an SSIS package. You have the Biml template of a package. I can hear you thinking, “So how does this become an SSIS package, Andy?” I’m glad you asked!

We now need to execute the BimlScript and then build the output.

Execute the BimlScript

First, we execute the BimlScript file. In Logical View, right-click the TruncateAndLoad_People.biml file (found in Library—>Utilities) and click Execute BimlScript:

BimlBasics_ExecSql_22

The Biml template of an SSIS package has now been executed. It generated the Biml version of an SSIS package. In the image below, I positioned the Logical View and Project View side-by-side for comparison:

BimlBasics_ExecSql_23

We now have a Biml representation of the SSIS package named Package1.dtsx.

Build the Package

To generate the actual SSIS package, right-click Package1 (found in Logical View—>Integration Services—>Packages) and then click Build:

 

BimlBasics_ExecSql_24

The Output window displays the results of the Build operation:

BimlBasics_ExecSql_25

An SSIS project is generated in the output folder of the BimlBasics Mist project folder:

BimlBasics_ExecSql_26

The package was generated just as we specified:

BimlBasics_ExecSQL_27

And it runs!

BimlBasics_ExecSql_28

Conclusion

In this post, I discussed and demonstrated how to configure an SSIS package that contains an Execute SQL Task, one of the handful of tasks I use when designing SSIS packages.

:{>

Related Training:
IESSIS2: Immersion Event on Advanced SQL Server Integration Services

Learn More:

Biml Academy
The Basics of Biml – Populating the Biml Relational Hierarchy
Stairway to Biml
Stairway to Integration Services
Varigence.com
BimlScript.com
SQL Server Central

Need help or training implementing a Biml solution?
Contact Enterprise Data & Analytics today!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.