SQL SERVER – The Why of BIML – Notes from the Field #051

[Note from Pinal]: This is a 51th episode of Notes from the Field series. In one of the earlier blog post we discussed about BIML and lots of people asked what is actually BIML and why one should use it. If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains The WHY of BIML. Read the experience of Reeves in his own words.

The Why of Biml

If you question whether Biml can be useful, you are not alone. After I introduce people to Biml, someone always asks, “How can writing something in an XML-based language actually help me out?” I asked this same question when I first started working with Biml. If I’m going to spend the time learning something it had better improve my development experience or bring something else of value to the tool belt. This article will show you how Biml can save you time creating packages.


As a quick refresher, Biml is an XML language used to describe Business Intelligence (BI) projects. This discussion on Biml will work within the Visual Studio (BIDS/SSDT) environment with BIDS Helper. Using Biml with BIDS and the BIDS Helper add-in allows you to create Integration Services (SSIS) projects. (Note that Biml is compatible with SQL Server Analysis Services projects but not within the BIDS Helper add-in.)

Be aware that the Biml language does not support formatting and layout. The language is built to be simple and effective with an intended design to script and automate.

Example – Source Packages

Why would you need to automate SSIS if all of your packages are different? If you look at most of the packages within your enterprise, some sections of code or complete packages are very similar in design, the only changes being metadata.

Source packages are a typical example of packages that are similar. With most source packages, you do a complete pull of all of the data needed within the tables in the source systems and load that data directly into tables in a stage environment with a similar shape. Most of the time, these packages are very basic and perform a limited number of transformations. Figure 1 illustrates a common source package.

Figure 1 – Common Source Package

If you look into the details of the above package, you will notice that the metadata from package to package is not very different. The differences within the metadata of the above example would include:

SQLT – Truncate Destination Table (Execute SQL Task)

  • SQLStatement: TRUNCATE TABLE TableName

DAFL – Load Destination Table (Data Flow Task)

  • OLE DB Source: Name of the table or view: TableName
  • OLE DB Destination: Name of the table or view: TableName

(Note that these packages are not following the recommended best practices.)

Listing 1 shows what would the above example look like in Biml.

Listing 1 – Common Source Package Biml

Note that the connection strings were truncated to enable the best display for the graphic. A valid connection string with my server settings would be:

provider=SQLOLEDB.1;data source=localhost;database=AdventureWorks2012;integrated security=SSPI

So now that you’ve seen the script needed for a common source package, you may still be asking the original question: Why would you write the above Biml to create five packages?

The best answer might be a chart showing the difference between creating packages manually and creating packages with Biml and then automating them with BimlScript. The chart in Figure 3 assumes it takes about one business day to create one package. Thus one package takes one day, two packages take two days, and so on. With a Biml and BimlScript solution, you have extra development time upfront in creating the Biml code and then adding the BimlScript needed for automation. That investment makes projects with a small number of packages not ideal.

Figure 2 – Package Development Methods

After you create a Biml file, the next step would be to add BimlScript needed for automation. BimlScript is added directly to the Biml file and is able to replace items within the XML.

Practical Use

BimlScript uses C# or Visual Basic as the scripting language. If automation with C# and Visual Basic sounds like another barrier to starting a Biml project, I have another suggestion to enable you to use Biml on your next project. Having to learn both Biml and BimlScript can be an overwhelming requirement to start a Biml project. What if you could defer learning almost any C# and just focus on Biml? The following example is going to demonstrate a method to do that.

If you took the Biml code shown in Listing 1 and added the line of code in Listing 2 to the <Biml> declaration in the script in Listing 1 above, you can add a variable that you could use later in your Biml file. Let’s not worry about anything except that this code creates a variable named tableName, which you’ll use later in the file.

Listing 3 – BimlScript used to create a Variable

This line alone will not accomplish anything if you do not use it within the Biml file. A good test to make sure you declared the BimlScirpt correctly would be to add the code and run Check Biml for Errors. Regenerating a package should still work and nothing will change.

However, if you add one more line of code to four places within the Biml file, you can use and replace that value with the variable declared in Listing 3. The three places within the code are defined by the three red outlines above, and one place is within the package name to create uniquely named SSIS packages.


Listing 4 – BimlScript that will use the Variable

The final code is shown below in Listing 4.

Listing 5 – Semi-Automated Common Source Package Biml

If you need to create another package, just update the variable and regenerate the package. This will enable you to create multiple packages within minutes. It is not a perfect solution but adds a great technique on the way to learning Biml.

Note that there are cut-and-paste issues within Visual Studio that will result in extra quotation marks. These are outlined in Listing 5 below. Removing the extra quotes is all you need to do, and you are able to modify the Visual Studio development environment to alleviate this issue. That is discussed here.

Listing 6 – BimlScript Cut-and-Paste Issue

Looking Ahead

This article had touched on a lot, and I hope I’ve given you a reason to consider Biml on your next project. The article used BimlScript, and scripting is the place to focus after you learn Biml. Biml with BimlScript can provide huge savings in development effort once you’ve learned them.

In a future article I will demonstrate how to add more BimlScript to the existing code to increase package automation even further.

Stay Tuned.

Reeves Smith

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Search Records with Single Quotes – SQL in Sixty Seconds #075

Earlier I wrote two blog posts about Search Records with Single Quotes in two parts. Refer the blog posts over here.

Though I had clarified that all the methods displayed in these two blog posts have the exact same performance, I kept on getting question on this subject, again and again! Well, as there are so many questions, I have decided to create a quick video which demonstrates that there is no performance difference among the four methods which I have displayed earlier.

Action Item

Here are the blog posts I have previously written. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Search Records with Single Quotes – Part 2

Yesterday I wrote a blog post about and it has created quite a lot of interest in the community SQL SERVER – Search Records with Single Quotes. Here are a few other alternatives which I received as a comment.

The question was how to search records with single quotes in table columns.

Suggestion from Andreas Driesen

SELECT [ProductModelID]
FROM [AdventureWorks2014].[Production].[ProductModel]
WHERE [Name] LIKE '%'+CHAR(39)+'%'

Suggestion from Sanjay Monpara and Hitesh Shah

SELECT [ProductModelID]
FROM [AdventureWorks2014].[Production].[ProductModel]
WHERE CHARINDEX('''',[Name]) <> 0

Suggestion from Vinod Kumar (via email)

SELECT [ProductModelID]
FROM [AdventureWorks2014].[Production].[ProductModel]
WHERE [Name] <> REPLACE(name, '''','')

Please note that all of them gives excellent performance, so use anyone you like.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Search Records with Single Quotes

Every day when I woke up there are hundreds of emails with various questions related to SQL. I spend my first hour of the day answer each of them.

How do I search records with single quotes in it?

Here is the answer to find records with single quotes in the table.

SELECT [ProductModelID]
FROM [AdventureWorks2014].[Production].[ProductModel]
WHERE [Name] LIKE '%''%'

Do let me know if there is any other way to find records with single quotes in it.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Combined Multiple .SQL files into One Single File

Here is an interesting problem which I enjoyed solving yesterday.

There were multiple SQL Files in my one folder. When I had to send it to my friend I had to collect all the files into a folder and zip it to send it via email. My friend who was using mobile device told me that it is difficult to see the content of the file on a mobile device so it would be nice if I can just send all the SQL files combined in one single file and send it to him.

I loved the idea, but the challenge was that there are over 100s of the files were there and combining them into a single file would be a manual task for hours. I did not like the solution and I went online to find some cool solution. After a while I found a solution that I can use wiht command prompt to combine multiple files into a single file and it is easy to do so.

Here is an example. Let us create three SQL Files.

Now we will execute the following command in command prompt which will combine all the three files into a single file.

The command is

type *.sql > OneFile.sql

Above command will combine all the three files into a single called OneFile.sql.

Following image displays the content of the OneFile.sql which is the concatenation of all the sql files in the folder.

I enjoyed solving this little problem.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

EXCEL / SQL SERVER – Extract the Domain from an Email Address

Just like any business person, I work with Excel pretty much half of my time when I am not working with SQL Server. Recently I faced challenges when I wanted to extract domain from the email address. I was using excel. I was in extreme rush so I did something which was in fact actually longer route than easier way. I laughed at myself and decided to blog about it.

Here is the way I took to extract domain out of email in excel.

  1. Imported Excel to SQL Server Table
  2. Executed script from my blog post to extract domain from email in SQL Server
  3. Exported SQL Server Table to Excel

In reality, I should just have written a new function in different columns of excel which can extract domain out of Excel.

Here is the function which will extract domain from the email address.


Following is the image which displays how above simple excel function can return the domain from the email address.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Finding Max Value From Multiple Values

Here is a question which I have received a few days ago.

“I have three different variables, I want to find out which one of them has the maximum or highest value. How can I do that?

I know I can do this with the IF or CASE statement, but that makes me write a long chode and I have to manually implement logic.

Is there any other way? “

Absolutely, here is the simple example which will work with SQL Server 2008 and later versions.

Example 1: With Variables

DECLARE @Int1 INT = 1, @Int2 INT = 3, @Int3 INT = 5;
FROM (VALUES (@Int1), (@Int2), (@Int3)) AS value(v);

Example 2: With Static Values

FROM (VALUES (1),(5),(3)) AS value(v);

Example 3: With Columns

Int1 INT,
Int2 INT,
Int3 INT);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (1, 1, 2, 3);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (2, 3, 2, 1);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (3, 1, 3, 2);
-- Query to select maximum value
FROM (VALUES (Int1), (Int2), (Int3)) AS value(v)) AS MaxValue
FROM SampleTable;

I hope this simple queries helps you to find maximum value from various variables.

Reference: Pinal Dave (http://blog.SQLAuthority.com)