英文:
Python in Execute Process Task
问题
我已经编写了一个Python代码，然后通过SSIS的"执行进程"任务来运行它。
该代码通过Google API（使用pip安装googletrans==4.0.0-rc1）来读取和写入SQL Management Studio以进行翻译，没有通过SSIS进行输入或输出。输入和输出是通过Python中的sqlalchemy完成的。
这段代码在Jupyter Notebook中运行得非常正常，并且我将其保存为.py文件，但是在SSIS的"执行进程"任务中出现了很多错误。
英文:
I have written a python code, then ran it through SSIS in the Execute Process Task.
The code reads and writes to SQL management studio to translate via Google API (pip install googletrans==4.0.0-rc1), with no output or input through SSIS. the input and output are done through sqlacademy in python.
The codes runs perfectly fine in Jupyter notebook, and I saved it to be .py file, but I face a lot of error in the Execute Process Task in SSIS.
答案1
得分: 0
以下是您要翻译的内容：
HelloWorld.err.py
A trivial python script that will write a string to standard out, another to standard error and the exit with return code 1.
import sys
print("Hello world")
print("Egads, something bad happened!", file=sys.stderr)
exit(1)
SSIS package
A sample SSIS package would look something like the following. An Execute Process Task has a successor of a Script Task.
[![enter image description here][1]][1]
Double click the precedent constraint (the line between the two) and change that from the default of Success to Completion
[![enter image description here][2]][2]
EPT Python
An Execute Package Task that is configured as shown
[![enter image description here][3]][3]
- Executable: C:\Program Files\Python39\python.exe
- Arguments: C:\ssisdata\SO_75361110.err.py
- StandardOutputVariable: User::StdOut
- StandardErrorVariabele: User::StdErr
SCR Echo Back
This is a generic Script Task that I use and abuse to dump the values of SSIS Variables to the output log/results tab. It's easy, lightweight and let's you print values without popups or any hard work.
All you need to do is add the Variable(s) in the readonly/readWrite collection. In this case, I selected User::StdOut and User::StdErr
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_ede4b38a087c4724a71149ffc5ed6be9
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
bool fireAgain = false;
foreach (Variable item in Dts.Variables)
{
Dts.Events.FireInformation(0, "SCR Echo Back", string.Format("{0}->{1}", item.QualifiedName, item.Value), "", 0, ref fireAgain);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
}
}
Results
When my package runs, as "expected" the SuccessValue from my run will be 1 given that I use exit(1) from the python script.
Let's see what the Output tab looks like
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_75361110.dtsx" starting.
Error: 0xC0029151 at EPT Python the thing, Execute Process Task: In Executing "C:\Program Files\Python39\python.exe" "C:\ssisdata\SO_75361110.err.py" at "", The process exit code was "1" while the expected was "0".
Task failed: EPT Python the thing
Warning: 0x80019002 at SEQC Do the thing: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdErr->Egads, something bad happened!
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdOut->Hello world
Warning: 0x80019002 at SO_75361110: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_75361110.dtsx" finished: Failure.
The program '[68044] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
Of note, are these two lines
> Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdErr->Egads, something bad happened!
> Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdOut->Hello world
That's our Script Task echoing back the value of our Standard Out and Standard Error text streams. My suspicion is that given a "normal" run is 10 minutes and the runs from SSIS are measured in seconds, the process execution is throwing errors to the caller but nobody is listening. This will add a listener and give you better information to diagnose your root cause.
[1]: https://i.stack.imgur.com/hhf97.png
[2]: https://i.stack.imgur.com/sGunj.png
[3]: https://i.stack.imgur.com/Ku4sY.png
<details>
<summary>英文:</summary>
HelloWorld.err.py
====
A trivial python script that will write a string to standard out, another to standard error and the exit with return code 1.
import sys
print("Hello world")
print("Egads, something bad happened!", file=sys.stderr)
exit(1)
SSIS package
====
A sample SSIS package would look something like the following. An Execute Process Task has a successor of a Script Task.
[![enter image description here][1]][1]
Double click the precedent constraint (the line between the two) and change that from the default of Success to Completion
[![enter image description here][2]][2]
EPT Python
-----
An Execute Package Task that is configured as shown
[![enter image description here][3]][3]
* Executable: C:\Program Files\Python39\python.exe
* Arguments: C:\ssisdata\SO_75361110.err.py
* StandardOutputVariable: User::StdOut
* StandardErrorVariabele: User::StdErr
SCR Echo Back
---
This is a generic Script Task that I use and abuse to dump the values of SSIS Variables to the output log/results tab. It's easy, lightweight and let's you print values without popups or any hard work.
All you need to do is add the Variable(s) in the readonly/readWrite collection. In this case, I selected `User::StdOut` and `User::StdErr`
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_ede4b38a087c4724a71149ffc5ed6be9
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
bool fireAgain = false;
foreach (Variable item in Dts.Variables)
{
Dts.Events.FireInformation(0, "SCR Echo Back", string.Format("{0}->{1}", item.QualifiedName, item.Value), "", 0, ref fireAgain);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
}
}
Results
----
When my package runs, as "expected" the SuccessValue from my run will be 1 given that I use `exit(1)` from the python script.
Let's see what the Output tab looks like
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_75361110.dtsx" starting.
Error: 0xC0029151 at EPT Python the thing, Execute Process Task: In Executing "C:\Program Files\Python39\python.exe" "C:\ssisdata\SO_75361110.err.py" at "", The process exit code was "1" while the expected was "0".
Task failed: EPT Python the thing
Warning: 0x80019002 at SEQC Do the thing: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdErr->Egads, something bad happened!
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdOut->Hello world
Warning: 0x80019002 at SO_75361110: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_75361110.dtsx" finished: Failure.
The program '[68044] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
Of note, are these two lines
> Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdErr->Egads, something bad happened!
> Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdOut->Hello world
That's our Script Task echoing back the value of our Standard Out and Standard Error text streams. My suspicion is that given a "normal" run is 10 minutes and the runs from SSIS are measured in seconds, the process execution is throwing errors to the caller but nobody is listening. This will add a listener and give you better information to diagnose your root cause.
[1]: https://i.stack.imgur.com/hhf97.png
[2]: https://i.stack.imgur.com/sGunj.png
[3]: https://i.stack.imgur.com/Ku4sY.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论