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 2 - Logging in with alternate Windows ID
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
In this example, EDIS is exporting data from the host SQL Server to an Oracle server with named alias ORA_PR_SLS
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.
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.
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
This example adds a new worksheet to file sales_sum.xlsx called "sales detail".
This example clears worksheet "sales detail".
This example renames worksheet "sales detail" to "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.
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
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.
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.
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.
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.
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".
The example below deletes files from an FTP directory that end with ".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.
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.
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.
EDIS has tools to perform the most common local file tasks. Below are examples.
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
TO WRITING THIS!
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.
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.
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.
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.
This example exports data to a raw file. If the file does not exist, EDIS automatically creates it.
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.
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.
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.
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.
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.
This example downloads all files ending with ".xls" from SharePoint document library "data_bin" to a local directory.
This example uploads all files ending with ".txt" to SharePoint document library "data_bin" from a local directory.
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.
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.
This example runs a web request to query the weather forecast from yahoo weather.
EDIS provides tools to zip and unzip files. Code examples are below.
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.