Tag Archives: #SQLServer

All about Microsoft SQL Server

#0422 – SQL Server – SSIS – Delete or rename files and perform other file operations without a script or writing code


One of the main reasons I value the interactions with the SQL Server community on various forums is because it often inspires me to explore alternate paths to doing the same task.

With SSIS, there are multiple patterns to achieve a particular outcome – some more prevalent than others. A task that we need to do often in SSIS is to perform file manipulations (e.g. rename or delete files after a data ingest is complete, etc). This is typically achieved by using a script task – but using a script task involves writing code. Therefore, a question that came up was:

Can we perform file operations (move, rename, delete or any other operations) without writing a script or a line of code?

The answer is that this is certainly do-able. In fact, some of my previous posts have used the same mechanism that I am proposing today.

The Solution

Assume that I have a set of files in a folder (following the pattern – SQLTwins*.txt) and I wanted to delete them. The Control Flow is quite procedural:

  • A ForEach Iterator is used to iterate through files in the directory
    • The iterator is completely configurable – allowing the user to specify the folder name and the file name pattern
  • A FileSystem task is used to perform the actual file operation
Control Flow of a package that manipulates files without a script or code!

Now, allow me to walk-you through the package configuration:

Variables

The package is dependent upon the following variables:

Variable NameDataTypeExpression / Default ValueRemarks
SourceFolderString(My source folder path)
FileNamePatternStringSQLTwins*.txt
CurrentFileStringVariable to hold current file being iterated upon by the ForEach Iterator
FullyQualifiedFileNameStringSourceFolder + CurrentFileFully-Qualified file name to be used by the FileSystem task
List of User Variables on the SSIS pacakge

ForEach Iterator

The configuration of the Foreach Iterator is quite simple:

  • Collection
    • The “Descriptions” and “FileSpec” expressions are set with the user variables – “SourceFolder” and “FileNamePattern” respectively
  • Variable Mappings
    • This allows the package to capture the output of the iterator
    • The variable “CurrentFile” will be used to capture the current file name
“Collection” tab of the ForEach Iterator showing “Descriptions” and “FileSpec” expressions set with the user variables – “SourceFolder” and “FileNamePattern” respectively
Variable Mappings showing the output of the ForEach Iterator setting the “CurrentFile” variable

File System Task

The configuration of the FileSystem task is even simpler! Other than the Name, the only configuration I did was to set the “Operation” and the “SourceVariable” variables.

Screengrab showing the configuration of the File System task

That’s it! We are all set to give the package a spin and did not write a single line of code!

When we run the package, we can see right away that the files have been deleted.

Prior to execution, we can see that the files are still present.
Once the package is executed, the files are deleted!

The intention of the post was to demonstrate that with Microsoft SQL Server and related services, there are tools and components available which allow one to get started extremely quickly. If you have never worked with SSIS before, do explore the components available in the SSIS toolbox before getting into some serious scripting!

Further Reading

  • Adding date and time to a file name after processing [Blog Link]
  • Moving and Renaming a File [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

#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.

#0418 – SQL Server – How to disable Shared Memory connections and configure a SQL Server instance to accept connections only via TCP/IP?


As part of my normal development activities, I use my trusted developer instance on my local machine. However, one of the main things I realized was that while I could connect to the instance using SQL Server Management Studio (SSMS), I could not connect to my instance via some of my SSIS packages.

I realized shortly afterwards that this was because the instance only has “Shared Memory” enabled as the protocol for connections by default. I turned on TCP/IP as well and was able to complete my work. However, I thought it best to document the steps I took for future reference.

What is my current connection using – Shared Memory or TCP/IP?

Before we go any further, let us investigate the default connection mechanism used by client applications running on the same machine as the SQL Server instance.

To do this, I have opened connections to the SQL Server via both – SSMS and SQLCmd and am then querying the DMV (sys.dm_exec_connections) to investigate the protocol being used for the connection. Session Ids used by each connection have been highlighted in the image below.

A connection to my local SQL Server instance via SQL Server Management Studio
Another connection using SQLCMD to the same SQL Server instance

Now, because my connections are active, I can take their session Ids and query the DMV – sys.dm_exec_connections which will give me the physical transport protocol that is used by this connection.

As can be clearly seen, the physical transport protocol used when connecting to a SQL Server on the same machine is “Shared Memory” by default.

If I explicitly try to connect to the instance using TCP/IP, note that I get an error #26 (Error Locating Server/Instance Specified):

Error 26 (Error Locating Server/Instance Specified) when connecting to the SQL server using TCP/IP network protocol in the SSMS “Connection Properties” window

Enabling TCP/IP

In order to change the connection, one needs to use the SQL Server Configuration Manager.

In the Configuration Manager, when we navigate to the SQL Server Network Configuration -> Protocols for <SQL Server Instance>, we notice that TCP/IP and Named Pipes are disabled – only the Shared Memory protocol is enabled.

Notice that by default, only the “Shared Memory” physical transport protocol is enabled

Now, all that needs to be done is to enable TCP/IP from the Protocol properties (right-click -> Enable or simply double-click to open the properties window) and restart the SQL Server service.

TCP/IP connections are now enabled.

Specifying Transport Protocol when connecting to the database

Now that I have reconfigured the SQL Server instance, I can now specify the protocol when connecting to a SQL server:

  1. In SSMS, when connecting to a SQL Server, click on “Options”
  2. Under “Connection Properties”, choose “TCP/IP” as the connection protocol
Accessing the “Connection Properties” screen when connecting to an instance using the Management Studio
Choosing the Network Protocol as “TCP/IP”

When I use the DMV (sys.dm_exec_connections) to check the session, I can see that it is now using TCP/IP and not Shared Memory.

Connections to the SQL Server are now using TCP/IP as the physical network protocol

How to disable Shared Memory?

One of the questions that we started with was how to disable “Shared Memory” for connections?

This can be achieved in the same way as we enabled TCP/IP. Simply use the SQL Server Configuration Manager to disable the “Shared Memory” protocol.

Disabling the Shared Memory protocol

I hope you will find this post helpful.

Disclaimer: Please DO NOT try this on your production SQL Server instances.

References:

Until we meet next time,

Be courteous. Drive responsibly.

#0417 – SQL Server – Select row count of local temp tables


I was recently contacted by a fellow team member who was interested in finding out the number of records in a temporary table which was being used as part of a long-running script.

As I had mentioned in one of my previous posts, local temporary tables are only accessible to the connection that created them. Hence, if a script is already running, the only connection that can execute queries against the local temporary table is the connection itself making this requirement a tricky one to work with.

The Solution

The solution for this is to realize that all tables – permanent or local consume storage either in a system/user database or in the tempdb. Hence, we can access this meta-data to fulfill our requirement. Let’s check it out with a demo.

Demo

In any window, we can access the dynamic management view (DMV): [sys].[dm_db_partition_stats]. As we know, this DMV returns page and row-count information for every partition in the current database.

So, let’s open a new query window in the SQL Server Management Studio and run the following query:

--Create a new test database
IF (DB_ID('SQLTwins') IS NULL)
BEGIN
    CREATE DATABASE [SQLTwins];
END
GO

USE [SQLTwins];
GO

--Window 01
BEGIN
    IF OBJECT_ID('tempdb..#SQLTwinsDemo','U') IS NOT NULL
    BEGIN
        DROP TABLE [dbo].[#SQLTwinsDemo];
    END

    CREATE TABLE [dbo].[#SQLTwinsDemo] ([Number] INT         NOT NULL,
                                        [Value]  VARCHAR(50) NOT NULL
                                       );

    INSERT INTO [dbo].[#SQLTwinsDemo] ([Number],
                                       [Value]
                                      )
    VALUES (9, 'Nine' ),
           (8, 'Eight'),
           (7, 'Seven'),
           (6, 'Six'  ),
           (5, 'Five' ),
           (4, 'Four' ),
           (3, 'Three'),
           (2, 'Two'  ),
           (1, 'One'  );
END

Now, in another window, try to run a simple row count query. As expected, it would return an error.

USE [SQLTwins];
GO
SELECT COUNT(*) FROM [dbo].[#SQLTwinsDemo];
GO
Msg 208, Level 16, State 0, Line 3
Invalid object name '#SQLTwinsDemo'.

Now, let’s use the DMV: [sys].[dm_db_partition_stats] in another window to get the row count information.

USE [SQLTwins];
GO
--Now, do this in Window #2
BEGIN
    SELECT [st].[name] AS [TableName],
           [partitionStatistics].[row_count] AS [RowCount]
    FROM [tempdb].[sys].[dm_db_partition_stats] AS [partitionStatistics]
    INNER JOIN [tempdb].[sys].[tables] AS [st] ON [st].[object_id] = [partitionStatistics].[object_id]
    WHERE [st].[name] LIKE '%SQLTwinsDemo%'
      AND ([partitionStatistics].[index_id] = 0  --Table is a heap
           OR
           [partitionStatistics].[index_id] = 1  --Table has a clustered index
          )
END
Fetching RowCount for local temporary tables using SQL Server DMVs

Hope it helps!

Until we meet next time,

Be courteous. Drive responsibly,

#0416 – SQL Server – Msg 8101 – Use column lists when working with IDENTITY columns


I have often written about IDENTITY columns on my blog. Identity columns, most commonly used to implement auto-increment keys, have been around for more than a decade now. Yet, I often see teams run into interesting use cases especially in cases where data is being migrated from one system to another.

Today’s post is based on one such incident that came to my attention.

The team was trying to migrate data from one table to another as part of an exercise to change the database structure for more efficiency. When moving the data from one table to another, they were using the option (SET IDENTITY_INSERT ON) in order to explicitly insert values into the Identity column. However, they were running into an error.

Msg 8101, Level 16, State 1, Line 24
An explicit value for the identity column in table 'dbo.tIdentity' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Here is a simulation of what they were doing:

USE tempdb;
GO
SET NOCOUNT ON;
--Prepare the environment
--   Create a table, and add some test data into it
--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO
--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO
--Turn Explicit IDENTITY_INSERT ON and insert duplicate IDENTITY values
SET IDENTITY_INSERT dbo.tIdentity ON;
GO
--NOTICE: No column list has been supplied in the INSERT
INSERT INTO dbo.tIdentity
VALUES (1, 'One'),
       (2, 'Two');
GO

--RESULTS
--Msg 8101, Level 16, State 1, Line 24
--An explicit value for the identity column in table 'dbo.tIdentity' can only be pecified when a column list is used and IDENTITY_INSERT is ON.

The Solution

Let’s re-read the error. It clearly gives an indication of what the issue is – if we need to insert an explicit value into Identity columns, we need to explicitly use column lists in our insert statements, as shown below.

USE tempdb;
GO
SET NOCOUNT ON;
--Prepare the environment
--Create a table, and add some test data into it
--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO

--Turn Explicit IDENTITY_INSERT ON and insert duplicate IDENTITY values
SET IDENTITY_INSERT dbo.tIdentity ON;
GO

--NOTE: Column list has been supplied in the INSERT,
--      so, no errors will be encountered    
INSERT INTO dbo.tIdentity ([IdentityId], [IdentityValue])
VALUES (1, 'One'),
       (2, 'Two');
GO

--Confirm that data has been inserted
SELECT IdentityId,
       IdentityValue
FROM dbo.tIdentity;
GO

--Now that data has been inserted, turn OFF IDENTITY_INSERT
SET IDENTITY_INSERT dbo.tIdentity OFF;
GO

-----------------------------------------------------------------
--RESULTS
----------
--IdentityId  IdentityValue
--1           One
--2           Two
 -----------------------------------------------------------------

Hope you will find this helpful.

Untill we meet next time,

Be courteous. Drive responsibly.