Things change.
Things change in software.
Things change regularly in software.

If you take nothing else away from this post, please take those nuggets of wisdom.

Last week, my latest book – titled Building Custom Tasks for SQL Server Integration Services: The Power of .NET for ETL for SQL Server 2019 and Beyond, (Second Edition) was released.

Soon after release, I noticed the paperback and Kindle editions trending at the top of Amazon’s Microsoft SQL Server category. I was thrilled! I began streaming a walk-through of the first 9 chapters of the demo project – something I’d planned for a while (the videos for which are available for a limited time on my YouTube channel), noting the occasional errata as I went along.

I was using the same SQL Server 2019 / Windows Server 2019 virtual machine (named vDemo19) I used to build the code for the book demo. Because I was on the same VM, I assumed I would not experience any configuration issues.

My assumption was incorrect.

Errata – Chapters 1-9

Most of the errata is trivial (imo); missing semi-colons and a failure to update the original VB to C# (in one instance).

Chapter 7

– chapter 7, page 68 – missing semicolon:
public override DTSExecResult Execute(
Connections connections,
VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents,
IDTSLogging log,
object transaction)
{
return DTSExecResult.Success;
}

Chapter 9

– chapter 9, page 101, Listing 9-2 – missing “UI” at the end of the name of the editor project:
“C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8 ➥
Tools\gacutil.exe” -u ExecuteCatalogPackageTaskUI

– chapter 9, page 103, Listing 9-4 – extra double-quote in the UITypeName decoration attribute:
, UITypeName= “ExecuteCatalogPackageTaskUI.ExecuteCatalogPackageTaskUI“, ExecuteCatalogPackageTaskUI, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=<Your public key>”
, TaskContact = “ExecuteCatalogPackageTask; Building Custom Tasks for SQL Server Integration Services, 2019 Edition; © 2020 Andy Leonard; https://dilmsuite.com/ExecuteCatalogPackageTaskBookCode”

– chapter 9, page 107, Listing 9-7 – the VB version of the Execute method arguments, instead of the C# version:
catalogPackage.Execute(false, null);

A Change in SMO

The error generated when you test the code in Listing 9-6 on page 107 will break you code…and your heart. There has been a change to how the IntegrationServices object may be declared and initialized. The change breaks the previously-working code (I promise it worked before!) in the book.

You may build the demo in either Visual Studio 2019 or 2017. The projects are converted to 2017 in the second portion of the book. I explain the reason in the book. If you test the 2017 version, the error reads:

(click to enlarge)

Error: The Execute method on the task returned error code 0x80131513 (Method not found: ‘Void Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices..ctor(Microsoft.SqlServer.Management.Smo.Server)’.). The Execute method must succeed, and indicate the result using an “out” parameter.

If you encounter this error, the bold, red lines below represent the changes readers need to make to get the code to function properly:

– chapter 9, page 107, Listing 9-6
//Server catalogServer = new Server(ServerName);
//IntegrationServices integrationServices = new IntegrationServices(catalogServer);
string connectionString = String.Format(@”Data Source={0};Initial Catalog=SSISDB;Integrated Security=True;”, ServerName);
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
IntegrationServices integrationServices = new IntegrationServices(sqlConnection);
Catalog catalog = integrationServices.Catalogs[PackageCatalog];
CatalogFolder catalogFolder = catalog.Folders[PackageFolder];
ProjectInfo catalogProject = catalogFolder.Projects[PackageProject];
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo catalogPackage = catalogProject.Packages[PackageName];

I write “If you encounter this error” because I am almost positive the error will be addressed in the near future.

Essentially, the code shown above from Listing 9-6 changes the way the IntegrationServices object is initialized. The book uses an SMO Server object to initialize the IntegrationServices object; the code above uses a SqlConnection to initialize the IntegrationServices object.

Interestingly, this code is updated (a lot) later in the book, and one of the changes is to use a SqlConnection to initialize the IntegrationServices object.

Watch me walk-through this last change in this video: