2

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
user3812719
  • 25
  • 5
  • You're a far braver soul than I... However, it sounds like you've done the correct actions to confirm the changes are as expected. Two scenarios I can come up with: the first is you're not actually running *that* package. Verify the SQL Agent job definition (or whatever calls dtexec that the package it is pointing to that package. It could be that it exists in a subfolder, etc). – billinkc May 25 '22 at 19:20
  • 1
    Option two -> You mention "update a package script" When you say script, are you referencing a VB.NET/C# script Task/Component or did script just mean SSIS package? If that's the case and further assuming this is a 2005/2008/R2 package, my hazy recollection is that it stored the 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) – billinkc May 25 '22 at 19:24
  • Thank you billinkc. By "script" I meant the VB script inside the SSIS package. Sorry, I should have been more clear. – user3812719 May 25 '22 at 20:03
  • 1
    Our SQL Server is 2016, and, per your suggestion, I rebuilt the .dtsx file (using VS2017). I then imported the re-built .dtsx into our SQL Server database via SSMS (overwriting the old package). Running it via DTExec, however, gave us the error "Description: The version number in the package is not valid. The version number cannot be greater than current version number." At least it's a different error message, one I've been getting when earlier I was trying to run the .dtsx file via DTExec's "/file" option. So I think you've pointed us in a useful direction. – user3812719 May 25 '22 at 20:08
  • 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 have the `dtutil` in the 130 path https://stackoverflow.com/a/18754188/181965 otherwise the deploy will upgrade the package during deploy – billinkc May 25 '22 at 20:57
  • Thank you billinkc, it worked! I opened a project in VS 2017, added the exported .dtsx file, then rebuilt the solution. VS2017 created a new .dtsx file in its obj folder which I then imported into SQL Server using dtutil.exe. I specifically used the one in the 130 folder of Microsoft SQL Server, namely, "C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\dtutil.exe". I have since did the same process to several other packages and they are all working. You're a genius! :) – user3812719 May 26 '22 at 19:11

1 Answers1

1

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

billinkc
  • 59,250
  • 9
  • 102
  • 159