sql server - SSIS SSDT VS 2022 - After Deploying, the XML Content is not Loaded by my Script Task - Stack Overflow

admin管理员组

文章数量:1303343

I have a SSIS package that will:

Look in a folder. Locate XML files. Load XML content in the variable Execute a stored procedure with the XML content as one of the inputs.

• If the XML doesn't fit the rules determined in my stored procedure, send back a failed flag, and move to an error folder for review.
• If the XML fits run the script, return a success flag, and move to a success folder.

Running this locally, I have success.

I've now deployed the package and have set up a new SQL Agent Job to execute this package with with an existent Service Account user.

After deploying, and running the job, it always will always take the error path and move the file to the error folder.

I have enabled logging that will write to the log table. The 'success' logs vs the 'failure' logs show a few major difference.

The ForEachLoop information log is not occurring. The Script tasks information log (that loads the XML) is not occurring. I also have setup a DTS.Events.FireInformation() message to fire after the XML loading. It does not appear.

There is no error that occurs.

Thank you for your time in solving this.

Just To Review.. I have enabled logging that will write to the log table. I've ensured this not running 32 bit vs 64 I've tried to change the tasks' protection level to DontDaveSensitive I've ensured the package is pointing at the correct SQL version I've set the package to fail if the task fails

I expected to see more information about the failure, or to reveal an error but that didn't happen.

I have a SSIS package that will:

Look in a folder. Locate XML files. Load XML content in the variable Execute a stored procedure with the XML content as one of the inputs.

• If the XML doesn't fit the rules determined in my stored procedure, send back a failed flag, and move to an error folder for review.
• If the XML fits run the script, return a success flag, and move to a success folder.

Running this locally, I have success.

I've now deployed the package and have set up a new SQL Agent Job to execute this package with with an existent Service Account user.

After deploying, and running the job, it always will always take the error path and move the file to the error folder.

I have enabled logging that will write to the log table. The 'success' logs vs the 'failure' logs show a few major difference.

The ForEachLoop information log is not occurring. The Script tasks information log (that loads the XML) is not occurring. I also have setup a DTS.Events.FireInformation() message to fire after the XML loading. It does not appear.

There is no error that occurs.

Thank you for your time in solving this.

Just To Review.. I have enabled logging that will write to the log table. I've ensured this not running 32 bit vs 64 I've tried to change the tasks' protection level to DontDaveSensitive I've ensured the package is pointing at the correct SQL version I've set the package to fail if the task fails

I expected to see more information about the failure, or to reveal an error but that didn't happen.

• sql-server
• ssis
• sql-server-data-tools

Share Improve this question Follow edited Feb 10 at 23:29 Jghayes525 asked Feb 10 at 21:12 Jghayes525Jghayes525 12111 silver badge55 bronze badges 3

• Please provide enough code so others can better understand or reproduce the problem. – Community Bot Commented Feb 11 at 4:57
• Maybe the folder doesn't exist in production? Or it points to empty one – siggemannen Commented Feb 11 at 10:30
• Please edit your question, and provide SSIS Script Task code. – Yitzhak Khabinsky Commented Feb 11 at 16:44

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I have a solution.

Earlier, in the midst of coding, I realized that the code was pointing to a newer version.

I proceeded to the project properties and changed the TargetServerVersion to 2019. I corrected the issue and rebuilt waiting for errors, but none came.

Later after finishing the code and deploying, it was silently failing to execute the script task which other components are dependent on.

This was because that script task was completed before I changed the target version, and it caused it to break somehow.

I (copied the contents) and then deleted and rebuilt the script task.

I then rebuilt and deployed the package.

The deployed job now works correctly.

本文标签：