SSIS: The module containing "%1" cannot be located, even though it is registered.

Last weekend our SQLServer 2005 server was migrated to a new domain. On the Monday morning, all our scheduled SISS packages started failing.

The errors indicated that all the transform tasks within the package were failing with a "The module containing "%1" cannot be located, even though it is registered." error. The only thing that had changed between Friday and Monday was the AD migration. A quick test of some of the packages confirmed that they ran on my local machine but not on the server.

Initially I suspected permissioning problem. I logged in using the new domain account of SQLAgent and used DTSRunUI to test one of the failing packages. The package failed with the same error as the scheduled job. I then logged in using the old SQLAgent account and re-ran the package. It failed with the same error. OK then, maybe it wasn't permissioning after all. I opened the dtsx file in a text editor and looked for one of the transform components:

<component id="97" name="Data Conversion 1" componentClassID="{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}" description="Data Conversion" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Data Conversion;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">

I copied the class ID and did a search in the registry.

I checked the path and discovered that the PipelineComponents directory was missing. A quick search found it in a nearby directory. With all the messing around on the server it looks like a slip of the mouse had moved the directory. Once restored, everything started working again.

In hindsight the problem was simple but it took some time for me to look beyond permissions and pick the physical package apart. Once I did that the problem was solved in minutes.

Lesson learned!

MSMQ Private versus Public queues

It's time again to change AD domains and migrate users and servers. From previous experience this has always been a painful process. At the moment I'm looking at MSMQ. In the past I've created queues as public. My reasoning being that public queues are more accessible than private queues and having them visible on AD is a good thing, right?

Well, no. Given the appropriate permissions you can access private queues from remote machines as readily as public queues. The main differentiators between public and private is that public queues are published on the AD domain, are browsable and can use MQ routing. All my applications use point to point connections and I don't MQ routing at this point. By not using these features, public queues have become more of a negative than a positive, especially with this domain move imminent.

The moral of the story is unless you have specific need of routing or managing the queues through AD, private queues are the way to go.

This technet article provides a more in depth description of the differences.

MTU pain with PATS TOF API

For the past few months I've been testing the TOF API against the PATS GT system. By and large the testing has been uneventful except for one persistent problem...

Symptoms

There are two connections to the ASP, one through a leased line and the other over the internet. Since day one there have been issues with the leased line. The TOF would connect and authenticate but then sit idle. What should happen is that all contract data should be downloaded, followed by any orders and fills.

Cause

Eventually we determined that the MTU size was being exceeded. The handshaking process worked because of the small packet size but when the contract download started, the process stopped as the packets were being dropped by the router.

The provider commissioned a Layer 3 MPLS solution. The underlying carrier allowed a maximum MTU size of 1500 bytes but this was reduced by the VPN overhead to 1478 bytes. ICMP was blocked by the firewall so fragmentation was not possible. The end result was that any packets over 1478 bytes were lost.

Solution

Three solutions presented themselves:

• Use a fixed MTU size.
• Enable ICMP between the hosts so a smaller MTU size can be negotiated.
• Change to a Layer 2 Private Leased Line.

In the end, option 1 was implemented for the short term but option 3 being the target long term solution. It will be interesting to see if this eventuates.