SSIS Packages executed from Visual Studio Business Intelligence Development Studio - BIDS [Design time] on a 64bit development server goes to unresponsive state during package execution.

First published on MSDN on May 21, 2014
Through the use of this blog, I would like to address a common scenario that you would run into when executing a complex SSIS package from within the BIDS environment in a x64 bit development server, where the visual studio- BIDS environment goes to a hanged state during the package execution.

When I say a COMPLEX – SSIS package, what I really mean is a SSIS Package that contains a couple of blocking transformations. In simpler terms, a blocking transformation is a one which must read and process all input records before creating any output records and thus has a great impact on the performance of a SSIS package execution. A Sort / Aggregate/ Fuzzy Lookup transformations etc. are good examples of a blocking transformation. When you execute these packages, you often see some temporary files been created / deleted on your physical drives. This is what we call as Buffer spooling. Now what could cause a buffer to swap? There are two possible causes. The first one is when a memory allocation fails. The second one is when Windows signals the low memory resource notification event. Both will trigger SSIS to reduce its working set. SSIS does so by moving buffer data to disk. You can track the package execution using the SSIS Performance Counters named Buffers spooled. When Buffers spooled > 0 it means that the memory is swapped to disk and this is a good indication that your SSIS packages execution is going to get delayed.

Now consider that you are developing / running these packages on a 64bit machine using the Visual Studio – Business Intelligence Development Studio [BIDS]. The SSIS-debug runtime process name that executes the package for you is DTSDEBUGHOST.EXE. Typically when you want your SSIS packages to get executed using the 64bit Runtime, you go to Project à right-click Properties [Project property Pages] à select the Debugging pane and change the Debug Options property Run64BitRuntime to “True”. This will execute your SSIS packages under the project to the run under the 64bit SSIS-Debug runtime calling the 64bit DtsDebugHost.exe. More details available under 64 bit Considerations for Integration Services: http://technet.microsoft.com/en-us/library/ms141766(v=sql.105).aspx



Note: The Run64BitRuntime project property applies only at design time.

But what if your complex SSIS package hangs in the middle of execution, you encounter buffer spooling and your SSIS package takes a very long time to complete or never completes even after hours of waiting time. It might be any SSIS package that was working fine in your other machine or your colleague’s machine. When you bring-up the Windows Task Manager on your x64bit machine, you notice something different than what you were expecting, you see that the DTDebugHost.exe *32 [32bit version of SSIS Debug Runtime] is been called and not a 64bit DTDebugHost.exe. Also notice that the DTSDebugHost.exe*32 memory consumed is somewhere around 2.5-3.0 Gigs and it is stagnant / halts there after buffer spooling.



Now you start thinking if the project settings that you had set to execute the package using 64BitRuntime have really come into effect. Also you notice that the description pane for this property clearly informs us that this property is ignored if 64bit SSIS runtime is not installed on the machine. Now did we not assume that when we selected the Business Intelligence Development Studio or Management Tools - Complete in the SQL Server installation - Feature selection page during the SQL Server setup installation on our 64bit machine, we expected a 64bit SSIS Debug Runtime [64bit DTSDebugHost.exe] to be installed along on our machines.



On further investigation you would determine that you would be able to see the 32bit SSIS Debug Runtime [DtsDebugHost.exe * 32] under <Installation Drive>\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DtsDebugHost.exe , but 64bit SSIS Debug Runtime [DtsDebugHost.exe] is NOT available under <Installation Drive>\Program Files\Microsoft SQL Server\100\DTS\Binn. Does that mean that our SQL client installation didn’t complete successfully? Answer – No . This is as per the design of the SQL Server installer.

For installing the 64bit version of DTSDebugHost.exe on 64-bit computers, you need to select the following options in the SQL Server installation - Feature selection page,

- Select Integration Services to install the Integration Services service to run packages outside the design environment and also to install the 64 bit SSIS debugging environment wiz. DTSDebugHost.exe - x64 bit.

- On a 64-bit computer, selecting Integration Services it installs only the 64-bit runtime and tools. If you have to run packages in 32-bit mode, you must also select an additional option to install the 32-bit runtime and tools:

· i.e. select Business Intelligence Development Studio or Management Tools – Complete as well.







INFERENCE:

If you are intending to use x64 bit SSIS runtime when executing the SSIS packages from the Visual Studio BIDS environment,  you to need to have the following,

a. In the Project Properties of an Integration Services package, you need to select 64-bit execution by setting the value of the Run64BitRuntime property to true on the Debugging page. By default, the value of this property is True. When the 64-bit version of the Integration Services runtime is not installed, this setting is ignored.

b. You must install the Integration Services feature on this machine for the respective 64 bit DTSDebugHost.exe [ 64 bit SSIS Debug Engine ] to be triggered during the package execution.

This way of including the x64 bit version of DTSDebugHost.exe in a separate installer and providing it as a part of Integration Services installation separately is as per the Microsoft SQL Server product Licensing Policy. We just provide the end user the 32bit version of SSIS designer / debugging runtime [ DTSDebugHost *32 ] along with the Visual Studio - Business Intelligence Development Studio alone. If the user is looking forward to use the x64 bit version of SSIS debugging runtime [ DTSDebugHost.exe *64], he/she needs to install the licensed version of Integration Services on the machine.

Also the recommended installation on the SSIS package developer machine would be the following,

· Integration Services to install the Integration Services service and to run packages outside the design environment and for 64bit DTS Debugging Engine [64bit DTSDebugHost.exe]  [if the SSIS package requires a 64bit Debugging Engine as in our case]

· Business Intelligence Development Studio to install the tools for designing packages. [MUST]

· Client Tools SDK to install managed assemblies for Integration Services programming. [if necessary]

Referenced Articles:

1. Considerations for Installing Integration Services: http://msdn.microsoft.com/en-us/library/ms143731(v=sql.105).aspx

2. 64 bit Considerations for Integration Services: http://msdn.microsoft.com/en-us/library/ms141766(v=sql.105).aspx

3. Introducing Business Intelligence Development Studio: http://msdn.microsoft.com/en-us/library/ms173767(v=sql.105).aspx



Author : Krishnakumar Rukmangathan, Support Engineer, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft