SQL processes "sleeping"; SCD in endless Loop

Topics: General, Issues 3: Executing the Component (Run-Time)
Mar 15, 2012 at 3:36 PM
Edited Mar 15, 2012 at 3:44 PM


I have a baffling problem on one of the Windows Server 2008 R2 installations of an SSIS package which contains the latest download of the SCD component. I do not see this problem on my Windows 7 development box or on the Windows Server 2008 R2 QA and UAT installations.

Here is the symptom on the problem box--

The SQL Agent job executes the SSIS package from the database. The package runs through validation, pre-execute--then executes the sql tasks that gather the source and existing dim data. So far, so good. It appears that when the SCD task is ready to do the merge, it hangs.  this causes all the sql spids associated with running job/ssis package to fall into a "sleep" state--waiting on some action.  The job eventually has to be terminated. No errors are recorded in the SSIS logs, SQL Server or the Windows Event logger.

On other servers, this package runs without fail.

I've tried running the package via dtexec in a command window on the problem box with the same results; i've tried running the job under an ssis proxy account using my login (I'm in the dba/windows admin role--so a goddess across multiple servers, including the problem child) with the same results. 

Now for the kicker--I can successfully run this package from the integrated services object connection.

On all the other server (and development)  installations, the package runs in all 3 ways; through the command line, through sql agent job and through  the integrated services object connection.

I've looked at so many things--the assemblies, the versions of the framework, the permission settings on the directories where the component dll lives, my permissions, the service account permissions, windows firewall, etc. with no obvious culprit(s).

Can anyone direct me on what the likely problem source might be? For example, does the SCD component use any existing helper DLLs in the \dts\pipeline directory path that may not be present, or available? Why would only execution from integration services succeed?

All suggestions, think-throughs, questions, etc. will be very gratefully accepted!:-)