We modified an SSIS package's script directly via the following:
UPDATETEXT MSDB.dbo.sysssisPackages.Packagedata @ptrval @SearchTextOffset @SearchTextLen @NewText;
The modification was to replace a string constant. To verify the modification was saved, we exported the package from SSMS and confirmed the exported script contained our modification (our new string constant).
However, when we execute the package via DTExec, the old string constant is still being used. It's as if the package was not updated.
Is there something else we need to do (after UPDATETEXT) for our change to take effect? Is UPDATETEXT even a viable way to update a package script?
Thank you.
CodePudding user response：
My recollection is that it SSIS stored the .NET script itself and the compiled binary. You might try opening the package you exported in Visual Studio, editing the Script Task and hitting the recompile button, save, save and deploy to the MSDB (via dtutil.exe)
The next challenge encountered was
The version number in the package is not valid
In the Project settings in VS 2017, change the target to SQL 2016 and redeploy. If you deploy via the command line, ensure that you usethe dtutil.exe in the 130 path, otherwise the deploy will upgrade the package during deploy operation (assuming the 140 dtutil is listed first in the PATH environmental variable)
Reference answer/chart for translating install path numbers to SQL Server versions i.e. 130 == SQL Server 2016