Overview
Configuration is provided for establishing connections with the Google Drive service. The configuration is used in the REST Connection Manager.
Setup
Press 
icon to get more information about the connection parameters.
Obtain data
Use REST Source component to get data from service resource.
Insert data
Use REST Destination component to insert data into service resource.
Manage remote files and directories
Use File Transfer Task to manage remote files and directories.
Demonstration
Quick Start
Step 1. Run "Visual Studio" under "Administrator account".
Select "Data Flow task" tab and Click "Add new Data flow task"
Step 2. Right click on "Connection managers tab" and Select New Connection
Step 3. In "Add SSIS Connection Manager" scroll down and Select REST "Connection managers for REST Connection" and Click "Add"
Step 4. From Configuration drop-down menu Select Google Drive and Click New
Step 5. Click NEXT
Step 6. Provide authentication details for your Google account to "Log in"
Step 7. Click Allow
Step 8. Save your password protected Google Drive access token file and Click OK
Step 9. Click "Test" to test your connection.
Step 10. Click OK
Step 1. Open the right side panel and Drag and Drop REST Source component
Step 2. Double click on it and from "General tab" Select GDrive Connection".
Next Select from available Google Drive resources listed in second drop-down menu.
Step 3. Click on "Columns tab" and Select from Google Drive available columns.
Step 4. Click on "Preview tab" to view the columns data. Next Click OK
Step 5. Drag and drop OLE DB Destination component from the left side panel - SSIS Toolbox
Step 6. Drag the blue arrow to Connect Google drive source with OLE DB Destination.
Next Select an Input and an Output to connect the components.
Step 7. Double Click on OLD DB Destination and Select Connection manager and Table name.
Step 8. Click on OLE Destination "Mappings tab" and Map the columns. Click OK
Step 9. Run the package
Step 1. Right click on "Connection managers tab" and Select New OLE DB Connection
Step 2. Select your DB connection and Click OK
Step 3. Drag and drop OLE DB Source component from the left side panel - SSIS Toolbox
Step 4. Drag and drop REST Destination component from the SSIS Toolbox and Rename it to "Google Drive destination"
Step 5. Drag the blue arrow to Connect OLE DB source with destination
Step 6. Double click on OLE DB Source component and Select your OLE DB connection and table name. Next Click Preview to see the data.
Step 7. Click on "Columns tab" on the left menu and Select the Columns you want to map.
Step 8. Double click on "Google Drive destination" component, "General tab" and Select Gdrive connection, Action and Resource from drop down menu.
Step 9. Click on "Mappings tab" and Map the columns
Step 10. Run the package.
Configuration
Base URL address: https://www.googleapis.com/drive/v3.
- OAuth
-
The authentication uses an authorized token. The token will be refreshed with the following expression:
{{=Date.now() + (response.expires_in - 300) * 1000}}.The authentication has the following user-defined parameters:
- Scope:
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
Documentation: https://developers.google.com/drive/v3/web/about-auth.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/changes.
The result is extracted from:{{=response.changes}}.
The result is paginated.The following request parameters will be automatically processed:
-
fields:
nextPageToken, changes({{=parameters.fields.join()}})
Documentation: https://developers.google.com/drive/v3/reference/changes/list.
-
fields:
- kind
Template: ShortText.
- fileId
Template: ShortText.
- removed
Data type: DT_BOOL
- time
Template: DateTime.
- file
Template: File.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/files/{{=Google.GDrive.getFileId(parameters.fileId)}}/comments.
The result is extracted from:{{=response.comments}}.The action has the following user-defined parameters:
- fileId: Required. Specifies file identifier.
The following request parameters will be automatically processed:
-
fields:
nextPageToken, comments({{=_.without(parameters.fields, 'fileId').join()}})
Documentation: https://developers.google.com/drive/v3/reference/comments/list.
- [Create] action
-
Endpoint URL address:
/files/{{=item.fileId}}/comments.Documentation: https://developers.google.com/drive/v3/reference/comments/create.
- [Update] action
-
Endpoint URL address:
/files/{{{=item.fileId}}/comments/{{=item.id}}.Documentation: https://developers.google.com/drive/v3/reference/comments/update.
- [Delete] action
-
Endpoint URL address:
/files/{{=item.fileId}}/comments/{{=item.id}}.Documentation: https://developers.google.com/drive/v3/reference/comments/delete.
- id
A key field. Template: ShortText.
- kind
Template: ShortText. A read-only field.
- createdTime
Template: DateTime. A read-only field.
- modifiedTime
Template: DateTime. A read-only field.
- author
Template: User. A read-only field.
- htmlContent
Template: LongText. A read-only field.
- content
Template: LongText.
- deleted
Data type: DT_BOOL A read-only field.
- resolved
Data type: DT_BOOL A read-only field.
- quotedFileContent
Field components:
-
Uses template:
ShortText. - Contains the following components: mimeType, value.
-
Uses template:
- anchor
Template: ShortText.
- replies
A read-only field. Field components:
- An array.
-
Uses template:
Reply.
- fileId
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/files.
The result is extracted from:{{=response.files}}.The following request parameters will be automatically processed:
-
fields:
nextPageToken, files({{=_.filter(_.without(parameters.fields, 'content', 'addParents', 'removeParents'), function(item) { return item.indexOf('.') == -1; }).join()}})
Documentation: https://developers.google.com/drive/v3/reference/files/list.
-
fields:
- [Create] action
-
Endpoint URL address:
https://www.googleapis.com/{{=(item.content != null) ? 'upload/' : ''}}drive/v3/files.The following request parameters will be automatically processed:
-
uploadType:
multipart -
supportsAllDrives:
true -
content:
{{=item.content}}
Documentation: https://developers.google.com/drive/v3/reference/files/create.
-
uploadType:
- [Update] action
-
Endpoint URL address:
https://www.googleapis.com/{{=(item.content != null) ? 'upload/' : ''}}drive/v3/files/{{=item.id}}.The following request parameters will be automatically processed:
-
uploadType:
multipart -
supportsAllDrives:
true -
content:
{{=item.content}}
Documentation: https://developers.google.com/drive/v3/reference/files/update.
-
uploadType:
- [Delete] action
-
Endpoint URL address:
/files/{{=item.id}}.The following request parameters will be automatically processed:
-
supportsAllDrives:
true
Documentation: https://developers.google.com/drive/v3/reference/files/delete.
-
supportsAllDrives:
The resource includes the fields from the File template.
- content
Data type: DT_IMAGE The value is gathered from
/files/{{=item.id}}address.- addParents
Template: ShortText.
- removeParents
Template: ShortText.
- [Read] action
-
Endpoint URL address:
/files/{{=Google.GDrive.getFileId(parameters.fileId)}}/permissions.
The result is extracted from:{{=response.permissions}}.The action has the following user-defined parameters:
- fileId: Required. Specifies file identifier.
The following request parameters will be automatically processed:
-
fields:
nextPageToken, permissions({{=_.without(parameters.fields, 'fileId').join()}}) -
_includeUserParameters:
{{=parameters}}
Documentation: https://developers.google.com/drive/v3/reference/permissions/list.
- [Create] action
-
Endpoint URL address:
/files/{{=item.fileId}}/permissions.Documentation: https://developers.google.com/drive/v3/reference/permissions/create.
- [Update] action
-
Endpoint URL address:
/files/{{=item.fileId}}/permissions/{{=item.id}}.Documentation: https://developers.google.com/drive/v3/reference/permissions/update.
- [Delete] action
-
Endpoint URL address:
/files/{{=item.fileId}}/permissions/{{=item.id}}.Documentation: https://developers.google.com/drive/v3/reference/permissions/delete.
The resource includes the fields from the Permission template.
- fileId
Template: ShortText.
- [Read] action
-
Endpoint URL address:
/files/{{=Google.GDrive.getFileId(parameters.fileId)}}/comments/{{=parameters.commentId}}/replies.
The result is extracted from:{{=response.replies}}.The action has the following user-defined parameters:
- fileId: Required. Specifies file identifier.
- commentId: Required. Specifies comment identifier.
The following request parameters will be automatically processed:
-
fields:
nextPageToken, replies({{=_.without(parameters.fields, 'fileId', 'commentId').join()}}) -
_includeUserParameters:
{{=parameters}}
Documentation: https://developers.google.com/drive/v3/reference/replies/list.
- [Create] action
-
Endpoint URL address:
/files/{{=item.fileId}}/comments/{{=item.commentId}}/replies.Documentation: https://developers.google.com/drive/v3/reference/replies/create.
- [Update] action
-
Endpoint URL address:
/files/{{=item.fileId}}/comments/{{=item.commentId}}/replies/{{=item.id}}.Documentation: https://developers.google.com/drive/v3/reference/replies/update.
- [Delete] action
-
Endpoint URL address:
/files/{{item.fileId}}/comments/{{=item.commentId}}/replies/{{=item.id}}.Documentation: https://developers.google.com/drive/v3/reference/replies/delete.
The resource includes the fields from the Reply template.
- fileId
Template: ShortText.
- commentId
Template: ShortText.
- [Read] action
-
Endpoint URL address:
/files/{{=Google.GDrive.getFileId(parameters.fileId)}}/revisions.
The result is extracted from:{{=response.revisions}}.The action has the following user-defined parameters:
- fileId: Required. Specifies file identifier.
The following request parameters will be automatically processed:
-
fields:
nextPageToken, revisions({{=_.without(parameters.fields, 'fileId').join()}}) -
_includeUserParameters:
{{=parameters}}
Documentation: https://developers.google.com/drive/v3/reference/revisions/list.
- [Update] action
-
Endpoint URL address:
/files/{{=item.fileId}}/revisions/{{=item.id}}.Documentation: https://developers.google.com/drive/v3/reference/revisions/update.
- [Delete] action
-
Endpoint URL address:
/files/{{=item.fileId}}/revisions/{{=item.id}}.Documentation: https://developers.google.com/drive/v3/reference/revisions/delete.
- id
A key field. Template: ShortText.
- kind
Template: ShortText. A read-only field.
- mimeType
Template: ShortText. A read-only field.
- modifiedTime
Template: DateTime. A read-only field.
- keepForever
Data type: DT_BOOL
- published
Data type: DT_BOOL
- publishAuto
Data type: DT_BOOL
- publishedOutsideDomain
Data type: DT_BOOL
- lastModifyingUser
Template: User. A read-only field.
- originalFilename
Template: ShortText. A read-only field.
- md5Checksum
Template: ShortText. A read-only field.
- size
Data type: DT_I8 A read-only field.
- fileId
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/drives.
The result is extracted from:{{=response.drives}}.Documentation: https://developers.google.com/drive/api/v3/reference/drives/list.
- [Create] action
-
Endpoint URL address:
/drives.The following request parameters will be automatically processed:
-
requestId:
{{=Math.random()}}
Documentation: https://developers.google.com/drive/api/v3/reference/drives/create.
-
requestId:
- [Update] action
-
Endpoint URL address:
/drives/{{=item.id}}.Documentation: https://developers.google.com/drive/api/v3/reference/drives/update.
- [Delete] action
-
Endpoint URL address:
/drives/{{=item.id}}.Documentation: https://developers.google.com/drive/api/v3/reference/drives/delete.
- id
A key field. Template: ShortText.
- kind
Template: ShortText.
- name
Template: ShortText.
- themeId
Template: ShortText.
- colorRgb
Template: ShortText.
- backgroundImageFile
Field components:
-
Uses template:
ShortText. - Contains the following components: id, xCoordinate, yCoordinate, width.
-
Uses template:
- backgroundImageLink
Template: ShortText.
- capabilities
Field components:
- Contains the following components: canAddChildren, canChangeCopyRequiresWriterPermissionRestriction, canChangeDomainUsersOnlyRestriction, canChangeDriveBackground, canChangeDriveMembersOnlyRestriction, canComment, canCopy, canDeleteChildren, canDeleteDrive, canDownload, canEdit, canListChildren, canManageMembers, canReadRevisions, canRename, canRenameDrive, canShare, canTrashChildren.
- createdTime
Template: DateTime. A read-only field.
- hidden
Data type: DT_BOOL
- restrictions
Field components:
- Contains the following components: adminManagedRestrictions, copyRequiresWriterPermission, domainUsersOnly, driveMembersOnly.
- [Read] action
-
The result is paginated.The following request parameters will be automatically processed:
-
pageToken:
{{=parameters.iterator}} -
pageSize:
{{=parameters.batchSize}} -
_includeUserParameters:
{{=parameters}}
-
pageToken:
- [Create] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
application/json:
{{=item}}
-
application/json:
- [Update] action
-
The action uses PATCH method.The following request parameters will be automatically processed:
-
application/json:
{{=item}}
-
application/json:
- [Delete] action
-
The action uses DELETE method.
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- User
Field components:
-
Uses template:
ShortText. - Contains the following components: kind, displayName, photoLink, me, permissionId, emailAddress.
-
Uses template:
- Permission
Field components:
-
Uses template:
ShortText. - Contains the following components: id, kind, type, emailAddress, domain, role, allowFileDiscovery, displayName, photoLink, expirationTime.
-
Uses template:
- File
Field components:
-
Uses template:
ShortText. - Contains the following components: id, kind, name, mimeType, description, starred, trashed, explicitlyTrashed, parents, properties, appProperties, spaces, version, webContentLink, webViewLink, iconLink, thumbnailLink, viewedByMe, viewedByMeTime, createdTime, modifiedTime, modifiedByMeTime, sharedWithMeTime, sharingUser, owners, lastModifyingUser, shared, ownedByMe, capabilities, viewersCanCopyContent, writersCanShare, permissions, folderColorRgb, originalFilename, fullFileExtension, fileExtension, md5Checksum, size, quotaBytesUsed, headRevisionId, contentHints, imageMediaMetadata, videoMediaMetadata, isAppAuthorized.
-
Uses template:
- Reply
Field components:
-
Uses template:
ShortText. - Contains the following components: id, kind, createdTime, modifiedTime, author, htmlContent, content, deleted, action.
-
Uses template:
Knowledge Base
- Where can I find the documentation for the Google Drive Connection?
- How to upload an Excel file to Google Drive as a Google Spreadsheet
- How to manually generate a Google Services Token?
Related scripts
What's New
- Fixed: File rename/move not working.
- New: Error when browsing a folder containing items with double quote (") characters (Thank you, Rob).
- New: Improved parameter entry.
- Fixed: Unable to access files on shared drives (Thank you, Steve).
- Fixed: Failed with error "Maximum number of named properties reached." when a folder contains more than 16000 files (Thank you, Dave).
- New: Support for shared drives.
- New: Updated to use new OAuth2 2.0 authorization addresses.
- Fixed: Failed with error "Not Found" when trying to overwrite an existing file.
- Fixed: 'Create remote directory' action in File Transfer Task will now avoid the creation of folders with same name.
- Fixed: Failed with error "Invalid field selection fileId" when reading from Permission resource (Thank you, Karen).
- Fixed: Upload action didn't update the existing file but created a new file.
- New: Introduced connection.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.