Made2Mentor

A “Hello World” program is an ages old traditional way to teach the basics of syntax for a programming language to a beginner. Since so many of us are just starting to use Biml Script, I thought it would be fun to start out with it. Also, I’ve made the entire solution available for download.

Before we begin, download and install BidsHelper from Codeplex.com. Then open Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT) and create a new project. We’ll call ours “HelloWorld”.

Right click on your project name and select “Add New Biml File”.

Open the Biml file and you’ll find this:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>

This is an XML declaration that specifies the XML Schema Definition (XSD). The XSD file defines “the rules” for any XML document created and validates them against those rules. Now, before we start getting into Biml code, I want everyone to read about the Copy and Paste Issues in Visual Studio. Otherwise, any code you copy from my blog (or really any source) and then paste into Visual Studio will not work.

Create a Basic SSIS Package

Since the whole point of “Hello World” is to create the most basic example in a programming language that produces output, the simplest step we can take is to create a package. So, let’s add the bare minimum necessary to create a package called “Hello World”. Begin by hitting enter at the end of the first line and typing “<". Intellisense should kick in with a list of options. You can either continue typing "Packages" or select it from the list.

As soon as you close the Packages Node, you’ll notice that the closed Packages tag appears and the cursor is placed between those tags. Type “<" again and you'll notice that Intellisense offers fewer options this time. Either type or choose "Package" (singular) and then type ">” to close that as well. You should see that your code has a blue squiggly line below “Package” which indicates that something is missing. If you hover over that, you’ll see the following:

Our code is missing a Constraint Mode (and also a Package Name) which is required. So, if we edit our code to add a Name of “Hello World” to our package and then type “ConstraintMode” the Intellisense offers the two options of Linear and Parallel. Linear will cause each Control Flow Task to be run in order, while Parallel allows them to be run concurrently and also allows you to specify the precedent constraints individually per task. For our simple example, just select Linear. Your code should now look like this:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
		<Package Name="Hello World" ConstraintMode="Linear">
		</Package>
	</Packages>
</Biml>

Right click on your Biml Script file, and select “Generate SSIS Packages.” A generic package named “Hello World.dtsx” should be created. If you attempt to open it, you’ll see that the package is empty.

Creating Tasks

So, we’ve achieved the basic premise of a “Hello World” application, but lets dig further. Let’s add the Biml code for a couple of Tasks. First, we’ll add an Execute SQL Task. Now, if you think about a simple Execute SQL Task, what elements would be required?

• Task Name
• Connection
• SQL Statement if using direct input.

So, we need to have a Connection. A Connection requires a name and a connection string, but it also requires an existing database. I’ve created an empty database called “HelloWorldDemo” on my instance. There are a number of ways to generate connection strings, but I prefer the following way because it is so simple. For your convenience I’ve recorded this quick video, but you can also follow the instructions below it.

1. Create a text file somewhere. I’m lazy so I created mine on my desktop and named it Connection Strings.txt. You may name yours whatever you wish.
2. Make sure that file extensions are not hidden on your system. Highlight the txt extension and replace it with udl. Windows will warn you that changing the extension might cause problems. Select Yes and change it anyway.
3. Double Click the file to open it. You’ll be presented with the standard Data Link interface that we’re all familiar with.
4. Configure that screen, but please select “Use Windows NT Integrated security.” You’ll see why in a moment.
5. Select the Database, in my case it’s called HelloWorldDemo, and click Test Connection. After that succeeds, select OK to close the screen.
6. Right Click the udl file and select Open With… Notepad (or some similar program). The last line of text will have your connection string.

Return to your Biml Script and create a Node called Connections just below the first line. Then place the code into it like so:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<Connection Name ="HelloWorldDemo" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HelloWorldDemo;Data Source=DAVESDEV\SQL2012" />
	</Connections>

Now that we have a connection, we can create Packages and Tasks which use it. Let’s create the Execute SQL Task mentioned above. First, we have to create a Tasks Node. Our Execute SQL Task will connect to our Connection, but won’t actually do anything.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<Connection Name ="HelloWorldDemo" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HelloWorldDemo;Data Source=DAVESDEV\SQL2012" />
	</Connections>
	<Packages>
		<Package Name="Hello World" ConstraintMode="Linear">
			<Tasks>
				<ExecuteSQL Name="SQL Hello World" ConnectionName="HelloWorldDemo">
					<DirectInput>Select 'Hello World' as GreetingMessage</DirectInput>
				</ExecuteSQL>
			</Tasks>
		</Package>
	</Packages>
</Biml>

Right click on your Biml Script and click Generate SSIS Packages. This time, since the Hello World.dtsx package already exists, you’ll receive the following window which allows you to selectively over-write packages. Obviously, in this case we want to over-write the package so select Commit. Now, if you open your package you will find a single Execute SQL Task and if you double click to open it, you will find the following:

While the package will open, those of us working in SSIS 2012 will receive an error while running it.

You will learn to loathe this.

After changing the project ProtectionLevel, go ahead and run Hello World.dtsx. The SQL Hello World task will turn green, and there is peace in the valley. Next, let’s create some tables in Biml.

Creating Tables

Step 1 – Create Definitions

Before we begin creating tables in Biml and Biml Script, let’s just define what is necessary to create the same tables using T-SQL. We’re going to create two tables so I can demonstrate how Biml Script will iterate through them. The two tables will be called “HelloWorld” and “FromMade2Mentor.” Let’s look at the T-SQL that I would write to define these.

Use HelloWorldDemo;
Go
 
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'dbo')
BEGIN
    EXEC sp_executesql N'CREATE SCHEMA dbo AUTHORIZATION dbo';    
END;
 
IF  EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'dbo' AND name = 'HelloWorld')
BEGIN
   Drop Table  dbo.HelloWorld;
END;
GO
 
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
 
Create Table dbo.HelloWorld
(
[HwId] smallint IDENTITY(1, 1) NOT NULL,
[HWChar10Field] varchar(30) NULL
);
GO
 
IF  EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'dbo' AND name = 'FromMade2Mentor')
BEGIN
   Drop Table  dbo.FromMade2Mentor;
END;
GO
 
Create Table dbo.FromMade2Mentor
(
[MentorId] smallint IDENTITY(1, 1) NOT NULL,
[MentorDate] date NULL
);
GO

While I realize that both of these are in the schema dbo and the create schema statement isn’t really necessary, I’ve left it in for two reasons. First, you’d need a statement like that to verify that a named schema like ‘Staging’ already existed before creating tables in it. Second, it illustrates how Biml script is similar to the above statement.

Creating tables in Biml is fairly easy. The important thing to remember is that you must first define the table(s) in a Biml file, and then use another Biml Script file to build the package(s) which you would then run to create them. The end result is a SSIS package with Execute SQL Tasks which create the necessary tables. So, let’s start with the Biml file which will contain the table definitions and we’ll name it Destination Table Defs.biml. As you can see in the following code the Biml follows the same basic path.

1. The Connection is declared (which isn’t necessary in SSMS but is implied).
2. The Database(s) is specified.
3. The Schema(s) is specified.
4. The Table Name is specified. (as in Create Table dbo.HelloWorld).
5. The Columns are named and defined for data type and other attributes such as Nullability and Identity.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<Connection Name ="HelloWorldDemo" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HelloWorldDemo;Data Source=DAVESDEV\SQL2012" />
	</Connections>
	<Databases>
		<Database Name="HelloWorldDemo" ConnectionName="HelloWorldDemo" ></Database>
	</Databases>
	<Schemas>
		<Schema Name ="dbo" DatabaseName="HelloWorldDemo"/>
	</Schemas>
	
	<Tables>
		<Table Name="HelloWorld"   SchemaName="HelloWorldDemo.dbo">
			<Columns>
				<Column Name="HwId" DataType="Int16" IdentityIncrement="1"></Column>
				<Column Name="HWVarChar30Field" DataType="AnsiString" Length="30" IsNullable="true"></Column>
			</Columns>
		</Table>
		<Table Name="FromMade2Mentor"   SchemaName="HelloWorldDemo.dbo">
			<Columns>
				<Column Name="MentorId" DataType="Int16" IdentityIncrement="1"></Column>
				<Column Name="MentorDate" DataType="Date" IsNullable="true"></Column>
			</Columns>
		</Table>
	</Tables>
</Biml>

Now, save your project, right click on Destination Table Defs.biml and select Check Biml for Errors. Assuming that your script passes validation right click again and select Generate SSIS Packages. What happens? Well, nothing. Why? You have provided the table definitions, but haven’t provided the mechanism to actually create them. That is our next step.

Step 2 – Generate Package(s)

Knowing what we learned earlier, we could create a static Biml file with Execute SQL Tasks to create these tables, but that’s not very efficient. Wouldn’t it be great if we could just dynamically create that package and it would create every table we’ve specified? This is your first taste, small though it is, of the power of Biml Script. Let’s go through the steps to create such a package as if we were going to do it in SSIS. We’d most likely:

1. Create a new package and name it. I’ll name it “Create Destination Tables.dtsx”.
2. We’d create and specify a Connection.
3. For each table we needed to create we’d drag out an Execute SQL Task, rename it, specify the connection, and provide the SQL query to use for Direct Input.
4. Close and save the package.

Of course, this process is tedious. It’s the same set of clicks and drags over and over again. Also, you’d need to open this package and add another Execute SQL Task every time you added a new table to the destination. Let’s create a new Biml File and name it “Create Dest Tables.biml”.Read through the following Biml Script code and you’ll see that it follows along with the process we’d take in SSIS. Paste this script into our new Biml file.

<#@ template language="C#" hostspecific="true" #>
	<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Create Destination Tables" AutoCreateConfigurationsType="None" ConstraintMode="Linear">
            <Tasks>
                <# foreach(var table in RootNode.Tables) {#>
                <ExecuteSQL Name="SQL Create <#=table.Schema #> <#=table.Name#>" ConnectionName="<#=table.Connection.Name#>">
                    <DirectInput>
                        <#=table.GetTableSql()#>
                    </DirectInput>
                </ExecuteSQL>
                <# } #>
               
            </Tasks>
        </Package>
    </Packages>
</Biml>

You may notice that Visual Studio shows you red sqiggles with your C# code. That’s because it is expecting XML only so you’ll have to learn to ignore them. Notice how all of the C# code is held inside of the pound signs. The foreach statement loops though every table defined and assigns it to the table variable I’ve declared. The embedded C# such as #=table.Name # is returning the Name property of the Table object. Therefore, I am dynamically naming my Execute SQL Tasks and correctly specifying them for each iteration. GetTableSQL is a function provided by Varigence which does just what you’d think. Now, right click Create Dest Tables.biml and check for errors. You’ll receive the following:

Basically, the problem is that the code is trying to iterate through the tables, but since we only selected the script file and not the definitions file, there were no Tasks created under the Tasks node. If you select both of the files and check for errors, this should be remedied. After passing that check, make sure that both are selected, right click and generate the package. Upon opening the package, you should see the following:

Now, double click SQL Create dbo HelloWorld. After the window opens, click the ellipses to expand the SQL Statement. After expanding the window to show the entire statement, you’ll notice that the resulting SQL query looks almost identical to the one we created above.

If you run the resulting package, the two tables we defined will now exist in the database.

Just for Fun

Typically a “Hello World” program would pop up that message to the user. While we have mimicked that with Packages and Tables “popping up,” I thought it would be fun to show you a little trick to finish this article. I borrowed this with permission from a friend of mine who just recently started blogging over on his new Biml Geek Blog.

Open our Project again, create a new Biml file, and name it. I’ve chosen to name it Hello World Message.biml. Copy and paste the following code into it.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#@ template language="C#" #>
<#@ assembly name="%WINDIR%\\Microsoft.NET\\Framework\\v2.0.50727\\System.Windows.Forms.dll" #>
<#@ import namespace="System.Windows.Forms" #>
<#MessageBox.Show("Hello World");#>
</Biml>

Remember that the scripting in this article is simply C#. Therefore, we can leverage the language to do a lot more than just iterate with For Each loops. So, one last time, right click the Biml file and select “Generate SSIS Packages” and you should see the following:

Keep on Biml-ing!