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.

SSIS_1

 

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
        Try
            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)
            Loop
            loFileStream.Flush()
            loFileStream.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        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

try:
    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.

    Returns:
        Credentials, the obtained credential.
    """
    home_dir = os.path.expanduser('~')
    credential_dir = os.path.join(home_dir, '.credentials')
    if not os.path.exists(credential_dir):
        os.makedirs(credential_dir)
    credential_path = os.path.join(credential_dir,
                                   'drive-python-quickstart.json')

    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:
        try:
            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
                    __title__=os.path.splitext(__file__)[0]
                    file_metadata = {'name' : __title__,'mimeType' : 'application/vnd.google-apps.spreadsheet','parents':[to_folder_id]}
                    __filepath__=os.path.join(DirPath,__file__)
                    media = MediaFileUpload(__filepath__,
                        mimetype='application/vnd.ms-excel',
                        resumable=True)
                    drive_service.files().create(body=file_metadata,media_body=media,fields='id').execute()           

            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'])
                drive_service2.files().delete(fileId=child['id']).execute()
            page_token = children.get('nextPageToken')
                #break

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

def main():

    from_folder_id = '0B7XCKEO-_TXNM1Ba1VTU0' # this the google folder id
    to_folder_id='0B7XCKEO-_TXNTEdLZF3bmc' # this the google folder id
    drive_files_in_folder(from_folder_id,to_folder_id)

if __name__ == '__main__':
    main()

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

ssis_2

Advertisements

2 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s