Tag Archives: Development

Articles on Microsoft SQL Server development

#0424 – SQL Server – Null value is eliminated by an aggregate or other SET operation. – Why? How to fix?

I recently ran into a forum post where the poster wanted to know why they were getting the following warning during query execution:

"Null value is eliminated by an aggregate or other SET operation."

Having been asked this question a few times by a few of my office colleagues as well, I thought to write up a quick post on the reason behind this warning.

A quick test

The test below is simple – I am creating a sample test table which allows NULL values to be inserted. I am then trying to perform a simple aggregate function (SUM) over the NULL-able column. Upon checking the “Messages” tab, we see that no warning is returned.

USE [tempdb];

--Safety Check
IF OBJECT_ID('dbo.NULLAggregation','U') IS NOT NULL
    DROP TABLE [dbo].[NULLAggregation];

--Create the test table
CREATE TABLE [dbo].[NULLAggregation] 
    ([Id]          INT           NOT NULL IDENTITY (1,1),
     [Value]       INT               NULL,
     [ValueString] VARCHAR(50)   NOT NULL,
     CONSTRAINT [pk_NULLAggregation] PRIMARY KEY ([Id])

--Insert the test data
INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( 1, 'One'),
       (10, 'Ten'),
       (22, 'Twenty-Two');

--Perform the aggregation
--NOTE: No NULL values are in the table at this point
SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];


Now, I add a single record with a NULL value in the [Value] column and repeat the aggregation. While the result is the same, we have a warning in the “Messages” tab.

INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( NULL, 'One');

SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];

Warning: Null value is eliminated by an aggregate or other SET operation.
A screenshot showing the warning encountered in SSMS when an aggregation operation is performed on a NULL value.
Warning encountered when aggregating on NULL values

The reason for the warning

To begin – this is just a warning and not an error. If your script/job is failing it is probably failing due to some other data condition OR as an indirect result of operating on NULL values.

The warning simply suggests that an aggregation operation is being done on a NULL value. If no NULL values are present in the dataset being evaluated, then the warning is not encountered.


The important thing is to consider what is important for the business/domain.

  • If processing on NULL values are okay for the business/domain, then one of the following two (2) workaround can be applied:
    • The warning can either be ignored OR
    • Use the “SET ANSI_WARNINGS OFF” option for your query/procedure
  • If processing on NULL values is not acceptable for your business/domain, then
    • Either use a simple WHERE clause to remove the records with NULL values from the aggregation
    • Use input validations in the application code and NOT NULL checks in the database to stop the NULL values from being entered by the users

NOTE: If you do decide to use the “SET ANSI_WARNINGS OFF” option, please do so with caution. It can have unintended consequences with string operations as well (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15)

I trust you found this post useful.

Until we meet next time,

Be courteous. Drive responsibly.


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


The package is dependent upon the following variables:

Variable NameDataTypeExpression / Default ValueRemarks
SourceFolderString(My source folder path)
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.

#0421 – SQL Server – SSIS – Wild card search to find if a file exists in a folder or directory

I recently answered a question on a forum which I believe will be useful to many of the readers in the audience.

SSIS packages are widely used for data import from and export to files. One of the main tasks in this situation would be to check if files with certain kinds of names exist in a particular folder or directory, i.e. basically perform a wild-card search in a directory.

The solution

This can be achieved by using the EnumerateFiles() method of the System.IO.Directory class in the SSIS Script task. Here’s the sample package:

In a folder, I have a set of files, some with similar names (which we will search from the SSIS package).

Files existing in the directory to be searched

The SSIS package has two (2) variables:

Variable NameConfiguration on the Script TaskDescription
SearchPathReadOnly, InputThis is the path to be searched
SearchPatternReadOnly, InputPattern to be searched
FileExistsReadWrite, OutputA boolean indicating downstream processes whether files were found or not
Table 1: Variables on the SSIS package
Screenshot showing the variables and their configuration on the script task

The script is a quite simple implementation as below:

public void Main()
    // SQLTwins: SSIS: Blog #0421

    string searchPath = Dts.Variables["User::SearchPath"].Value.ToString();
    string searchPattern = Dts.Variables["User::SearchPattern"].Value.ToString();

    System.Collections.Generic.List<string> searchResults = System.IO.Directory.EnumerateFileSystemEntries(searchPath, searchPattern).ToList();

    if (searchResults.Any())
        Dts.Variables["User::FileExists"].Value = true;

    Dts.TaskResult = (int)ScriptResults.Success;

Ensure that you have the following directive in the “namespaces” section of your script:

using System.Linq;

Here’s the script in action:

Screenshot showing the script in debug mode indicating that files were found matching the pattern.

As you can see, the script can help perform a wild-card search in a given folder or directory.

Further Reading:

  • File System errors when trying to move and rename a file [Blog Link]
  • Adding Date & Time to file names after processing [Blog Link]
  • VSTA Errors when working with SSIS packages [Blog Link]
  • System.IO.Directory.EnumerateFiles [MSDN 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

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],
       DB_NAME([database_id]) AS [DatabaseName],
FROM [sys].[dm_exec_sessions]
WHERE [is_user_process] = 1
  AND [host_process_id] = 4388; --This is the PID from the Task Manager

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

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.


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

#0419 – 2 things I didn’t know about Notepad

Today’s post is a quick one based on an experience I had recently. In one hour, I learnt two (2) new things that I didn’t know about Windows Notepad.

I was in an Azure training recently and working my way through a lab exercise. As I was building my environment, I had collected a bunch of tokens and connection strings in a Notepad file. And that’s when I ran into the first thing I didn’t know about Notepad.

The maximum length of text allowed in a line is 1024 characters

The following is a simulated text that I generated by replicating the English alphabet and the numbers (0-9) such that the resulting string is 1044 characters in length. In Notepad, the string automatically wraps at 1024 characters.

The workaround was simply to open the file in the Visual Studio IDE (which does not have the same limitations).

Image showing automatic wrapping of strings to 1024 characters in Notepad
Image showing automatic wrapping of strings to 1024 characters in Notepad

Text searches only work for first 128 characters

Immediately after I realized the word wrap limit, I was trying to search a connecting string (which was 133 characters in length) and landed up with multiple hits (which I was not supposed to).

After triple-checking everything, looked at what was being searched and that’s when I realized that the search box only takes 128 characters.

Screengrab showing that the text in the Notepad search box in Notepad stops at 128 characters
Screengrab showing that the text in the Notepad search box stopped at 128 characters (green lines)

If you want to see it for yourself

You can use copies of the following string (37 characters in length) and an instance of Notepad:


Until we meet next time,

Be courteous. Drive Responsibly.