We are creating SSIS package dynamically using Microsoft.SqlServer.Dts.RuntimeMSDN Link. For the tables to load, we are dynamically create package and execute them. It was working fine till SQL 2014.
But, Once we deployed the packages to SQL Server 2019, some dynamic SSIS packages are not getting created at all. For E.g., Out of 4 packages, only 2 are getting created. We think that due to some threading issue, it is happening.
PFB our code for reference. The below script task is being called in loop for each table load. How to fix this issue ?
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Microsoft.SqlServer.Dts.Runtime.Package pkg = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = pkg.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ConnectionManager sql_source = pkg.Connections.Add("OLEDB");
.
.
ConnectionManager sql_dest = pkg.Connections.Add("OLEDB");
.
.
app.SaveToXml(savepath, pkg, null);
pkg.Execute();
UPDATE: Additionally the parent package is being executed through AUTOSYS. Below is the command of autosys.
&dtexec /ISServer "PackagePath\package.dtsx" /server SSISServerName /par $package::autosysJobName;autosysjobname /par $package::TableName;ALL /Par "$ServerOption::SYNCHRONIZED(Boolean)";True
