SSIS Execute Process Task for Python script to API with OAuth2 - Access denied to the file with saved token

Question

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 Server\110\DTS\binn>C:\Python27\python.exe C:\BI\API\Python_GoogleAnalytics_Reporting\v3_api_analytics\google_analytics_api_v3_10krows_nosampling_multiple_profiles.py C:\Python27\lib\site-packages\oauth2client_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:\BI\API\Python_GoogleAnalytics_Reporting\v3_api_analytics\google_analytics_api_v3_10krows_nosampling_multiple_profiles.py", line 172, in if name == 'main': main(sys.argv) File "C:\BI\API\Python_GoogleAnalytics_Reporting\v3_api_analytics\google_analytics_api_v3_10krows_nosampling_multiple_profiles.py", line 54, in main scope='https://www.googleapis.com/auth/analytics.readonly')
File "C:\Python27\lib\site-packages\googleapiclient\sample_tools.py", line 93, in init credentials = tools.run_flow(flow, storage, flags)
File "C:\Python27\lib\site-packages\oauth2client_helpers.py", line 133, in positional_wrapper return wrapped(*args, **kwargs)
File "C:\Python27\lib\site-packages\oauth2client\tools.py", line 247, in run_flow storage.put(credential)
File "C:\Python27\lib\site-packages\oauth2client\client.py", line 421, in put self.locked_put(credentials)
File "C:\Python27\lib\site-packages\oauth2client\file.py", line 83, in locked_put self._create_file_if_needed()
File "C:\Python27\lib\site-packages\oauth2client\file.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:\Python27\python.exe C:\BI\API\Python_GoogleAnalytics_Reporting\v3_api_analytics\google_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?

Answer 1

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:\Python27\python.exe C:\BI\API\Python_GoogleAnalytics_Reporting\v3_api_analytics\google_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 Server\110\DTS\binn> C:\Python27\python.exe C:\BI\API\Python_GoogleAnalytics_Reporting\v3_api_analytics\google_analytics_api_v3_10krows_nosampling_multiple_profiles.py

C:\Python27\lib\site-packages\oauth2client_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:\BI\API\Python_GoogleAnalytics_Reporting\v3_api_analytics\analytics.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:\BI\API\Python_GoogleAnalytics_Reporting\v3_api_analytics\

And everything works fine!