Giving Thanks To SSMS Tools

Over the next few days, I want to get in the spirit of Thanksgiving by pointing out a few tools upon which I depend.  I have at least one SQL Server Management Studio window open throughout the course of my work day, and these tools make me a lot more productive by eliminating some of the busy work aspects of my job.

I have three tools in mind, so this series will run through the weekend.  Your regularly-scheduled BIML post will run on Monday.

The Last of Us

This isn’t a video game review. The Last of Us is a PS3 game (which is being remastered for PS4) by Naughty Dog. It is, superficially, the same general combat system as Uncharted, so if you liked Uncharted’s combat, you’ll like The Last of Us.

But you may not like the story. I’m having a very difficult time coming to grips with it myself. Perhaps a slight digression will help.

I’ve also recently finished (and platinumed) Assassin’s Creed: Rogue. It’s the first in the series in which you play as a Templar, not an Assassin. Yet, although you’re one of the “bad guys”, it doesn’t feel like it. The Templars might be an evil organization, but Shay Patrick Cormac gets involved in it for the right reasons and even tries to make it better. It’s a fresh look at the AC plotline, and I highly recommend it.

Joel, on the other hand…

I’m somewhat conflicted about revealing the ending here, especially if you haven’t played the game. It’s the most powerful ending I’ve ever seen in a video game. I’ll point you towards YouTube if you don’t own a PS3.

What I can say about The Last of Us is this: I’m not sure I could play through it again, knowing how the game ends. It isn’t because it’s a bad game — it’s actually very good — or because the storyline isn’t gripping — it’s brilliant — or that the ending doesn’t make sense — it does. It almost makes too much sense.

I’m going to watch The League now (which is hilarious, if you haven’t seen it). I might eventually do a proper review of the Last of Us, but not for a while. I think I need to let it simmer for a while.

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.

BIDSHelper Bug: NoMatch Issue With Visual Studio 2013

BIDS Helper is a fantastic tool, but it’s not perfect.  If you’re using Visual Studio 2013, there is a bug in BIDS Helper that would prevent you from implementing lookups with NoMatch criteria like I used in BIML Part 6.

Here’s a quick-and-easy BIML package which fails:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection Name="Instance" ConnectionString="Data Source=LOCALHOST;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    </Connections>
    <Packages>
        <Package Name="NoMatchError" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
            <Connections>
                <Connection ConnectionName="Instance"></Connection>
            </Connections>
            <Tasks>
                <Container Name="Test NoMatch Error" ConstraintMode="Parallel">
                    <Tasks>
                        <Dataflow Name="NoMatch DataFlow">
                            <Transformations>
                                <OleDbSource Name="Source" ConnectionName="Instance">
                                    <DirectInput>
                                        SELECT name FROM master.sys.tables;
                                    </DirectInput>
                                </OleDbSource>
                                <Lookup Name="Lookup" OleDbConnectionName="Instance" CacheMode="Full" NoMatchBehavior="RedirectRowsToNoMatchOutput">
                                    <DirectInput>
                                        SELECT name FROM master.sys.tables;
                                    </DirectInput>
                                    <Inputs>
                                        <Column SourceColumn="name" TargetColumn="name"></Column>
                                    </Inputs>
                                    <Outputs></Outputs>
                                </Lookup>
                                <OleDbDestination Name="Destination" ConnectionName="Instance">
                                    <InputPath OutputPathName="Lookup.NoMatch"></InputPath>
                                    <ExternalTableOutput Table="sys.tables"></ExternalTableOutput>
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

The error message that you get from this is Destination Input references output NoMatchError.Test NoMatch Error.NoMatch DataFlow.Lookup.NoMatch, which is not found in the existing outputs in the data flow.

To try to get around this, you might set to read . This lets you generate the BIML package, but then you get an error in the SSIS package itself which reads: “Error at NoMatch DataFlow [Lookup [19]]: The Lookup.Outputs[Lookup Match Output] has an invalid error or truncation row disposition.”

On the Advanced Editor, the Component Properties tab shows that NoMatch behavior is wrong: it reads “Treat rows with no matching entries as errors” rather than “Send rows with no matching entries to the no match output.” This is a sign that BIDS Helper is either not setting no-match behavior or is setting it to error behavior.

Incidentally, changing Lookup.NoMatch to Lookup.Error on line 39 and NoMatchBehavior="RedirectRowsToErrorOutput" on line 20 generates a package which redirects failed lookups to the Error output and successfully hooks that Error output to the destination.

Please vote on the issue and help stamp out this bug. If you need to do use this pattern in the meantime, I’ve confirmed that it works with Visual Studio 2012, and you can also use Mist if you’ve purchased a copy.

It’s Hall of Fame season again!

Let’s Go Tribe has an excellent breakdown of the new 2015 HOF Ballot. They have both the first timers and the guys who have been on the ballot for a while. Jay Jaffe’s breakdown is even more detailed and awesome, because he’s Jay Jaffe and is rapidly becoming my favorite baseball writer.

The inner asshole in me would love to see Biggio held off for a second year, maybe missing by one vote because somebody spells his name wrong or something. Randy Johnson is the slam dunk candidate in this class; Pedro is only slightly less slam-dunkier because of the brevity of his career, but it’s probably going to be his year too. Biggio probably gets in, too. However, I think that’s it for 2015’s ballot. Piazza’s got an outside chance, and I expect John Smoltz will make a nice showing his first year on the ballot, but it’s hard to see anybody apart from that making a serious impact.

Reducing maximum eligibility from 15 years to 10 years isn’t a horrible move, but they really need to increase the number of ballot slots, maybe to 15. I’ll probably be covering this until ballots are released, so stay tuned for updates.