SharePoint

EDIS Online User Guide – SharePoint

EDIS has built in support for seamless querying of SharePoint lists, and uploading & downloading files to document libraries. This makes EDIS a great tool for teams that need to manage data through files on SharePoint libraries. EDIS can download the files and load them to the SQL Server with a combination of using the SharePoint download task and data transfer task.

Remarks
When a new SharePoint connection is created, it is by default accessible to all users in the EDIS_Role. To lock to a specific ID, group, or set of ID’s, see section Multi-Tenant Support.

SharePoint Connection Setup
To run an EDIS SharePoint task, you first need to configure a SharePoint service ID to connect to SharePoint. Below is an example to setup a SharePoint connection.

Procedure: SSISDB.EDIS.usp_config_sharepoint_id

Parameters

Name Description Default Value
@svc_id SharePoint service ID alias to use to run EDIS SharePoint tasks None; Required
@user_id The user ID to use to connect to SharePoint None; Required
@password The password associated with the User ID to connect to SharePoint None; Required
@is_sharepoint_online Flag indicating if the SharePoint server is on-prem or located on Office 365 online. If the SharePoint server is on-prem, set this value to 0. If it is on Office 365, set this value to 1. None; Required

Example: This example creates a new SharePoint connection with alias “SVC_CTS_SALES”. After running this procedure once, the alias can now be used with any EDIS SharePoint task.

EXEC SSISDB.EDIS.usp_config_sharepoint_id

 @svc_id = 'SVC_CTS_-- USER'

,@user_id = 'admin@contoso_sales.com'

,@password = '_svc_sls_admin_$sdnd1#x!'

,@is_sharepoint_online = 1

 

SharePoint Read List Task
This tasks reads data from a SharePoint list and loads it to a global temp table.

Procedure: SSISDB.EDIS.usp_sharepoint_list_read

Parameters

Name Description Default Value
@url SharePoint URL that hosts the list None; Required
@svc_id The service ID used to connect to sharepoint None; Required
@output_tbl_nm The global temp table that you want to output the results to. You provide a name, and EDIS will build the table on the fly based on the columns in the SharePoint list None; Required

Example: This example reads from a list named “sls_feedback”. The results are outputted to table ##sls_feedback

EXEC SSISDB.EDIS.usp_sharepoint_list_read

 @url = 'https://contoso.sharepoint.com/data_team'

,@svc_id = 'SVC_CTS_-- USER'

,@list_nm = 'sls_feedback'

,@output_tbl_nm = '##sls_feedback'

,@crt_dest_tbl = 1;

 

SharePoint Write List Task
This task writes data to a SharePoint list. You provide a source query and EDIS matches the source query columns to the SharePoint list based on the column names.

Procedure: SSISDB.EDIS.usp_sharepoint_list_write

Parameters

Name Description Default Value
@url SharePoint URL that hosts the list None; Required
@svc_id The service ID used to connect to sharepoint None; Required
@list_nm Name of the SharePoint List to query None; Required
@src_qry The source query that will retrieve the data from the host SQL Server None; Required
@batch_size Batch size to load the data. Since SharePoint servers are sized up differently per organization, it is recommended to test a few loads with varying batch sizes to see which one achieves the fastest throughput rate. The default batch size is 50 50

Example: This example writes to list “sls_upload”. Columns from the source query are matched to the list based on their name.

EXEC SSISDB.EDIS.usp_sharepoint_list_write

 @url = 'https://contoso.com/sales_team'

,@svc_id = 'SVC_CTS_-- USER'

,@list_nm = 'sls_upload'

,@src_qry = '

    select top 1000 sales_id, sales_dt, usr_id, sls_amt

    from adventureworks.sales.sls

'

,@batch_size = 30

 

SharePoint Purge List Task
This task purges all data in a SharePoint list. This is similar to a TRUNCATE TABLE statement in SQL Server, considering that it deletes all items in a list.

Procedure: SSISDB.EDIS.usp_sharepoint_list_purge

Parameters

Name Description Default Value
@url SharePoint URL that hosts the list None; Required
@svc_id The service ID used to connect to sharepoint None; Required
@list_nm Name of the SharePoint List to purge None; Required
@batch_size Batch size to load the data. Since SharePoint servers are sized up differently per organization, it is recommended to test a few loads with varying batch sizes to see which one achieves the fastest throughput rate. The default batch size is 50 50

Example: This example deletes all rows in sharepoint list “sls_upload”.

EXEC SSISDB.EDIS.usp_sharepoint_list_purge

 @url = 'https://contoso.com/sales_team'

,@svc_id = 'SVC_CTS_-- USER'

,@list_nm = 'sls_upload'

,@batch_size = 30

 

SharePoint List Get Metadata Task
This task gets all column names, data types and other various attributes from a SharePoint list. This is helpful when you need to get a list of column names or understand the layout of a SharePoint list.

Procedure: SSISDB.EDIS.usp_sharepoint_list_get_metadata

Parameters

Name Description Default Value
@url SharePoint URL that hosts the list None; Required
@svc_id The service ID used to connect to sharepoint None; Required
@list_nm Name of the SharePoint List to you want to get the metadata for None; Required
@output_tbl_nm Name of the global temporary table to output the list meta to. This table will be created on the fly and columns automatically populated. None; Required

Example: This example gets the column names and data types from SharePoint list “sls_upload” and loads it to temp table ##sls_list_meta.

EXEC SSISDB.EDIS.usp_sharepoint_list_get_metadata

 @url = 'https://contoso.com/sales_team'

,@svc_id = 'SVC_CTS_-- USER'

,@list_nm = 'sls_upload'

,@output_tbl_nm = '##sls_list_meta'

 

SharePoint Document Library Download Files
This task downloads files from a SharePoint Document Library.

Procedure: SSISDB.EDIS.usp_sharepoint_library_download_files

Parameters

Name Description Default Value
@url SharePoint URL that hosts the list None; Required
@svc_id The service ID used to connect to sharepoint None; Required
@doc_lib_nm Name of the SharePoint Document Library None; Required
@doc_lib_sub_folder Sub folder to look in for the files, if applicable Empty String
@file_crit File criteria to search for when downloading files from SharePoint. This argument accepts wildcard characters. i.e. setting this variable to “*.txt” will download all files with a .txt extension None; Required
@local_folder_path The path of the local folder to download the SharePoint files to None; Required

Example: This example downloads all files ending in “.xls” from SharePoint document library “data_bin”, folder “archives”.

EXEC SSISDB.EDIS.usp_sharepoint_library_download_files

 @url = 'https://contoso.com/sales_team'

,@svc_id = 'SVC_CTS_-- USER'

,@doc_lib_nm = 'data_bin'

,@doc_lib_sub_folder = '/archives/'

,@file_crit = '*.xls'

,@local_folder_path = 'C:\temp\data_raw_landing\'

 

SharePoint Document Library Upload Files
This task uploads files toa SharePoint Document Library.

Procedure: SSISDB.EDIS.usp_sharepoint_library_upload_files

Parameters

Name Description Default Value
@url SharePoint URL that hosts the list None; Required
@svc_id The service ID used to connect to sharepoint None; Required
@doc_lib_nm Name of the SharePoint Document Library None; Required
@doc_lib_sub_folder Sub folder you want to upload the files to, if applicable Empty String
@file_crit File criteria to search for when uploading files from a local folder. This argument accepts wildcard characters. i.e. setting this variable to “*.txt” will upload all files from the local folder path with a .txt extension None; Required
@local_folder_path The path of the local folder to scan for files to upload to the SharePoint document library None; Required

Example: This example uploads all files ending in “.txt” to SharePoint document library “data_bin”.

EXEC SSISDB.EDIS.usp_sharepoint_library_upload_files

 @url = 'https://contoso.com/sales_team'

,@svc_id = 'SVC_CTS_-- USER'

,@doc_lib_nm = 'data_bin'

,@file_crit = '*.txt'

,@local_folder_path = 'C:\temp\data_raw_landing\'

 

SharePoint Document Library Get File List
This task gets a list of all files in a SharePoint document library and loads the results to a global temporary table.

Procedure: SSISDB.EDIS.usp_sharepoint_library_get_file_list

Parameters

Name Description Default Value
@url SharePoint URL that hosts the list None; Required
@svc_id The service ID used to connect to sharepoint None; Required
@doc_lib_nm Name of the SharePoint Document Library None; Required
@file_crit File criteria to search for when scanning the document library. This argument accepts wildcard characters. i.e. setting this variable to “*.txt” will only list files from the SharePoint document library with a .txt extension. * (All files)
@output_tbl_nm The name of the global temporary table you want to output the file results list to i.e. “##sp_files”. This table gets generated on the fly. None; Required

Example: This example gets a list of all files in SharePoint document library “data_bin”.

EXEC SSISDB.EDIS.usp_sharepoint_library_get_file_list

 @url = 'https://contoso.com/sales_team'

,@svc_id = 'SVC_CTS_-- USER'

,@doc_lib_nm = 'data_bin'

,@file_crit = '*'

,@output_tbl_nm= '##sp_files_data_bin'