1

Note: I am not an SSIS developer and have a very limited knowledge on it, so may be missing on very small details, please read carefully.

  1. I have an existing SSIS package which has script task in one of the steps.
  2. I had to update one single value in script task, so I opened it in notepad++ and updated the value and saved it. Now when the scheduled SSIS job runs, it still picks up the old value and not the updated one.
  3. I realized script task is a VB file, so probably would need to re-compile it for the binaries. I read about it and found out, if you open it through VS and save it, VS automatically compiles it and you don't have to do anything further. I can be wrong here.
  4. I can't do project deployment on the server, so I would just need the updated package file to replace it on the server.(I am taking this updated package file from the Visual studio project that I created to edit the SSIS package. I am not sure, if there is another way to get the package or even if this is a right package.

Now the problem is,

  1. when I updated the old package using notepad++, it didn't throw any error when it was running through scheduled job, but it took the old value
  2. Now when I am putting the new package that I edited using Visual studio, it throws below error
Executed as user: sampleUser(nameupdated). Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 5:08:09 PM Error: 2021-11-02 17:08:10.83 Code: 0xC0016016 Source: OraclePackage Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2021-11-02 17:08:11.26 Code: 0x00000005 Source: Rename files in archive folder to _yyyymmddhhmm Rename files in archive folder to _yyyymmddhhmm Description: Failed to migrate scripts contained in the package to the VSTA 14.0 format. Move the scripts to a new Script task. End Error Error: 2021-11-02 17:08:14.19 Code: 0x00000005 Source: Script Task Script Task Description: Failed to migrate scripts contained in the package to the VSTA 14.0 format. Move the scripts to a new Script task. End Error Error: 2021-11-02 17:08:17.17 Code: 0x00000001 Source: Script Task Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:08:09 PM Finished: 5:08:17 PM Elapsed: 7.25 seconds. The package execution failed. The step failed.

I read about this error and found that package's security level can cause this issue, so update the protection level of the package from "EncryptSensitiveWithUserKey" to "DontSaveSensitive"

  1. Now when I am running the project I get below error
Executed as user: sampleuser(nameUpdated). Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 5:31:44 PM Error: 2021-11-02 17:31:53.93 Code: 0x00000001 Source: Script Task Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:31:44 PM Finished: 5:31:53 PM Elapsed: 9.047 seconds. The package execution failed. The step failed.

Please let me know if there is another way to edit the script task directly and compile it in place so that it takes the updated values else, if there is something else I can do.

Thanks in advance.

Duke
  • 21
  • 1
  • 5

1 Answers1

0

I found a solution(more of an error or less knowledge from my side actually)

  1. Updating the security from "EncryptSensitiveWithUserKey" to "DontSaveSensitive"
  2. When you open an existing SSIS package in Visual studio, the read and write variables are wiped out from script task for some reason. Reassigning them resolved the problem.

How did I find out if that was the problem, I put some exception handling in the script task to notify me of further issues.

Hope this helps to someone in the similar problem.

Duke
  • 21
  • 1
  • 5