SSIS 使用 OAuth2 将 Python 脚本的进程任务执行到 API - 使用保存的令牌拒绝访问文件 [英] SSIS Execute Process Task for Python script to API with OAuth2 - Access denied to the file with saved token

问题描述

我遇到了奇怪的问题，不知道要进一步检查什么.

情况总结:

• 以管理员身份在 Windows Server 2016 上工作，所以应该没有任何访问问题
• 开发了一些 Python 脚本，用于从 Google Analytics 中获取数据，以

  一切正常！

  I have strange problem and don't know what to check further.

  Summary of the situation:

  • working on the Windows Server 2016 as administrator, so should not have any access problems
  • developed some Python script for getting data from Google Analytics using , as basis took this example (but getting data only from one account and only for one time interval)
  • script successfully works: withing the first time code of API execution opens GA Web Site in browser, requests for authorization and approves API connection, creates analytics.dat file with store connection token inside
  • right after that all the next script executions successfully get information from GA and save it in file
  • but when I trying to add the same script execution into SSIS Package as Execute Process Task step and executing that step (Visual Studio is opened under the same administrator account withing the same active windows session) - it requests again authorization on the web-site, after successful authorization I see The authentication flow has completed message in browser, close it, but getting the error of Python script's execution:

  C:Program Files (x86)Microsoft SQL Server110DTSinn>C:Python27python.exe C:BIAPIPython_GoogleAnalytics_Reportingv3_api_analyticsgoogle_analytics_api_v3_10krows_nosampling_multiple_profiles.py C:Python27libsite-packagesoauth2client_helpers.py:255: UserWarning: Cannot access analytics.dat: No such file or directory
  warnings.warn(_MISSING_FILE_MESSAGE.format(filename))

  Your browser has been opened to visit:

  https://accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fanalytics.readonly&redirect_uri=http%3A%2F%2Flocalhost%3A8080%2F&response_type=code&client_id=xxx.apps.googleusercontent.com&access_type=offline
  

  If your browser is on a different machine then exit and re-run this application with the command-line parameter

  --noauth_local_webserver

  Traceback (most recent call last): File "C:BIAPIPython_GoogleAnalytics_Reportingv3_api_analyticsgoogle_analytics_api_v3_10krows_nosampling_multiple_profiles.py", line 172, in if name == 'main': main(sys.argv) File "C:BIAPIPython_GoogleAnalytics_Reportingv3_api_analyticsgoogle_analytics_api_v3_10krows_nosampling_multiple_profiles.py", line 54, in main scope='https://www.googleapis.com/auth/analytics.readonly')
  File "C:Python27libsite-packagesgoogleapiclientsample_tools.py", line 93, in init credentials = tools.run_flow(flow, storage, flags)
  File "C:Python27libsite-packagesoauth2client_helpers.py", line 133, in positional_wrapper return wrapped(*args, **kwargs)
  File "C:Python27libsite-packagesoauth2client ools.py", line 247, in run_flow storage.put(credential)
  File "C:Python27libsite-packagesoauth2clientclient.py", line 421, in put self.locked_put(credentials)
  File "C:Python27libsite-packagesoauth2clientfile.py", line 83, in locked_put self._create_file_if_needed()
  File "C:Python27libsite-packagesoauth2clientfile.py", line 70, in _create_file_if_needed open(self._filename, 'a+b').close() IOError: [Errno 13] Permission denied: 'analytics.dat'

  • I tried to delete that file analytics.dat - but after that getting the same error.
  • I executed Python script by .bat file with the command inside C:Python27python.exe C:BIAPIPython_GoogleAnalytics_Reportingv3_api_analyticsgoogle_analytics_api_v3_10krows_nosampling_multiple_profiles.py. Execution of the .bat itselft works fine, but execution of the same .bat from SSIS Package as Execute Process Task step - returns the errors above
  • Also, had the same problem before with Bing Ads API. But there I temporary fixed that by commented out line in the code with re-writing of updated token information to the file: in Bing Ads it is valid long time before expiring. But in the GA API that token in file expires after 1 hour and after that that file need to be overwritten.
  • also, I tried to add into scripts some prints to show under which service account script executes from SSIS package in Visual Studio (using print(os.getlogin())) - but as expected it shows the same administrator account under which I am working on the Server...

  So, I don't understand, why I am getting such Access denied to the file with saved token problem... In the properties of the file the owner - is the same Windows Administrator account, under whose session I am opening Visual Studio for execution of the step in DTSX package.

  Could you help me to find the problem, please?

  解决方案

  I found the problem. And it was related with the properties of Execute Process Task step of SSIS Package...

  As I noticed above, I executed Python script by .bat file with the command inside: C:Python27python.exe C:BIAPIPython_GoogleAnalytics_Reportingv3_api_analyticsgoogle_analytics_api_v3_10krows_nosampling_multiple_profiles.py. Execution of the .bat itself worked fine, but execution of the same .bat from SSIS Package as Execute Process Task step - returns the errors above.

  In the logs of errors in the beginning we see:

  C:Program Files (x86)Microsoft SQL Server110DTSinn> C:Python27python.exe C:BIAPIPython_GoogleAnalytics_Reportingv3_api_analyticsgoogle_analytics_api_v3_10krows_nosampling_multiple_profiles.py

  C:Python27libsite-packagesoauth2client_helpers.py:260: UserWarning: Cannot access analytics.dat: No such file or directory

  warnings.warn(_MISSING_FILE_MESSAGE.format(filename))

  What says initially, that there is no file analytics.dat in the working folder of script. But I expected, that script executes in the same folder where it is located and there already had existed file C:BIAPIPython_GoogleAnalytics_Reportingv3_api_analyticsanalytics.dat

  But then I noticed, that on the first place in that message (I marked it as bold) is working folder of the external script execution, script starts in the SSIS folder.

  I found, that when the process is launched from SSIS, it's not being run from the same folder as the executable .bat file located. What is different from the direct .bat file execution.

  So, it is necessary additionally specify working folder property of Execute Process Task step of SSIS Package.

  I set such property value:

  Working directory: C:BIAPIPython_GoogleAnalytics_Reportingv3_api_analytics

  And everything works fine!

  这篇关于SSIS 使用 OAuth2 将 Python 脚本的进程任务执行到 API - 使用保存的令牌拒绝访问文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！