Connection Manager Attributes Changed error in SSIS can be resolved by cleaning up your query

Just resolved a funny issue that was making me crazy for about 15 minutes. In the "Use results of an SQL query" window in the SQL Query a Lookup task in SSIS 2005, I pasted in a query I had written in SQL Management Studio 2008 R2. The formatting looked a little funny, with the little squares in place of some line breaks and tabs all over the place, but I ignored it and clicked through to the Columns tab. At the bottom of the tab, where errors and warnings appear, I got this:

Connection Manager attributes have changed. The mappings of previous columns are no longer valid.

The message stayed there even after I changed the OLE DB connection manager, added and removed columns to the lookup, and clicked every available button in the Lookup Transformation Editor. And it prevented me from clicking OK to finalize and save the transformation. I decided that I should look through the code for any bad syntax or other anomalies, but it was difficult with the screwy formatting, so I "washed" it by cutting and pasting it into Notepad++ and then back into SSIS.

And guess what? Not only did the code look better, the warning message went away. So if you're seeing that warning, try cleaning up the formatting of your SQL query.

Python scratchpad entry

Simplest syntax I've found for converting dates to datetimes, given a date d:

datetime.combine(d, time())

I've got a couple other blog entries in the pipeline, but I've had blog apathy lately and haven't finished any of them. Hopefully soon.

FILLFACTOR is really not that complicated

I keep coming across code that explicitly sets the FILLFACTOR option on an index for no good reason. FILLFACTOR is not complicated - it leaves some percentage of index pages empty in order to prevent page splits and resulting fragmentation and low page density during inserts into the middle of a key range. It can be a useful option iff:

- the table will actually get rows inserted into it!

- there is no monotonically increasing index key (like a primary key identity column)

- the author is intimately familiar with the usage pattern of the table and knows how to balance the additional I/O required to read the extra pages with the I/O saved by preventing page splits

Otherwise you're just taking an automatic IO performance hit of n% (where n is 100-(FILLFACTOR)) for no good reason.

Particularly ridiculous is the code that I've found which does something like this:

CREATE TABLE #tempstuff (foo int, bar varchar(20))

INSERT INTO #tempstuff SELECT foo, bar FROM dbo.sometable

CREATE INDEX ix_reallyNotUseful ON #tempstuff (foo, bar) WITH FILLFACTOR = 90

First off, make the index clustered. If this table will not see any more inserts, there's no reason to leave it as a heap and then have to create surrogate keys in order to build the non-clustered index. Might as well physically order it by the index keys and gain the additional performance of the clustered index.

Second, the table is not getting any more inserts after this. Why would you care about leaving extra space on the pages? Set the FILLFACTOR to 100!

This has been a public service rant from your irritable neighbor database engineer.

Nested CTEs

Had my first need for nested CTEs today - for a table with prices by object and another with adjustment factors by object, I needed to multiply the prices by all factors occurring after the price date. This could be done in the C++ code I was working on via an ugly for loop for each price date, but I knew there had to be a better, set-based way within SQL.

The answer was nested Common Table Expressions (CTEs), which Microsoft officially says are not supported, but which can actually be implemented by defining multiple CTEs (with a comma between each) and using the first one in the second.

For this application, the first CTE orders the object ids and adjustment factors and adds row numbers. An ORDER BY would have sufficed to order everything, but in order to avoid inefficient subqueries in the next CTE, I needed consistent row numbering. The second CTE aggregates the adjustment factors for each date by recursing down the ordered factors for each object and multiplying them as it goes.

This solution condenses all the selection logic into one set of SQL statements, is compact, fairly readable, and has a better complexity profile than the for loop alternative.

(Table and column names changed to protect confidentiality.)

WITH AdjFactorsOrdered (ObjectId, AdjDate, AdjFactor, ord) AS

(SELECT ObjectId, AdjDate, AdjFactor, ROW_NUMBER() OVER (PARTITION BY ObjectId ORDER BY AdjDate desc)

FROM vwAdjFactors

WHERE AdjDate > '2009-01-01'

AND AdjDate <= '2010-01-01'

AND ISNULL(AdjFactor, 1) != 1

AND SubTypeId != 1

),

AdjFactorsCombined (ObjectId, AdjDate, AdjFactor, ord) AS

(SELECT ObjectId, AdjDate, AdjFactor, ord

FROM AdjFactorsOrdered

WHERE ord = 1

UNION ALL

SELECT mul.ObjectId, mul.AdjDate, base.AdjFactor * mul.AdjFactor as AdjFactor, mul.ord

FROM AdjFactorsCombined base

INNER JOIN AdjFactorsOrdered mul

ON mul.ord = base.ord + 1

AND mul.ObjectId = base.ObjectId

)

SELECT ObjectId, AdjDate, AdjFactor

FROM AdjFactorsCombined

ORDER BY ObjectId, AdjDate

Daily WTF: SSIS edition

With apologies to Alex P of the real Daily WTF, here's my latest legacy code annoyance:

We have a variety of data imports that employ multiple SSIS packages in order to break the import logic into manageable, consistent stages. So far, so good. Except that a few particularly intrepid former team members of mine preferred to construct parent packages that called child packages, complete with variable passing to maintain state awareness, directory location consistency, dynamic environment configuration, etc. instead of just calling them from the job management system in successive tasks.

Oh, and none of these packages are in source control, they're just scattered about the file system like leaves in an ill-kept yard.

All of this leads me to my current exercise in SSIS surgery - I needed to change the behavior of one of these packages, so I decided to update it to our modern standards of SSIS behavior and move it to a new location. This one has a final step that calls the next package, a process I wasn't quite prepared to rework, so I wanted to simply update the directory in which it expects to find the next package. "Hopefully it does it on the fly," I thought, "since all the related packages are kept in the same directory and it could just pull the working directory at runtime..."

No such luck.

Instead, the package uses a variable to hold the path of the next package, which is configured via an expression that pulls from another variable, which pulls from another variable, which pulls from a package configuration, which pulls from a registry string. Which I don't have on my machine.

Got that?

Registry Key -> Package Configuration -> Variable A -> Variable B -> Variable C -> Script Task

I've got news for you, former developer: it's called IO.Directory.GetCurrentDirectory().

Off Topic: In-Ear Monitors (aka earbuds)

Just received my latest pair of serious headphones, a set of Ultimate Ears Triple.Fi 10vis, and couldn't resist posting some thoughts.

Some background: as it says in my profile, I used to work in the live and recorded sound biz, and I'm moderately obsessed with good sound. My go-to headphones are still my Sony MDR-7506s, but the full list has included...

• AKG K240
• Etymotic ER6i
• Etymotic ER-4P
• Sennheiser CX300
• Sennheiser CX400
• Sennheiser HD650
• Sennheiser HD280
• Sony MDR-7506
• Sony MDR-V600
• Sony MDR-V700
• UE Triple.Fi 10vi

along with plenty of cheaper junk for travel, gym, etc. I use the AKGs with a Behringer UCA202 at work, the Sony MDR-7506s when listening at home or DJing, and I have been happily using the Etymotic ER4s with my iPhone 3GS, but when the UEs came up in Amazon's Gold Box for $99, I couldn't resist and picked up a pair.

I've been A/Bing the UEs and Etymotics for the last hour, and I have to say, despite the usual price difference (the UEs usually retail for about 2x as much), the comparison is not cut and dried. My first impressions are as follows, with the "winner" in each area in bold in case you don't want to read the whole thing.

Lows

The biggest known weakness of the Etymotics is the bass response, even when the flanges are shoved all the way down your ear canal and are tickling your temporal lobe, and the UEs are the clear winner here. The thump and throb of the bass on NIN's Gave Up and Zero Sum was much tighter, clearer, and more extended on the UEs, while the Etymotics gave up on clarity as the frequencies descended toward 20 Hz.

Highs

On the other end of the spectrum, the Etymotics redeemed themselves. The opening chords of Band of Horses' The Funeral gave me goosebumps, and the NIN tracks generally had more sizzle on the cymbals and more edge to the guitars and synths. The UEs sound a bit darker, despite the better frequency separation lent by the multiple drivers.

Clarity

Still, the effectiveness of the UEs' multiple drivers can be heard in the clarity of the sound. Despite the compression of the some of the MP3s I have on my iPhone, the UEs carved out more of a pocket for each instrument and frequency range. I'm sure this difference between the headphones would be more pronounced with higher-quality amplification, but I could hear, for example, the muddying effect of heavy bass on the mids in the Etymotics, but not in the UEs.

Soundstage

Somehow, even put up against the multiple drivers, the Etymotics produced a wider, more defined soundstage. Special effects like dynamic panning were more pronounced, and subtle placement of instruments within the field was clearer, especially on quieter tracks with more headroom/less compression. The better high frequency response on the Etymotics may have something to do with this perception.

Other

Because I use these headphones in the real world - on airplanes, buses, NYC subways, etc., there are some ancillary considerations that have a real effect on my headphone preference. Despite the fact that the UEs are billed as noise-isolating, the Etymotics were clearly superior at blocking outside noise, even in the relatively quiet environment of my office. The cord is a bit longer and heavier on the Etymotics, but lacks the built-in microphone of the UEs. The earbud flanges on the Etymotics take quite a bit of getting used to, whereas the UEs, while they don't fit as snugly, come with 4 different sets of tips and are immediately comfortable and feel easier to wear for long periods.

However, with the sound quality a near wash between the two, the main reason I'm switching from the Etymotics to the Ultimate Ears is the filters. The Etymotics have dirt/debris filters that fit inside the driver enclosure behind the flanged tips and must be changed every time they get clogged. They are tiny, difficult to replace, and not tremendously cheap - $15 gets you 3 sets of filters, and I have to change mine every few months at most. I consider the filter system to be the Achilles heel of the otherwise-excellent Etymotics, and I'm hoping for substantially less maintenance with the UEs. We'll see how it goes.

Notification Services doesn't like version mismatches

I was pinged today to check out a failed installation of Notification Services with a vendor app. The vendor consultant was telling us that there was a mismatch in the version of SQL Tools installed, which sounded odd to me, since generally the tools are consistent between versions and the engines are different.

I tried to start up the Notification Services instance and got a 1359 error:

---------------------------

Services

---------------------------

Could not start the NSxxxx service on Local Computer.

Error 1359: An internal error occurred.

---------------------------

OK

---------------------------

Inspecting the error log, I found this:

Description:

The Notification Services instance encountered an error in one of its components and must stop.

EventParameters:

Instance Name: xxxxx

Problem Description: The database was created with or upgraded to a different edition of Notification Services. Use the Notification Services edition that the database expects.

Notification Services Edition: Standard Edition

Database Edition: Enterprise Edition

So it turns out that the consultant was partially correct - Notification Services won't start if the version is mismatched between the engine installation and its corresponding SQL Server instance. Good to know.