Compiling script components in SSIS package through code

LVS Raghu Vamsi 40 Reputation points
2023-09-14T11:17:08.1566667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,572 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,444 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,155 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,342 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Patrick Rühmkorb 1 Reputation point
    2023-09-14T12:21:56.91+00:00

    @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();