I was trying to automate the process of rendering the SSRS report into google sheet. Well I could not find a direct way to do it so I manage to do a workaround. Hope it may help you.

Well to start of with, I created a SSIS package with a script task that runs the reports with the parameters and renders the report in excel and stores into a specified folder.



Script task code snippets(vb.net)

    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
        Dim loRequest As System.Net.HttpWebRequest
        Dim loResponse As System.Net.HttpWebResponse
        Dim loResponseStream As System.IO.Stream
        Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Dim laBytes(256) As Byte
        Dim liCount As Integer = 1
            loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
            'loRequest.Credentials = New System.Net.NetworkCredential("<>", "<>") '' if want to use username and password
            loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials 'using windows autthentication
            loRequest.Timeout = 600000
            loRequest.Method = "GET"
            loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
            loResponseStream = loResponse.GetResponseStream
            Do While liCount > 0
                liCount = loResponseStream.Read(laBytes, 0, 256)
                loFileStream.Write(laBytes, 0, liCount)
        Catch ex As Exception
        End Try
    End Sub
    Public Sub Main()
        ' Add your code here

        Dim url, destination As String
        destination = Dts.Variables("Folder_Destination").Value.ToString + "\" + Format(Now, "yyyyMMdd") + " - (" + MonthName(Month(Today)).ToString() + ") - " + Dts.Variables("CountryName").Value.ToString + ".xls"
        url = "http://"reportservername"/ReportServer/Pages/ReportViewer.aspx?%2f"Reportname"&rs:Command=Render&"parametername"=" + Dts.Variables("CountryID").Value.ToString + "&rs:Format=EXCEL"
        SaveFile(url, destination)
        Dts.TaskResult = ScriptResults.Success
    End Sub

Once the report is rendered into the folder , I used Python to convert .xls file into google sheet and store it into a particular folder in googledrive. I will not be explaining regarding the Google api and related stuff. You can use this link to do the setup.

Once you are done with the setup . You can use the following python code . I saved it as xls2Googlesheet_wly_Smry.py.

from __future__ import print_function
import httplib2
import os
from apiclient import discovery
from googleapiclient.http import MediaFileUpload
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage
from apiclient import errors

    import argparse
    flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
    flags = None

# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/drive-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/drive'
CLIENT_SECRET_FILE = 'client_secrets.json'
APPLICATION_NAME = 'Drive API Python Quickstart'

def get_credentials():
    """Gets valid user credentials from storage.

    If nothing has been stored, or if the stored credentials are invalid,
    the OAuth2 flow is completed to obtain the new credentials.

        Credentials, the obtained credential.
    home_dir = os.path.expanduser('~')
    credential_dir = os.path.join(home_dir, '.credentials')
    if not os.path.exists(credential_dir):
    credential_path = os.path.join(credential_dir,

    store = Storage(credential_path)
    credentials = store.get()
    if not credentials or credentials.invalid:
        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        if flags:
            credentials = tools.run_flow(flow, store, flags)
        else: # Needed only for compatibility with Python 2.6
            credentials = tools.run(flow, store)
        print('Storing credentials to ' + credential_path)
    return credentials

def drive_files_in_folder(from_folder_id,to_folder_id):
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    drive_service = discovery.build('drive', 'v3', http=http)
    drive_service2 = discovery.build('drive', 'v2', http=http) # i created two version as the function children() wasnt working with V3

    # the result will be a list
    result = []
    page_token = None
    DirPath="C:/Users/zoe/Google Drive/Source/"
    while True:
            param = {'q':"trashed=false",'orderBy':"modifiedDate desc"}

            for __file__ in os.listdir(DirPath):
                if __file__.endswith(".xls"): # used to look for all the xls files in the folder
                    file_metadata = {'name' : __title__,'mimeType' : 'application/vnd.google-apps.spreadsheet','parents':[to_folder_id]}
                    media = MediaFileUpload(__filepath__,

            if page_token:
                param['pageToken'] = page_token
            #if not page_token:
            children = drive_service2.children().list(folderId=from_folder_id,**param).execute()

            for child in children.get('items', []):
                print (child['id'])
            page_token = children.get('nextPageToken')

            if not page_token:
        except errors.HttpError, error:
            print ('An error occurred: %s' % error)
    return result

def main():

    from_folder_id = '0B7XCKEO-_TXNM1Ba1VTU0' # this the google folder id
    to_folder_id='0B7XCKEO-_TXNTEdLZF3bmc' # this the google folder id

if __name__ == '__main__':

After this , I used a Execute process task to run this python code.



4 thoughts on “Export .xls ( excel) to Google sheet using SSRS +SSIS and Python.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s