Compiling script components in SSIS package through code

Question

Actually, we have SSIS packages built in visual studio 2017 and target version as Sql server 2016 and now as part of new changes, we are updating the sql server installed version from 2016 to 2022. because of this, we changed the visual studio to 2019 and changed the target version of project from sql server 2016 to 2019 and build it. After deploying it in server, we are getting binary code issue in script components which needs compiling.

The problem is we have many packages for which we cannot open each package and compile it manually.

Can you please tell us any other way like powershell or c# code to compile script components code after changing target version.

Answer 1

@LVS Raghu Vamsi There is an alternative to do it manually. I am using the ability to create a SSIS package programmaticaly.

https://learn.microsoft.com/en-us/sql/integration-services/building-packages-programmatically/creating-a-package-programmatically?view=sql-server-ver16

I created an ETL Package with a Script Task. Inside the Script Task I iterate over the packages, load the package, iterate over all tasks and transformations to find the script tasks and script components, load them and close them. It's like opening and closing a script manually. If you do this, the binary script will be recompiled and saved.

You have to differentiate between a script component and script task because they are using different classes. After recompiling you have to save the package again.

ScriptTask task = (ScriptTask)taskHost.InnerObject;
try
                {
                    task.ScriptingEngine.LoadProjectFromStorage();
                    task.ScriptingEngine.VstaHelper.Build("");
                    task.ScriptingEngine.SaveProjectToStorage();
                    task.ScriptingEngine.DisposeVstaHelper();
                }

 ScriptComponentHost scriptComp = (compWrap as IDTSManagedComponent100).InnerObject as ScriptComponentHost;
 if (!scriptComp.LoadScriptFromComponent())
 {
    throw new Exception("Failed to load script information from the component"); 
 }
 if (scriptComp.CurrentScriptingEngine.VstaHelper == null)
 {
     throw new Exception("Vsta 3.0 is not installed properly");
 }
 if (!scriptComp.CurrentScriptingEngine.LoadProjectFromStorage())
 {
     throw new Exception("Failed to load project files from storage object");
 }
 if (!scriptComp.SaveScriptProject())
 {
     throw new Exception("Failed to save project");
 }
 scriptComp.CurrentScriptingEngine.DisposeVstaHelper();