SSIS For Development Is Not Fun

In the vein of my 2019 post I like Git over TFVC , I have been working with SSIS and running into some challenges where I think .Net (C#/F#) running as a console app is a better choice than SSIS.

Simple projects

Code is great for reading from databases and files then creating files and writing to databases. SSIS can do this too.

In my experience and opinion, Code is much easier to modify and see what’s happening. I have more control.

SSIS is nice for the blocks that do things without code, so some will like that better.

Running locally and debugging

Pulling settings from the database instead of from a appsettings.json file is difficult, especially when you need to run it locally to create a catchup file or debug it. I usally need to disable blocks and modify database values in SSIS.

Breakpoints in .Net is possible on variables, SSIS is at a higher not always useful level.

Git

The diffs on SSIS packages .dtsx files are xml and a lot changes when behavior doesn’t change. I’d rather look at code in the Git PRs and see what changed.

Unexpected SSIS behavior

• 2 lines to the same block and it doesn’t run either
• Read file with a line to a DB insert sets lengths when it’s added.
  • I’ve had values truncated and didn’t find it later.
    • Once changed in the UI you get some warnings, but have to hunt into the Advanced Editor to find and update it.
• The tooling feels out of date and clunky compared to writing code.
• Seems like the connection manager or file connection “randomly” break and shows errors.
• Sometimes you just have to close Visual Studio and re-open it.

C# Logging is more helpful than SSIS logging

The SSIS Executions report has the OnPost and OnPre events, but doesn’t give details that are sometimes needed. In C#, I can setup a logger to log to a file and put in whatever I need.

C# Scripts Runtime failures

It’s great you can add C# script to set variables for use later. It’s worked fine for me in the past (besides having to wait for the new Visual Studio instance to open to edit it), but this project is building and running locally, but then failing when ran from the SQL Server Agent job.

Set FolderFilesList Script:Error: The binary code for the script is not found- Please open the script in the designer by clicking Edit Script button and make sure it builds successfully-

• I already deleted the package, redeployed it then failed.
• Then I deleted the script tag, recreated it and deployed then I get a new failure `invalid expression term ‘)’, ScriptMain.cs 114, 22.

Solution

The version of C# running depends on the version of SQL Server you are running. I was running SQL Server 2019, which is using C# 7.3. The code I was trying to run was using newer C# features.

The failing code was:

var filteredFiles = files.Where(file =>
{
	string datePart = Path.GetFileNameWithoutExtension(file)
		.Split('_')
		.Last();

	return DateTime.TryParseExact(
		datePart,
		"yyyyMMddHHmm",
		CultureInfo.CurrentCulture,
		DateTimeStyles.None,
		out var fileDate)
		&& fileDate >= cutoff;
}).ToList();

The working, older code syntax is:

List<string> filteredFiles = new List<string>();
foreach (string file in files)
{
	string datePart =
		Path.GetFileNameWithoutExtension(file)
			.Split('_')
			.Last();

	DateTime fileDate;

	if (DateTime.TryParseExact(
			datePart,
			"yyyyMMddHHmm",
			CultureInfo.InvariantCulture,
			DateTimeStyles.None,
			out fileDate)
		&& fileDate >= cutoff)
	{
		filteredFiles.Add(file);
	}
}

Visual Studio Extension

https://marketplace.visualstudio.com/items?itemName=SSIS.MicrosoftDataToolsIntegrationServices

Does it even work in VS 2026 (it’s supposed to)? We stuck with VS 2022.

OLEDB and Driver Updates are Difficult

I changed the connection manager to Native OLE DB\Microsoft OLE DB Driver for SQL Server, but the SSIS package still used the old provider. I had to add Provider=MSOLEDBSQL; to the connection string to get it to use the new provider. That took awhile to figure out and was a bit maddening.

I wanted to use MSOLEDBSQL19 (Microsoft OLE DB Driver 19 for SQL Server) as it is the current recommended OLE DB driver but we have SQL Server 2019.

It supports TDS 8.0 and modern security features. Version 19.2.0+ also supports TLS 1.3. Use Provider=MSOLEDBSQL19 in your connection strings. ~ https://learn.microsoft.com/en-us/sql/connect/oledb/major-version-differences?view=sql-server-ver17

Use the Microsoft OLE DB Driver for SQL Server 19 with Provider=MSSQLOLEDB19, put that into the Project Parameter Connection string ( https://stackoverflow.com/questions/77786708/is-it-possible-to-change-the-provider-within-a-connection-string-via-a-dtsconfi) . This prevents the designer from “optimizing” the provider away, because it no longer owns the literal string.

Support seems to be slow

VS 2026 came up in November 2025, SSIS tooling didn’t come out until April 2026 .

It feels like that want things moved to Azure Data Factory or Fabric.

What’s New in Integration Services in SQL Server 2025

• watch out, 32 bit is being deprecated

A Balancing Opinion on SSIS

As you can see, I’m biased towards C# and more comfortable with it. It’s about using the right tool for the right job and what the team is comfortable with.

My co-worker, who is more comfortable with SQL and works in the data world said “SSIS can be much faster at loading data than a c# console app depending. The key is to use the tool for what it is good at, which is orchestration. I have seen way too many spaghetti code SSIS packages that are unnecessary if they use it appropriately and use SQL for its strengths. When i do data movement my SSIS packages typically only ever have three or 4 components in them. Mostly just the Get Data from here and move it to here ones. the rest is all handled by TSQL and tables.”.

and then he said these wise words: “It’s all about using the right tool for what it is good at. They all have their strengths and weaknesses. I tend to use the KISS approach. Use SSIS for orchestration and SQL for what it excels at. And for edge cases I might use script tasks with C#. Otherwise the SSIS packages can get unmaintainable. Not to mentioned how bad it is to do source control on SSIS.”

It is easier for a non-developer to understand and maintain an SSIS package than a C# console app. The SSIS package is more visual and easier to follow.

Conclusion

I’m a developer, I like code :-). The real answer is, it depends. Use the “right tool for the right job”.

What do you think? Do you like SSIS or .Net better for development?