Last night I saw upon the stair
A little man who wasn’t there
He wasn’t there again today
Oh, how I wish he’d go away
--from Antigonish by Hughes Mearns
Recently, I’ve been assisting a client in moving their data mart databases and SSIS packages to a new server. Prior to decommissioning the old server, I was comparing package execution results, and came across an anomaly as exposed by our standard audit infrastructure tables. In reviewing the package execution table, it appeared that several of the packages were being logged more than once (same package guid and execution guid but different execution keys).
I could tell by reviewing the SQL Agent job history that each of these packages only executed once, and the rows in the package execution table were not completely identical – the columns that are normally updated at the end of the execution were null for one of the rows in each duplcate set. Hmmmmm…. Obviously, something goofy was happening in the package, related to the instantiation and update of the package execution record.
When I opened the packages in question, I saw that the Control Flow pane included the three normal audit infrastructure sequence containers that are part of all our packages. However, I noticed something. Two of the infrastructure sequence containers had the numeral one appended to the container name.
Usually, this means that the SSIS developer has copied and pasted an object with a name that already exists in the package context. BIDS appends sequential numerals to objects in these cases, to make sure the object names are unique. I searched the Control Flow pane, but could find no duplicates for the containers. Sure, it was possible that the developer who created the package had copied from an open package and hadn’t renamed the objects in his destination package, but I had the evidence of the package execution table screaming at me that the code was firing more than once. Also, I wasn’t able to remove the trailing numerals from the container names without BIDS throwing an error, so SSIS still believed that the containers existed within the package.
Invisible code. Oh, yeah. We hate that.
I was reduced to observing what was right in front of my nose – the Package Explorer tab. I’m embarrassed to say that in over five years of working with SSIS, I have never looked at that tab before. However, the Package Explorer tab does not hold a grudge – even though I’d ignored it all this time, it helped me out anyway.
When I expanded the Executables node, I could see that the two infrastructure containers were indeed duplicated. By right-clicking on the ones that were not displayed on the Control Flow tab (the items without the trailing numeral), I was able to delete them. I was then able to rename the visible containers, removing the trailing numeral without error.
Subscribe