September 12, 2017 at 3:04 am
Hi All,
We are working on some project, wherein we have to integrate SSIS packages with Google Adwords and Big Query.
As an output, we want to save data of Adwords and Big Query in SQL table by calling API or script task.
Would be great if anyone can share their experience on the same.
Thanks
Regards.
September 13, 2017 at 4:33 am
Hi guys,
Would request to help
September 13, 2017 at 7:39 am
Not sure what you need here. Both of these Google products have ways to call down the data via REST. You make a script using something like Python to make those calls and save the results as a CSV file, in which SSIS can also pick up and ingest into SQL Server. This can be setup with SSIS execute script tasks and you can split this up between two Python scripts. One to make the call via Adwords and the other to make the call via BigQuery. Both of these will have outputs of say, a CSV file per execution that you can then ingest up the line.
If you're new to say, Python, then explore the examples Google has for them. They are pretty easy and you can use modules within Python like Pandas to convert JSON or other outputs from those REST calls into a CSV flat file that is highly customizable.
September 13, 2017 at 9:43 pm
Hi,
Thanks for your reply.
I am familiar with csv and loading them into SSIS, script and python is something i haven't worked so far.
Can you please help me in understanding calling rest API and consume output as CSV. I tried searching for rest API for google adwords and big query, not sure how to use them.
Your help would be highly appreciated.
Thanks
September 13, 2017 at 10:03 pm
Sure.
What you will want to do is build a service app that is basically a Python script that builds a service object (authenticates) with the Google API you want to utilize for whatever (this is also known as your scope. Each scope is different per Google product).
Once authenticated, then you can make calls using the Google Python Library the tutorial below asks you to install. You make the call and append some JSON code to it in order to define the filters like a SELECT statement. You basically tell the API what data you want and how to filter that data. The response is normally a JSON reponse, so you have to also build a way in your script to output that JSON response to a CSV file.
As all of the Google products use oAuth. Please read up on how that works and register your script at the link below to snag your keys that used to authenticate with the API service. This helps secure your Python script with Google and you can fully control it as well monitor the calls your script makes to the service in the link below as well.
https://console.cloud.google.com/
Full tutorial here.
https://developers.google.com/analytics/devguides/reporting/core/v4/quickstart/service-py
Hope that helps.
September 13, 2017 at 11:19 pm
Hi,
We are working on client side and using some external tool like Python would be problemtic
I have worked earlier on calling REST API using wsdl, not sure how to create wsdl for adwords and big query,
Can u suggest something wherein i dont have to install any external tool.
Thanks
September 14, 2017 at 7:07 am
BI_NewBie - Wednesday, September 13, 2017 11:19 PMHi,We are working on client side and using some external tool like Python would be problemtic
I have worked earlier on calling REST API using wsdl, not sure how to create wsdl for adwords and big query,Can u suggest something wherein i dont have to install any external tool.
Thanks
You will have to search for that. Outside of using Powershell, which is going to be weaker to support this, the only other options is to use a third-party plugin for SSIS that can do the same. I just don't think they have those for Adwords and BigQuery, but I could be wrong. I tend to stay away from anything that modifies SSIS because of the support it requires from that third-party long-term.
.NET is of course an option along with Java if you have those resources. I just tend to not recommend those options because of the increased dev costs of making something that can be done so easy in Python or even Powershell.
Other options is to pay big $$$ for anyone of the ETL tools that connect to every API known to man. They are pretty expensive when all you have to do here is write like 25 lines of code in Python to get what you need. There are plenty of tutorials online and YouTube videos that show you how.
Another option if you can't find a solid way to get data via the API's is just to setup emailed reports. Then you're having to create a pipeline that is snagging emails via SSIS, which is not pretty.
September 14, 2017 at 7:36 am
BI_NewBie - Wednesday, September 13, 2017 11:19 PMHi,We are working on client side and using some external tool like Python would be problemtic
I have worked earlier on calling REST API using wsdl, not sure how to create wsdl for adwords and big query,Can u suggest something wherein i dont have to install any external tool.
Thanks
SSIS is a server-side tool, not client-side, so can you please explain what you mean?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 14, 2017 at 7:46 pm
Phil Parkin - Thursday, September 14, 2017 7:36 AMBI_NewBie - Wednesday, September 13, 2017 11:19 PMHi,We are working on client side and using some external tool like Python would be problemtic
I have worked earlier on calling REST API using wsdl, not sure how to create wsdl for adwords and big query,Can u suggest something wherein i dont have to install any external tool.
Thanks
SSIS is a server-side tool, not client-side, so can you please explain what you mean?
I didn't even catch that because I'm service based and assumed he meant client as in the customer, not as in the front-end haha. 🙂
September 15, 2017 at 6:20 am
BI_NewBie - Wednesday, September 13, 2017 11:19 PMHi,We are working on client side and using some external tool like Python would be problemtic
I have worked earlier on calling REST API using wsdl, not sure how to create wsdl for adwords and big query,Can u suggest something wherein i dont have to install any external tool.
Thanks
A Rest API won't have a wsdl. Wsdl is SOAP Terminology.
As mentioned above there are tons of tutorials on the web and google has code samples for most languages.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply