Kirk Haselden

SQL Server 2005 Shipped!!!

This morning at 9 AM in shiproom, we did official signoffs for SQL Server 2005!

As Paul Flessner went around the room to get signoffs from everyone, it gave me great pleasure and an enormous sense of closure to give the all go for Integration Services.

There is a lot of blood, sweat and tears rolled up into this release! We're enormously proud of what we've accomplished and excited to finally be delivering it to customers.

Thanks to all of you for your support, guiding input and for making the product better through beta testing it.

This is a memorable day!

K

New IS Article...

Jay Nathan wrote an interesting article on Fuzzy components in Integration Services.

It's a good read. Check it out.

http://msdn.microsoft.com/msdnmag/issues/05/09/SQLServer2005/default.aspx

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

PackageFormatVersion

Recently I received an email from a partner that has a newer package generated with the September CTP and is attempting to open it with an older build (Beta 2).

First of all, I don't know why someone would want to do this, although I can extrapolate some valid reasons for it. If you find yourself in this situation and you attempt to open a newer package in an older build, you will get an error that says "The version of the component is not compatible etc."

Now, before I proceed, I must tell you that what I'm about to describe is not supported by Microsoft. It's just a little tip you may try to salvage newer packages on older builds.

The PackageFormatVersion is saved in a package near the top of the package file contents. It tells the runtime what version of the package persistence code wrote the package. As of this writing there are only two versions. Version 1 and Version 2. Version 2 came about when we made some changes to the persistence of the dataflow task and ForEach loop. We use the version to detect when (In what persistence code era) the package was saved so that we can upgrade the package seemlessly. Funny, but, it's rather difficult to know now what changes we'll do in a few years and there will be features in future versions about which we know nothing now. So, going backwards is much more problematic than going forward, (For persistence, of course. Some may note that rehashing the past is no problem for their better half).

If you are getting this error when you attempt to load a package created with a newer version of IS into an older version, you may try the following after making a backup copy of the package.

Edit the package using notepad or your favorite editor such as VS. Find the node. Change the version from 2 to 1. Attempt to reload in older version. One of three things will happen.

1) The package will load up just fine without any problems.

2) The package will load up partially, but with some items missing and some errors. You'll have to fix the errors.

3) The package will blow up and destroy your computer.

Don't say I didn't warn you. If you try this, you do it at your own risk. #3 is a bit of an exaggeration. Likely the worst thing that will happen is the package will fail to load. The point is, you do this at your own peril. Please don't tell me about times that you tried this and failed. Actually, don't tell me about it at all, unless it was a smashing success of course. You've been warned. :)

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

SQL Task parameters

Folks have been asking about this a lot, so here is a quick reference:

Connection Managers and Substitution Parameters for Queries

Connection Manager Type	Parameter Marker	Parameter Name	Example Query	Example Parameter Name
OLEDB	?	0 1 …	SELECT Name FROM People WHERE Name LIKE ?	0
ADO.Net	@Varname	@Varname	SELECT Name FROM people WHERE Name LIKE @Name	@Name
ADO	?	@Param1 @Param2 …	SELECT Name From People WHERE Name LIKE ?	@Param1
ODBC	?	1 2 …	SELECT Name FROM People WHERE Name Like ?	1

[Update : This grid can now be found on SQLIS.com]

[Update : You might also consider using Property Expressions for building the query instead of replacement parameters. Users have reported that it is simpler]

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

PASS 2005

Well, I wanted to give a blow by blow summary of PASS as it was happening, but most every night, around midnight or later, I'd open my laptop in my hotel room to comment on the events of the day, only to succumb to sleep.

PASS was a great experience for me. It was good to finally meet up with some of my virtual friends that I had known only via email and the blogosphere. I was also fortunate enough to renew friendships with some folks I'd known for years. It was great to meet some folks for the first time as well.

Other than the fact that I was coming down with some sort of nasty bug, the lack of sleep and the failing demos, my talks went pretty well. The one I was the most nervous about, "The one minute custom task from scratch", went off without a hitch with a little help from one of my new friends, Gentry Bieker.

For those who have emailed me with additional requests or questions, I'll try to get back to them as soon as possible. I'm trying to catch up with my day job now. Isn't it funny how you can get a month behind when you're only gone for a week?

Thanks,

 Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden