BIDS Helper Non-Bug: If Statements

During BIML Part 6, I had a segment of BIML code which produces what looks like a somewhat silly output. What follows is a simplified version of the issue.

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<#
	string metadataConnectionString ="Provider=SQLNCLI10;Server=LOCALHOST;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;";
	DataTable tables = ExternalDataAccess.GetDataTable(metadataConnectionString, "SELECT 1 AS SomeValue, 2 AS SomeOtherValue");
	 #>
	<PackageProjects>
		<PackageProject Name="TestProject" ProtectionLevel="DontSaveSensitive">
			<Packages>
				<Package PackageName="TestPackage" />
			</Packages>
		</PackageProject>
	</PackageProjects>
	<Packages>
		<Package Name="TestPackage" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
			<Tasks>
				<# foreach (DataRow row in tables.Rows) {
					if (Convert.ToInt32(row[0]) == 1 && Convert.ToInt32(row[1]) == 2) { #>
				<Container Name="Delete From New Tables" ConstraintMode="Linear">
				</Container>
				<#	} 
				} #>
			</Tasks>
		</Package>
	</Packages>
</Biml>

Looking at this code, you can see that we generate two values: 1 and 2. If you create a BIML script and check for errors in BIDS Helper, everything looks fine. But let’s make one small change: instead of selecting the values 1 and 2, we’ll select the values 1 and 0. Changing the SQL value we return should be just fine, and we go to generate the package when…

The element ‘Tasks’ in namespace ‘http://schemas.varigence.com/biml.xsd’ has incomplete content. List of possible elements expected: (and so on).

The cause of this problem is that the Tasks tag must have something inside it. If you try to create a package with an empty set of tasks, you’ll get that error. In our case, the BIML generating the SSIS package realizes there’s a package with an empty task set, and pops up that error message.

There are a few ways that you can fix this. The easiest method is to add a dummy container. That’s what I ended up doing in my BIML series because it was an easy way to solve the problem. The downside is that you could end up with several dummy containers wasting space—in my case, I ended up with 5 dummy containers.

Alternatively, you can change your query to pre-filter. In this case, if we have a filter on SomeValue and SomeOtherValue such that we don’t get any rows back if there isn’t a case in which SomeValue = 1 and SomeOtherValue = 2, then we can put an if block in and check that if tables.Rows has zero values, don’t even build the Tasks section.