- Database
- Email
- Excel
- Flat Files
- FTP
- Google BigQuery
- Local Files Tasks
- Merging Data
- Powershell Task
- Process Task
- Raw Files
- SharePoint Lists
- SharePoint Documents
- SQL Command
- Web Tasks
- Zipping Files
Importing and exporting data from a database with EDIS is quick and easy. And when you need to setup connections to other databases, EDIS supports ODBC, OLEDB, and ADO.Net connections. What makes development even easier is once you setup a connection, it can be accessed using a named alias, which means that developers no longer need user ID’s and passwords. The DBA just needs to provide them the named alias. Below are some code examples that setup ODBC and OLEDB connections and import/export data.
Demo 1
Demo 2 - Logging in with alternate Windows ID
Setting up an ODBC connection to Oracle server
EXEC EDIS.usp_config_db_conn
@server_id = 'ORA_PR_SLS', @server_provider = 'ODBC'
,@server_platform = 'ORACLE'
,@cn_str = 'DSN=ORA_PR_SLS_SET1; UID = pUser; PWD = $s^hg_!rx5'
;
Setting up an OLEDB connection to SQL Server with Windows Authentication
EXEC EDIS.usp_config_db_conn_mssql
@server_id = 'SQL_SLS_REP_DB', @server_instance = 'MDDTX5\DEV01'
,@use_integrated_security = 1
,@windows_auth_user_id = 'MDDTx5\_svc_user_d1'
,@windows_auth_password = 'x$nbx_rr38B4'
;
In this example, EDIS imports regional sales data from an Oracle server with named alias ORA_PR_SLS to destination table production.dbo.sales_rgn_stats on the host SQL Server
EXEC EDIS.usp_run_data_transfer
@src_sys = 'ORA_PR_SLS', @dest_sys = @@SERVERNAME
,@src_qry =
'
SELECT RGN_ID, SUM(SALES_AMT) as SALES
FROM PR_SLS.WKLY_SLS_BY_RGN
GROUP BY RGN_ID
'
,@dest_tbl = 'production.dbo.sales_rgn_stats'
;
In this example, EDIS is exporting data from the host SQL Server to an Oracle server with named alias ORA_PR_SLS
EXEC EDIS.usp_run_data_transfer
@src_sys = @@servername, @dest_sys = 'ORA_PR_SLS'
,@src_qry =
'
SELECT LOCATION_ID, SUM(SALES) AS SALES
FROM sales.dbo.weekly_sales_by_loc
GROUP BY LOCATION_ID
'
,@dest_tbl = 'PR_SLS.SLS_STG_LOC
,@batch_size = 100000
;
Sending an email with EDIS is very quick and easy. Additionally, EDIS provides the ability to attach a query results set as an Excel file or text file with ease. You simply just provide the query you want to run with the file name you want it called, and EDIS takes care of the rest in a seamless manner. But what if you are sending a large file? No problem! EDIS can compress the query attachment as a zip file all by setting a simple parameter flag.
Below are examples of configuring an email service ID and sending emails with EDIS.
This example creates an email service ID to be used when sending an email. This is similar to how you would setup an email account in a program such as Microsoft Outlook.
EXEC EDIS.usp_config_email_acct @svc_id = 'SALES_SUPPORT_EMAIL'
,@smtp_host = = 'smtp.office365.com'
,@use_ssl = 1
,@sender_email_address = 'sales_support@contoso.com'
,@port = 587
,@user_id = 'sales_admin@contoso.com'
,@pwd = '$xYnRTv_b15R3'
;
EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'
,@cc = 'greg@contoso.com'
,@subject = 'Weekly Sales Review Data Notification'
,@body = 'Weekly sales review data has been published and is ready for review
,@svc_id = 'SALES_SUPPORT_EMAIL'
;
EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'
,@cc = 'greg@contoso.com'
,@subject = 'Weekly Sales Alert'
,@body = 'The weekly sales data batch process encountered an error and needs to be addressed.
,@is_high_pri = 1
,@svc_id = 'SALES_SUPPORT_EMAIL'
;
EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'
,@cc = 'greg@contoso.com'
,@subject = 'Weekly Sales Report'
,@body = 'Attached please find the weekly regional sales report.'
,@qry = '
SELECT RGN_ID, SUM(SALES) AS SALES_AMT
FROM SALES_DB.dbo.RGN_SALES_DATA
WHERE FSCL_WK = 7 AND FSCL_YR = 2016
GROUP BY RGN_ID
'
,@qry_attachment_path = 'C:\temp\rgn_sales.xlsx'
,@svc_id = 'SALES_SUPPORT_EMAIL'
;
EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'
,@cc = 'greg@contoso.com'
,@subject = 'Weekly Sales Report'
,@body = 'Attached please find the weekly regional sales report.'
,@qry = '
SELECT RGN_ID, SUM(SALES) AS SALES_AMT
FROM SALES_DB.dbo.RGN_SALES_DATA
WHERE FSCL_WK = 7 AND FSCL_YR = 2016
GROUP BY RGN_ID
'
,@qry_attachment_path = 'C:\temp\rgn_sales.txt'
,@compress_qry_attachment = 1
,@svc_id = 'SALES_SUPPORT_EMAIL'
;
EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'
,@cc = 'greg@contoso.com'
,@subject = 'Weekly Sales Report'
,@body = 'Attached please find the weekly regional sales report.'
,@file_attachment_path = 'C:\temp\sales.xlsx'
,@svc_id = 'SALES_SUPPORT_EMAIL'
;
Getting data in and out of Excel from SQL Server has never been known as an easy task. There are numerous bugs and issues when importing excel sheets such as mixed datatype columns showing nulls for some of the rows due to Excel only scanning the first 8 rows. Additionally, when you export data in SSIS to Excel, numbers get formatted as text. And when your column is wider than 255 characters, it gets automatically truncated without you knowing.
Because of these issues, we took a different approach and built our own Excel engine to import and export data for Excel the right way. Additionally, we went a step further and added formatting on data exports such as enabling auto filters, auto-fitting columns, and bolding the header row. And when exporting, if the destination file does not exist, EDIS will create it for you automatically, which means you know longer need to manage templates.
Our EDIS import/export engine supports both Excel 97 (xls) and Excel 07 (xlsx, xlsm).
This example imports sheet "sales_raw" from Excel file sales.xlsx and transfers the data to table staging.dbo.sales_raw on the host SQL Server. Also, by using the @crt_dest_tbl parameter, EDIS creates the destination table automatically.
EXEC EDIS.usp_excel_import_data @file_path = 'C:\temp\sales.xlsx'
,@sheet_nm = 'sales_raw'
,@dest_tbl_nm = 'staging.dbo.sales_raw'
,@crt_dest_tbl = 1
;
This example exports data to file sales_sum.xlsx, sheet "sales summary". Additionally, it bolds the header row, auto fits the columns, and applies an autofilter to the sheet.
Watch demo now
See code below
EXEC EDIS.usp_excel_export_data @file_path = 'C:\temp\sales_sum.xlsx'
,@src_qry =
'
SELECT LOCATION_ID, SUM(SALES) AS SALES
FROM sales.dbo.weekly_sales_by_loc
GROUP BY LOCATION_ID
'
,@sheet_nm = 'sales summary'
,@bold_headers = 1
,@autofit_cols = 1
,@use_autofilter = 1
;
This example adds a new worksheet to file sales_sum.xlsx called "sales detail".
EXEC EDIS.usp_excel_add_worksheet @file_path = 'C:\temp\sales_sum.xlsx'
,@sheet_nm = 'sales detail'
;
This example clears worksheet "sales detail".
EXEC EDIS.usp_excel_clear_worksheet @file_path = 'C:\temp\sales_sum.xlsx'
,@sheet_nm = 'sales detail'
;
This example renames worksheet "sales detail" to "sales detail old".
EXEC EDIS.usp_excel_rename_worksheet @file_path = 'C:\temp\sales_sum.xlsx'
,@sheet_nm = 'sales detail'
,@new_sheet_nm = 'sales detail old'
;
Flat files can be very tricky to work with. But with EDIS, it becomes very simple and allows both importing and exporting quick and easy. And just like our Excel tasks, if the destination flat file does not exist, EDIS will create it for you automatically and append headers. Additionally, our flat file import/export engine supports multi-character delimiters and row terminators, as well as text qualifiers.
But we also took it a step further. Besides handling delimited files, EDIS can also import fixed width and ragged right files
This example imports a tab delimited flat file to table ##sales_raw.
EXEC EDIS.usp_run_data_transfer @src_sys = 'FLATFILE', @dest_sys = @@servername
,@file_path = 'C:\temp\sales_raw.txt'
,@col_delim = '{tab}'
,@row_term = '{lf}'
,@text_qual = '"'
,@dest_tbl = '##sales_raw'
,@crt_dest_tbl = 1
;
This example exports data to a csv file. And if the CSV file does not exist, EDIS will create it automatically.
Watch demo now
See code below
EXEC EDIS.usp_run_data_transfer @src_sys = @@SERVERNAME, @dest_sys = 'FLATFILE'
,@file_path = 'C:\temp\sales_raw.csv'
,@col_delim = ','
,@text_qual = '"'
;
This example imports data from a fixed width text file to table ##sales_raw. What makes the fixed width import process easy is all you have to do is specify the break points in a list as scene below.
EXEC EDIS.usp_run_data_transfer @src_sys = 'FLATFILE', @dest_sys = @@servername
,@file_path = 'C:\temp\sales_raw.txt'
,@fixed_width_row_len = 34
,@fixed_width_intervals = '7;23'
,@dest_tbl = '##sales_raw'
,@crt_dest_tbl = 1
;
This example imports data from a ragged right text file to table ##sales_raw. To import ragged right, you need to specify the break points for the fixed width columns as well as the max column length for the last column so that EDIS knows where to break the last column.
EXEC EDIS.usp_run_data_transfer @src_sys = 'FLATFILE', @dest_sys = @@servername
,@file_path = 'C:\temp\sales_raw.txt'
,@max_col_width = 85
,@ragged_right_intervals = '7;18;32'
,@dest_tbl = '##sales_raw'
,@crt_dest_tbl = 1
;
Working with FTP servers in EDIS is easy. Whether you need to connect to a standard FTP site, Secure FTP, or FTP over SSL, EDIS can perform the task with just a few lines of code. EDIS also supports wildcard characters for the file set you want to upload or download, which allows for greater flexibility when working with FTP directories.
To connect to an FTP site, you first need to load a credentials set. Once this is created, you can use the named alias to connect to the FTP site quick and easy.
The example below creates a new FTP credentials set with named alias SVC_FTP_CTS. This named alias can be later used to connect and perform FTP tasks with EDIS.
EXEC EDIS.usp_config_ftp_id
@ftp_id = 'SVC_FTP_CTS'
,@uid = 'admin@contoso.com'
,@pwd = '$csl_*vnT31'
;
The example below downloads files from an FTP site where the file name starts with "sales" and ends with ".txt". The use of the asterisks (*) allows for wildcard matching patterns. Additionally, EDIS creates a SFTP connection by using the parameter @use_ssh.
EXEC EDIS.usp_ftp_download_files
@ftp_id = 'SVC_FTP_CTS'
,@ftp_srvr = 'contoso.com'
,@ftp_dir = '/pub/sales/'
,@local_dir = 'C:\temp\sales_import\'
,@file_crit = 'sales*.txt'
,@use_ssh = 1
;
The example below uploads files from local directory "C:\temp\sales_import\" to FTP directory "/pub/sales/processed". By leveraging the astericks (*) wildcard pattern, EDIS will upload any file that starts with "sales_p" and ends with ".txt".
EXEC EDIS.usp_ftp_upload_files
@ftp_id = 'SVC_FTP_CTS'
,@ftp_srvr = 'contoso.com'
,@ftp_dir = '/pub/sales/processed/'
,@local_dir = 'C:\temp\sales_import\'
,@file_crit = 'sales_p*.txt'
,@use_ssh = 1
The example below deletes files from an FTP directory that end with ".csv".
EXEC EDIS.usp_ftp_delete_files
@ftp_id = 'SVC_FTP_CTS'
,@ftp_srvr = 'contoso.com'
,@ftp_dir = '/pub/sales/processed/'
,@file_crit = '*.csv'
EDIS has the simplest tools needed to import and export data to Google BigQuery. Nuances like preparing the data for export and handling conversions are done automatically so that you can get your data in and out of BigQuery fast and easy. And EDIS does more than just import and export data. You can merge datasets, create new tables, drop tables, and append data in BigQuery all with simple and easy to use EDIS tasks. To see examples of all EDIS BigQuery tasks, please see the online user's guide page for BigQuery.
To connect to Google BigQuery, you need to create a Google Service account on Google Cloud. Once complete, you need to download the JSON X509 certificate credentials file from Google. After that is complete, use the procedure below to load the credentials to EDIS and create a named alias for access to BigQuery. You will need the project name and project ID that the service account needs to connect to.
EXEC EDIS.usp_config_bigquery_id @bq_svc_id = 'BQ_SVC'
,@project_nm = 'sales-team-proj'
,@project_id = '12343252972729'
,@creds_file_path = 'C:\temp\svc_sls_creds.json'
;
The example below imports 1000 rows from the 'sales_by_rgn' table and loads it to destination table on the host SQL Server. And table ##sales_raw is automatically created by specifying parameter @crt_dest_tbl.
EXEC EDIS.usp_bigquery_import_data
@bq_svc_id = 'BQ_SVC'
,@src_qry =
'
SELECT RGN, SUM(SALES) AS SALES
FROM `pub-sales.SALES.sales_by_rgn`
WHERE SALES_DT BETWEEN ''2016-02-01'' AND ''2016-03-01''
GROUP BY RGN
LIMIT 1000
'
,@dest_tbl_nm = '##sales_raw'
,@crt_dest_tbl = 1
;
The example below exports data from Server SLS_P1 to BigQuery dataset SALES, table SALES_RGN_AND_ONHANDS. What makes the BigQuery export task very easy is that it will automatically create the destination table for you if it does not exist. And you can do advanced things like tell the task to drop the existing table or merge the results if you are needing to do an incremental load. The BigQuery export task can also load data from any DB Service ID created in EDIS using parameter @src_svc_id. The default source is the host SQL Server for EDIS.
EXEC EDIS.usp_bigquery_export_data
@bq_svc_id = 'BQ_SVC'
@src_svc_id = 'SLS_P1'
,@src_qry =
'
SELECT RGN_ID, SALES, OH_QTY
FROM adventureworks.sales.sales_and_onhands
WHERE rec_dt BETWEEN ''2016-02-01'' AND ''2016-03-01''
'
,@dataset_nm = 'SALES'
,@dest_tbl_nm = 'SALES_RGN_AND_ONHANDS'
,@drop_existing = 1
,@work_dir = 'C:\temp\bq_load\'
;
EDIS has tools to perform the most common local file tasks. Below are examples.
EXEC EDIS.usp_get_loc_file_list
@folder_path = 'C:\temp\'
,@file_crit = '*'
,@show_details = 1
,@tmp_tbl_nm = '##file_list'
;
SELECT *
FROM ##file_list
EXEC EDIS.usp_delete_loc_files
@folder_path = 'C:\temp\'
,@file_crit = '*.xlsx'
;
EXEC EDIS.usp_rename_loc_file
@curr_file_path = 'C:\temp\sales.xlsx'
,@new_file_path = 'C:\temp\sales_June.xlsx'
;
EXEC EDIS.usp_copy_loc_file
@src_file_path = 'C:\temp\sales.xlsx'
,@tgt_folder_path = 'C:\archive\sales\'
;
EXEC EDIS.usp_create_loc_dir
@folder_path = 'C:\archive\2016\june'
;
EXEC EDIS.usp_delete_loc_dir
@folder_path = 'C:\archive\2016\june'
;
Never write an insert, update, delete, or merge statement ever again.
The EDIS merge task takes data from a source table and merges to a target table with just two parameters. Instead of spending hours writing a lengthy update statement, just use EDIS.usp_merge and be done in seconds.
GO FROM WRITING THIS
MERGE SALES_DB.dbo.wkly_sales as TGT
using #sales_raw as SRC
ON TGT.loc = SRC.LOC
WHEN MATCHED THEN UPDATE
SET tgt.sales_amt = src.sales_amt, tgt.last_upd_ts = src.last_upd_ts, tgt.sys_rgn_id = src.sys_rgn_id
WHEN NOT MATCHED THEN
INSERT (loc, sales_amt, last_upd_ts, sys_rgn_id)
VALUES (src.loc, src.sales_amt, src.last_upd_ts, src.sys_rgn_id)
TO WRITING THIS!
EXEC SSISDB.EDIS.usp_merge 'SALES_DB.dbo.wkly_sales', '#sales_raw';
Watch demo now
How does this work?
The merge task first looks at the target table for the primary key so that it knows how to join the target and the source. And if your table does not have a primary key, you can supply the key columns for the merge task to match against with just a simple parameter. Non-key columns from the source and destination are matched based on their names. And EDIS makes it very simple in that you don't have to worry about case sensitivity. Additionally, special columns such as identity seeds have a parameter in the merge task to allow inserts on identity values if you need to override. Once the merge command is set, it is wrapped in a transaction in case an error occurs during execution so that the table returns to the previous state.
And beyond the simplicity of this task, the merge task also provides advanced error handling for duplicate rows and data conversion/truncation issues. Instead of getting a vague error statement saying "Error: String or binary would be truncated", with usp_merge you get this:
usp_merge error: Column Truncation error has been detected. This occurs when a column in the source table is longer than the column in the destination table.
Below is a list of column(s) that that have a longer width in the source.
| Column | Source Type | Target Type | Suggested Target Table DDL Alter Statement |
| cmts | varchar(50) | varchar(5) | ALTER TABLE SALES_HDR ALTER COLUMN [cmts] varchar(50) NOT NULL |
This provides you a solution that is immediately actionable and saves you a ton of time.
This example merges source table #data to target table SALES_DB.dbo.sales_by_wk.
EXEC EDIS.usp_merge_data @target = 'SALES_DB.dbo.sales_by_wk'
,@source = '#data'
;
EDIS has a tool to enable you to run powershell scripts directly in SQL by passing in a parameter with the script. Below is an example.
EXEC EDIS.usp_run_powershell_script
@script = 'get-childitem -Path "C:\temp\"'
,@output_tbl_nm= '##files'
;
SELECT * FROM ##files
EDIS has a tool to enable you to run a command shell process. A major difference though between our tool and xp_cmdshell is our tool runs under the context of the caller, whereas xp_cmdshell runs under the service account of SQL Server. This adds an extra layer of security to ensure the user running this tool has the necessary permissions to perform the task. Below is an example.
EXEC EDIS.usp_run_process_task
@process_nm = 'cmd.exe'
,@args = '/c whoami.exe'
,@output_tbl_nm = '##users'
;
SELECT * FROM ##users
SSIS supports a native file type called RAW, which is the fastest and most efficient file type to move data to and from SQL Server. EDIS also supports this file type and makes importing and exporting to raw files simple. Below are examples.
This example imports data from a raw file to the host SQL Server temp table ##sls_raw.
EXEC EDIS.usp_run_data_transfer @src_sys = 'RAW', @dest_sys = @@servername
,@file_path = 'C:\temp\sales.raw'
,@dest_tbl_nm = '##sales_raw'
,@crt_dest_tbl = 1
;
This example exports data to a raw file. If the file does not exist, EDIS automatically creates it.
EXEC EDIS.usp_run_data_transfer @src_sys = @@servername, @dest_sys = 'RAW'
,@file_path = 'C:\temp\sales.raw'
,@src_qry = 'SELECT WK_NBR, RGN_ID, SUM(SALES_AMT) FROM sales_db.dbo.sales_wk_rgn GROUP BY WK_NBR, RGN_ID'
;
Reading and writing to SharePoint lists with SQL Server should be easy. But the tools that exist today are not easy to configure. Additionally, many of them have not been updated to handle connecting to SharePoint lists on Office 365. So, just like our own Excel engine, we built our own SharePoint engine that can read from on-prem SharePoint servers as well as Office 365 SharePoint servers.
Watch Demo Now
Setting up a SharePoint connection is quick and easy. It uses the same principals as setting up database connections where you access the source via a named alias. Below is an example that sets up a SharePoint connection. This SharePoint server is also located on Office 365, which is specified using the parameter @is_sharepoint_online.
EXEC EDIS.usp_config_sharepoint_id
@svc_id = 'SHAREPOINT_SALES_SVC'
,@user_id = 'admin@contoso_sales.com'
,@password = '_svc_sls_admin_$sdnd1#x!'
,@is_sharepoint_online = 1
;
The example below retrieves the column listing metadata from SharePoint list "avt_persons" and outputs the results to table ##list_meta. The table ##list_meta is autogenerated when the procedure runs.
EXEC EDIS.usp_sharepoint_list_get_metadata
@url = 'https://netorgft762028.sharepoint.com/EDIS_DEV/'
,@svc_id = 'SHAREPOINT_SALES_SVC'
,@list_nm = 'avt_persons'
,@output_tbl_nm = '##list_meta'
;
The example below reads data from SharePoint list "avt_persons" and outputs the results to table ##avt_persons_sharepoint. The table ##avt_persons_sharepoint is autogenerated when the procedure runs.
EXEC EDIS.usp_sharepoint_list_read
@url = 'https://netorgft762028.sharepoint.com/EDIS_DEV/'
,@svc_id = 'SHAREPOINT_SALES_SVC'
,@list_nm = 'avt_persons'
,@output_tbl_nm = '##avt_persons_sharepoint'
;
The example below writes data to SharePoint list "rgn_sls". EDIS matches the source query data to the destination SharePoint list based on the column names.
EXEC EDIS.usp_sharepoint_list_write
@url = 'https://netorgft762028.sharepoint.com/EDIS_DEV/'
,@svc_id = 'SHAREPOINT_SALES_SVC'
,@list_nm = 'rgn_sls'
,@src_qry = 'SELECT RGN, SUM(SALES) FROM Sales_db.dbo.rgn_sales_wkly GROUP BY RGN'
;
SharePoint document libraries are a great way to collaborate and exchange files. EDIS supports uploading and downloading files from SharePoint document libraries so that you can use them as a hub to distribute and collect your data.
EXEC EDIS.usp_config_sharepoint_id
@svc_id = 'SHAREPOINT_SALES_SVC'
,@user_id = 'admin@contoso_sales.com'
,@password = '_svc_sls_admin_$sdnd1#x!'
,@is_sharepoint_online = 1
;
This example downloads all files ending with ".xls" from SharePoint document library "data_bin" to a local directory.
EXEC EDIS.usp_sharepoint_library_download_files
@url = 'https://contoso.sharepoint.com/Sales Team/'
,@svc_id = 'SHAREPOINT_SALES_SVC'
,@doc_lib_nm = 'data_bin'
,@file_crit = '*.xls'
,@local_folder_path = 'C:\temp\user_inputs\'
;
This example uploads all files ending with ".txt" to SharePoint document library "data_bin" from a local directory.
EXEC EDIS.usp_sharepoint_library_upload_files
@url = 'https://contoso.sharepoint.com/Sales Team/'
,@svc_id = 'SHAREPOINT_SALES_SVC'
,@doc_lib_nm = 'data_bin'
,@file_crit = '*.txt'
,@local_folder_path = 'C:\temp\user_inputs\'
;
The SQL Command tool is used to issue a SQL command to an external server. This is useful when you need to issue commands to create tables, indexes, or other DDL statements on an external server. SQL commands use named aliases to access other servers, as described in the 'Database' section of this developer features page.
The SQL command procedure can execute multiple commands in one batch. All you need to do is delimit each command with a semi-colon, and EDIS will execute them in the order they are submitted.
EXEC EDIS.usp_run_sql_cmd
@srvr_nm = 'ORA_PR_SLS'
,@sql_cmd = 'CREATE INDEX ix_wk on PR_SLS.SLS_BY_RGN (FSCL_WK)'
;
EXEC EDIS.usp_run_sql_cmd
@srvr_nm = 'ORA_PR_SLS'
,@sql_cmd = '
DROP TABLE PR_SLS.SLS_BY_RGN;
CREATE TABLE PR_SLS.SLS_BY_RGN (RGN_ID INT, FSCL_WK INT, SLS_AMT FLOAT);
'
;
EDIS can run both web requests (OData) and soap requests. Below are some examples.
This example runs a web request to query the current stock price for Microsoft (MSFT). The results are loaded to global temporary table ##quote_price. This table is automatically created.
DECLARE @stock_ticker varchar(5) = 'MSFT'
DECLARE @url varchar(max) = 'http://www.google.com/finance/option_chain?q=AAPL&output=json'
EXEC EDIS.usp_run_web_request
@url = @url
,@output_tbl_nm = '##quote_price'
,@method = 'GET'
;
SELECT * FROM ##quote_price
This example runs a web request to query the weather forecast from yahoo weather.
EXEC EDIS.usp_run_web_request
@url = 'https://query.yahooapis.com/v1/public/yql?q=select%20item.condition%20from%20weather.forecast%20where%20woeid%20%3D%202487889&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys'
,@output_tbl_nm = '##weather'
;
SELECT *
FROM ##weather
EDIS provides tools to zip and unzip files. Code examples are below.
EXEC EDIS.usp_unzip_files
@zip_file_path = 'C:\temp\sales_group.zip'
,@folder_path = 'C:\temp\sales_files\'
;
This example finds all files ending in “.txt” in the C:\temp\sales_files folder and combines them into one zip file. Additionally, the @include_sub_folders parameter tells EDIS to traverse all sub folders and grab the corresponding files that end in “.txt” to include in the zip file.
EXEC EDIS.usp_zip_files
@folder_path = 'C:\temp\sales_files\*.txt'
,@zip_file_path = 'C:\temp\sales_group.zip'
,@include_sub_folders = 1
;