Creating EzApi for SQL Server 2022

I used EzApi to generate SSIS (SQL Server Integration Services) packages programmatically in C#. Back then, I relied on the original Microsoft EzApi project from CodePlex, which has been discontinued for years. I had forked the code and maintained my own builds, supporting SQL Server versions 2008R2, 2012, 2014, and 2016.

The moment came to validate EzApi against SQL Server 2022, and as soon as I started generating a DTSX package, several issues surfaced. A package that previously built successfully for SQL Server 2012, 2014, and 2016 can no longer be produced under the 2022 environment with coresponding fixes on EzApi dlls like before.

All started from here: in the application, the code was using eZ classes / objects like this:

var storedProcedureTask = new EzExecSqlTask(extractSequence);

which started throwing this error:

Cannot create a task from XML for task "", type "" due to error 0x80070057
The parameter is incorrect

After a lot of hours and AI help, I managed to find that I need to switch the EzApi moniker ExecID from simple reference:

Microsoft.ExecuteSQLTask

to a fully assembly versioned reference:

Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=16.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Then next problem was ScriptTask, which was not using EzApi:

Executable eee = SubSequence.Executables.Add("STOCK:ScriptTask");

which also started throwing an error:

Cannot create a task from XML for task "", type "" due to error 0x80070057
The parameter is incorrect

After a lot of hours and AI help managed to find that I need to switch the moniker into the application code from stock reference

STOCK:ScriptTask

to fully assembly versioned reference

Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=16.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Then next problem was that scipt task coudn't be passed

Task.ScriptingEngine.VstaHelper.LoadNewProject(task.ProjectTemplatePath, null, "MyScriptProject");

which started throwing this error:

System.ApplicationException: 'Except devenv, all applocalized program like dtdebughost.exe should be under CommonExtensions folder'

AI suggested that Microsoft restricted this dll of script task to not be used in order apps (except Microsoft Visual Studio) because this DLL is a code compiler which can be used by apps resulting in vurnable code generations, but I am not sure. Just because I spent a lot of hours in this one, I just removed completly this step to just check what else is happening.

Then next problem was that DataFlow which was using EzApi class

dataSourceFlow = new EzDataFlow(ezSequence);

also started throwing an error

Cannot create a task from XML for task "", type "" due to error 0x80070057
The parameter is incorrect

Now with previous knowledge based on monikers switch, I switched from standard moniker

Microsoft.Pipeline

to versioned standard moniker

SSIS.Pipeline.8 in Ezapi

BTW I was able to find available monikes for executables, components, connections by introducing debuging mechanism into EzApi, for example:

var app = new Microsoft.SqlServer.Dts.Runtime.Application(); bool found = false; foreach (Microsoft.SqlServer.Dts.Runtime.TaskInfo ti in app.TaskInfos) { Console.WriteLine($"Available ExecutableClassID (Moniker) '{ti.CreationName}' in System TaskInfos."); }

This allowed me to continue, but then it failed at package save:

package.SaveToFile(CreationPath + @"\" + _PO.Name + ".dtsx");

throwing an error

Failed to save Data Flow objects

Source "Extract Source Flow CompanyEXT"

StackTrace
at Microsoft.SqlServer.Dts.Runtime.Package.SaveToXML(String& packageXml, IDTSEvents events)
at Microsoft.SqlServer.SSIS.EzAPI.EzPackage.SaveToXML(IDTSEvents events)
at Microsoft.SqlServer.SSIS.EzAPI.EzPackage.SaveToFile(String fileName)
at xxxxxx.xxxxxx.Helpers.SSISPackageHelper.CreateSSISPackage() in C:\\TFS\\xxxxxx\\xxxxxx\\xxxxxx\\xxxxxx\\xxxxxx.xxxxxx\\xxxxxx.xxxxxx\\Helpers\\SSISPackageHelper.cs:line 339

After some days with countless hours of debugging, I understood that SSIS monikers have been changed in SQL Server 2022 (maybe even earlier). Now targeting to resolve the DataFlow issue I created a sample app just to isolate the problem.

This code with monikers change in EzApi works fine

var pkg0 = new EzPackage(); var df0 = new EzDataFlow(pkg0); pkg0.SaveToFile(@"C:\\test.dtsx");

But this code with monikers change in EzApi does not work

var pkg0 = new EzPackage(); var df0 = new EzDataFlow(pkg0); IDTSComponentMetaData100 oledbComp = df0.DataFlow.ComponentMetaDataCollection.New(); oledbComp.ComponentClassID = "DTSAdapter.OLEDBSource.8"; IDTSDesigntimeComponent100 oledbInst = oledbComp.Instantiate(); oledbInst.ProvideComponentProperties(); oledbComp.Name = "TestOleDbSource"; oledbComp.ValidateExternalMetadata = false; pkg0.SaveToFile(@"C:\\test.dtsx");

The moment that IDTSComponentMetaData100 is there it stops working and throws error

Failed to save Data Flow objects

Then searching on this dedicated issue I found that the SaveToXML functions fails in EzApi which fails from Microsoft.SqlServer.PipelineXML DLL. I suspect that Microsoft.SqlServer.PipelineXML expects the old monikers.

To ensure this I did another test and I generated a package directly from Microsoft Visual Studio using the Integration Services Project and the generated package while inspecting it's xml had the old monikers as CreationName.

I have concluded that there are now runtime monikers which differ from design/save monikers and I haven't found any way to resolve this into my app / EzApi.

BTW, the new EzApi SQL Server 2022 project and changes described here can be found in the sql_server_2022_resolve_monikers branch:

https://github.com/koureasstavros/SQLServer2022EzApi/blob/sql_server_2022_resolve_monikers/

Any ideas?

Looking on how SQL Server 2016 was working and monikers available using app.TaskInfos seems that there was .5 also there, but those were able to be resolved without beeing specified with .5 inside app or EzApi. This changes a bit the picture, so the monikers should be left like they were but find the issue of why there is this error ins SQL Server 2022

Cannot create a task from XML for task "", type "" due to error 0x80070057
The parameter is incorrect

Here is the original code that was working in SQL Server 2016 but dlls and references for SQL Server 2022 https://github.com/koureasstavros/SQLServer2022EzApi/blob/sql_server_2022