#0420 – SQL Server – SSIS – Does SSIS use TCP/IP by default?


A while ago, I wrote a post on how to force SSMS to use TCP/IP for connections to the local SQL Server instance. This led me to a question – what is the default connection protocol used by SQL Server Integration Services (SSIS) when connecting to SQL Server Does SSIS have the same affinity to use Shared Memory over TCP/IP?

Does SSIS always use TCP/IP?

To find the answer to this question, I devised a simple test. I wrote a simple SSIS package and went about checking the connection properties of the connections opened by the SSIS package.

The test package

The test package is quite simple. I have a Data Flow task with two (2) – OLEDB connection managers – one connected to the [AdventureWorks2019] sample database and the other connected to the [tempdb] database.

The Data Flow task simply pumps all rows of the [AdventureWorks2019].[dbo].[Employee] table to an identical test table [tempdb].[dbo].[dEmployee] that I had created before the test.

To allow for easier monitoring of the connection, I have also added a Script Task with a simple 1 second (1000 milli-second) sleep interval in the processing of each row.

The image shows the internals of the data-flow task. At the top, I have a OLE DB source pumping data to an OLE DB Destination through a Script Component.
Sample SSIS Package – Internals of the Data flow task

If you are interested, here’s how the Script task has been configured:

Script task showing all input columns as selected and directly getting transferred to output stream.

Here’s the code for the the 1 second delay (1000ms = 1 sec):

/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
///  string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
///  Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    //Introduce 1 second sleep at each row
    Thread.Sleep(1000);
}

Test Execution & Results

The test strategy is quite simple – to run the package and monitor the connections opened by the package based on the hosting process.

From Visual Studio

When the package is executed from Visual Studio, the SSIS package is executed under a Debug Host Process. So, with the package running, I use the Windows Task Manager to find the host process Id of the debug host process.

Finding the Host process PID for the DtsDebugHost.exe process.
Finding the Host process PID for the DtsDebugHost.exe process.

With once the process Id has been identified, we will head over to the SQL Server Management Studio (SSMS) and run the following query (after substituting the various Ids).

SELECT [session_id],
       [host_name],
       [program_name],
       [host_process_id],
       [client_interface_name],
       [database_id],
       DB_NAME([database_id]) AS [DatabaseName],
       [row_count]
FROM [sys].[dm_exec_sessions]
WHERE [is_user_process] = 1
  AND [host_process_id] = 4388; --This is the PID from the Task Manager
GO

SELECT * 
FROM [sys].[dm_exec_connections]
WHERE [session_id] IN (63, 66); --This is the list of session_id from the query above
GO

Here’s what the query returned in my case:

T-SQL Query output showing that SSIS connections open Shared Memory connections by default.
The SSIS connections open Shared Memory connections by default

As can be seen, the SSIS used Shared Memory connections by default when debugging the package from Visual Studio.

Independent execution of the SSIS package

To confirm that the Shared Memory connections were not caused by the SSIS Debug Host process, I simply executed the same package by double-clicking on it (which will invoke DTExec.exe).

Getting the host PID for the DTExecUI.exe process
Getting the Host PID for the DTExecUI.exe process

Using the same set of queries from above, here’s the output:

T-SQL Query output showing that SSIS connections open Shared Memory connections by default.
The SSIS connections open Shared Memory connections by default

As can be seen, the SSIS connections open Shared Memory connections by default.

References

  • SSMS uses Shared Memory connections by default [Blog Link]
  • How to download the AdventureWorks2019 sample database [MSDN Link]

Until we meet next time,

Be courteous. Drive responsibly.

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.