DBA Features – SQL ETL – Automate SQL Server ETL

EDIS has built-in auditing to track and log all ETL commands performed by EDIS. The results are loaded into a base table that can be used by the DBA to monitor numerous aspects such as start and end times, rows transferred, IP addresses, and queries executed. This auditing tool brings clarity and simplicity to the DBA when they need to answer questions regarding what data is coming into and out of their server, when it is ran, and who ran it.

For companies that require audit trails, this tool provides them a one-stop shop and peace of mind. Below is a sample look at EDIS's auditing tool.

Get a list of all ETL tasks ran on a specific date

SELECT task_end_ts, task_duration, task_action, exec_id, ip_adrs, rows_tsfr
FROM SSISDB.EDIS.etl_audit
WHERE CAST(task_start_ts as DATE) = '2015-02-01'

Example Results

task_end_ts	task_duration	task_action	exec_id	ip_adrs	rows_tsfr
2015-02-01 08:43:03	65	DATA_TRANSFER_TASK	00013B6C-A9F6-43DA-98D4-C5404B0FDB28	121.150.1.1	10523

EDIS has built-in multi-tenant support. With this feature, multiple business groups can work on the same SQL Server and you can still separate connections so that the business groups do not use each others connections. You can add a single group or user, or add multiple groups to a specific service ID, so that business teams can share connections or have exclusive ones. To illustrate this, the code example below will lock server id "ORA_PR_SLS" to a Windows domain group called "CTS\GG_DEV_-- USERS".

EXEC EDIS.usp_config_svc_id_perm

@service_id = 'ORA_PR_SLS'

,@usr_id = 'CTS\GG_DEV_-- USERS'

,@action = 'INSERT'

;

With this benefit, DBA's can maintain business owned credentials in a more streamlined fashion and have more granular control over what systems a developer can access.

EDIS is industry tested and can scale to run hundreds of ETL tasks per hour in parallel. Organizations today are using EDIS to consolidate workloads and minimize the number of servers and licenses they need for their ETL, which saves both time and money. With EDIS's scaling power, organizations are empowered to:

■ Consolidate multiple ETL servers

■ Save on hardware and licensing costs

■ Centralize ETL, audits, and reduce management cost

■ Synchronize workloads and batch windows

Imagine if you could just eliminate one ETL server from your organization? That would save you significant time and money. With EDIS though, you can eliminate many of them and consolidate and centralize your workload.

Managing credentials for accessing other systems is challenging and time consuming. With EDIS though, it is quick and simple. Our program uses the concept of a named alias to access another server. What this means is that when you need to setup a connection to another system, whether it be SQL Server, DB2, Oracle, MySQL, or other platforms, you assign the connection a nickname (named alias) that developers will use to connect and query that system. The great part about this is that going forward, you do not need to provide developers with credentials to the server, you just provide them the named alias and they can use that to access the system. Below is an example illustrating this concept.

SAMPLE: Setting up an ODBC connection to an Oracle Server

EXEC EDIS.usp_server_id_update

@server_id = 'ORA_PR_SLS', @server_provider = 'ODBC'

,@server_platform = 'ORACLE'

,@cn_str = 'DSN=ORA_PR_SLS_SET1; UID = pUser; PWD = $s^hg_!rx5'

,@action = 'INSERT'

;

Once this is setup, developers can access the new system to transfer data using this example procedure that references the @server_id (ORA_PR_SLS)

EXEC EDIS.usp_run_data_transfer

@src_sys = 'ORA_PR_SLS', @dest_sys = @@servername

,@src_qry = 'SELECT RGN_ID, SUM(SALES) AS SLS FROM PR_SLS.SLS_BY_RGN GROUP BY RGN_ID'

,@dest_tbl = 'RPT_SLS_DB.dbo.Sales_by_region_agg'

;

Additionally, if you ever need to update the password for a connection, you simply run this procedure again with the @action parameter set as “UPDATE” as illustrated below.

Updating the password to ORA_PR_SLS connection

EXEC EDIS.usp_server_id_update

@server_id = 'ORA_PR_SLS', @server_provider = 'ODBC'

,@server_platform = 'ORACLE'

,@cn_str = 'DSN=ORA_PR_SLS_SET1; UID = pUser; PWD = g-tY+1=v1'

,@action = 'UPDATE'

;

How is this Secure?

When creating a server connection, the credentials are encrypted with the EDIS host SQL Server's service master key, which means user ID’s and passwords are never exposed.