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&#8217; 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.

The Hall Countdown

Sports on Earth (which has taken a serious quality hit of late but is still quite good) has a very nice article on the ticking clock some MLBers face with regards to the HOF, based purely off past voting histories.

The article’s surprisingly depressing on a couple of Hall cases. Jeff Bagwell is project to go into the HOF, but the authors thinks Bags might have a delay until 2020. That’s based largely on the assumption that three-man classes will be rare, and that a four-man class will be totally unheard of. I think even the HOF voters will have to accept three-man classes for a few years just to clear the logjam.

I also think Piazza sails in next year; I wouldn’t even put a “worst case” scenario then. It’ll depend on if Biggio gets in, but I think he will.