Hunting Public land evokes images and stories of many orange hats grouped together shooting each other !

But when you don't have access to private land, you have to go public!  This past weekend for opening day of deer season, my buddy Dale and I went down to Clinton Missouri, around Truman Lake to hunt.  There is a lot of pulic land around Truman lake for hunting, i've read estimate of up to 75,000 acres.

The key is scouting, scouting, scouting, and lots of walking.

We ended up not doing as much scouting as we wanted, but found what we thought was a nice area with lots of potential.  We didn't see much.

There was a lot of shooting around us, but they must have been poor shots as we didn't see anyone with a deer.

Lots of "road hunters", many would drive down a path, jump out for 30 minutes and then walk back, not sure what these guys were doing; maybe they weren't seeing much either and looking for a better place.

Ended up having some of these road hunters blast 3 rounds in quick succession at some running does (there's a good idea !), and scared them right to the side of me, about 25 yards.  Unfortunately they were so spooked that I was unable to swing around 45 degrees and get a shot off, so I just got to stare at them for a bit and hope to see them another day.

I finally found some private land to hunt for no cost, we'll see how that goes next.

Don't expect your queries to be in order without a select statement, guess what same goes for SSIS!

Today I had to trouble-shoot a package (not developed by me or our SQL DBA Group), but regardless, it becomes our problem !

SSIS was exporting a table to a flat file, turns out this flat file is really an XML File.  The table is populated with XML from a stored procedure prior to the export.  The data pump ole db source was a table, turns out it's not always in order, resulting in malformed XML.  Change the source from table to a select statement with an order by clause and all is working well, fortunately there was a rowid (identity) column in the table already.

Frustruating to the end user, as it was a package that had been working fine for months.

Of course there was no primary key, making this a heap table; but either way, if the requirement is to have the data ordered, than you need an order by statement, even if the data is "in the table that way", and even for SSIS.  [Be nice if Microsoft put a warning on there that the data access mode may not be in order.]

This seems to be more of a problem on heap tables (no primay key) and Enterprise edition sql (which supports Merry-go-round scans [i think that's what it's called]).

The patching game.

Better give me a whole lotta lumps.....

Last week Microsoft released a patch for a SQL Server Security vulnerability.  This week Microsoft released SQL Server 2005 CU 6 (which includes the security patch and some other fixes).

Test those "other fixes" carefully.

We just ran into a change in behavior for how SSIS evaluated variables that are passed into Execute SQL Tasks as parameters, Not sure exactly which patch introduced the change as they are cumulative and we were running CU 2 previously, so anything from CU 3 on up could have been the cause.

We were running on version 9.00.4211, which is SQL 2K5, SP3 CU2.  We upgraded to the Security Fix (9.00.4262), and the SSIS package broke.  Hoping not to have to trouble shoot the issue and needing to be on CU 6 anyway, we applied it, hoping it would fix the issue...no luck, time to trouble shoot.

The issue is really one of poor coding practice.

There are some variables passed into an execute sql task to dynamically create a where clause. 

The variable in question was declared as an Integer

The variable was than passed into the execute sql task, but it was of a varchar type, below is the corrected parameter mapped as a numeric.

The execute sql task is shown here, the parameter is the first one evaluated with the if "? <> 2".

This execute sql task passed the parameter in is as '', blank, if the parameter mapping was not set properly.  I confirmed this behavior behind the scenes with a profiler trace as well.  Interestingly even with the parameter mapped properly as numeric, it is still passed into sql server as a varchar !  But with the parameter mapped as numeric, the varchar is properly populated with value of the variable being passed in and the statement completes successfully.

Test those SQL Server patches carefully, there is always the off chance of unintended behavior change.  Ultimately it was "sloppy coding" to be passing an integer variable in ssis, to a parameter mapped as varchar.  But it has been running that way for 14-16 months without issue.

Test carefully.

9.00.4211.00 - 2005 SP3 CU 2
9.00.4262.00 - QFE Security Fix
9.00.4266.00 - 2005 sp3 cu 6

In response on where to get sp3 cu 6:

http://support.microsoft.com/kb/974648/en-us