Python in Execute Process Task

huangapple
go评论108阅读模式
英文:

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.

  1. import sys
  2. print("Hello world")
  3. print("Egads, something bad happened!", file=sys.stderr)
  4. 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

  1. using System;
  2. using System.Data;
  3. using Microsoft.SqlServer.Dts.Runtime;
  4. using System.Windows.Forms;
  6. namespace ST_ede4b38a087c4724a71149ffc5ed6be9
  7. {
  8.     [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
  9.     public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  10.     {
  11.         public void Main()
  12.         {
  13.             bool fireAgain = false;
  14.             foreach (Variable item in Dts.Variables)
  15.             {
  16.                 Dts.Events.FireInformation(0, "SCR Echo Back", string.Format("{0}->{1}", item.QualifiedName, item.Value), "", 0, ref fireAgain);
  17.             }
  19.             Dts.TaskResult = (int)ScriptResults.Success;
  20.         }
  22.         enum ScriptResults
  23.         {
  24.             Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
  25.             Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
  26.         };
  27.     }
  28. }

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

  1. SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_75361110.dtsx" starting.
  2. 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".
  3. Task failed: EPT Python the thing
  4. 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.
  5. Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdErr->Egads, something bad happened!
  6. Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdOut->Hello world
  7. 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.
  8. SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_75361110.dtsx" finished: Failure.
  9. The program '[68044] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
  11. Of note, are these two lines
  13. > Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdErr->Egads, something bad happened!
  15. > Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdOut->Hello world
  17. 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.
  19. [1]: https://i.stack.imgur.com/hhf97.png
  20. [2]: https://i.stack.imgur.com/sGunj.png
  21. [3]: https://i.stack.imgur.com/Ku4sY.png
  23. <details>
  24. <summary>英文:</summary>
  28. HelloWorld.err.py
  29. ====
  30. A trivial python script that will write a string to standard out, another to standard error and the exit with return code 1.
  32.     import sys
  33.     print(&quot;Hello world&quot;)
  34.     print(&quot;Egads, something bad happened!&quot;, file=sys.stderr)
  35.     exit(1)
  37. SSIS package
  38. ====
  39. A sample SSIS package would look something like the following. An Execute Process Task has a successor of a Script Task.  
  41. [![enter image description here][1]][1]
  43. Double click the precedent constraint (the line between the two) and change that from the default of Success to Completion
  45. [![enter image description here][2]][2]
  48. EPT Python
  49. -----
  50. An Execute Package Task that is configured as shown
  52. [![enter image description here][3]][3]
  54. * Executable: C:\Program Files\Python39\python.exe
  55. * Arguments: C:\ssisdata\SO_75361110.err.py
  56. * StandardOutputVariable: User::StdOut
  57. * StandardErrorVariabele: User::StdErr
  60. SCR Echo Back
  61. ---
  63. 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&#39;s easy, lightweight and let&#39;s you print values without popups or any hard work.
  65. 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`
  68.     using System;
  69.     using System.Data;
  70.     using Microsoft.SqlServer.Dts.Runtime;
  71.     using System.Windows.Forms;
  73.     namespace ST_ede4b38a087c4724a71149ffc5ed6be9
  74.     {
  75. 	    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
  76. 	    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  77. 	    {
  78. 		    public void Main()
  79. 		    {
  80.                 bool fireAgain = false;
  81.                 foreach (Variable item in Dts.Variables)
  82.                 {
  83.                     Dts.Events.FireInformation(0, &quot;SCR Echo Back&quot;, string.Format(&quot;{0}-&gt;{1}&quot;, item.QualifiedName, item.Value), &quot;&quot;, 0, ref fireAgain);
  84.                 }
  86. 			    Dts.TaskResult = (int)ScriptResults.Success;
  87. 		    }
  89.             enum ScriptResults
  90.             {
  91.                 Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
  92.                 Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
  93.             };
  94. 	    }
  95.     }
  97. Results
  98. ----
  99. When my package runs, as &quot;expected&quot; the SuccessValue from my run will be 1 given that I use `exit(1)` from the python script. 
  101. Let&#39;s see what the Output tab looks like
  103.     SSIS package &quot;C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_75361110.dtsx&quot; starting.
  104.     Error: 0xC0029151 at EPT Python the thing, Execute Process Task: In Executing &quot;C:\Program Files\Python39\python.exe&quot; &quot;C:\ssisdata\SO_75361110.err.py&quot; at &quot;&quot;, The process exit code was &quot;1&quot; while the expected was &quot;0&quot;.
  105.     Task failed: EPT Python the thing
  106.     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.
  107.     Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdErr-&gt;Egads, something bad happened!
  108.     Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdOut-&gt;Hello world
  109.     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.
  110.     SSIS package &quot;C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_75361110.dtsx&quot; finished: Failure.
  111.     The program &#39;[68044] DtsDebugHost.exe: DTS&#39; has exited with code 0 (0x0).
  113. Of note, are these two lines
  115. &gt; Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdErr-&gt;Egads, something bad happened!
  117. &gt; Information: 0x0 at SCR Echo Back, SCR Echo Back: User::StdOut-&gt;Hello world
  119. That&#39;s our Script Task echoing back the value of our Standard Out and Standard Error text streams. My suspicion is that given a &quot;normal&quot; 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.
  122.   [1]: https://i.stack.imgur.com/hhf97.png
  123.   [2]: https://i.stack.imgur.com/sGunj.png
  124.   [3]: https://i.stack.imgur.com/Ku4sY.png
  126. </details>

huangapple
  • 本文由 发表于 2023年2月6日 20:07:20
  • 转载请务必保留本文链接：https://go.coder-hub.com/75361110.html
  • python
  • python-3.x
  • sql
  • sql-server
  • ssis
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定