Importing & Exporting Data

EDIS Online User Guide – Importing & Exporting Data

Importing and exporting data with EDIS is as simple as it gets. The primary procedure used to perform imports and exports is EDIS.usp_run_data_transfer. This procedure works with multiple sources and destinations to maximize flexibility on where you need to push and pull data. Below are examples of importing and exporting from text files, databases, Access, and raw files. For importing and exporting with Excel, click HERE

If your source or destination is a database, you first need to setup a service ID to connect to that data source. For examples of setting up database service ID’s, click HERE

Stored Procedure: SSISDB.EDIS.usp_run_data_transfer

Parameters

Name Description Default Value
@src_sys Source System: Can be a service ID if connecting to a database or one of the following:

1) “ACCESS” – If connecting to an Access DB 2) “FLATFILE” – If connecting to a Flat File 3) “RAW” – If connecting to a raw file

@@servername
@dest_sys Destination System: Can be a service ID if connecting to a database or 1 of the following:
1) FLATFILE – If exporting to a flat file
2) RAW – If exporting to a raw file
@@servername
@src_qry Source query when running a data transfer where the source is a database Null
@dest_tbl Destination Table to send a data transfer to (If the destination is a database). Tables need to be fully qualified with the Database_name.[Schema_Name – If applicable].Table Name. Note: SQL Server global temporary tables do not need to be qualified. You can just enter them as “##my_tmp_tbl” Null
@crt_dest_tbl Flag indicating if you want to generate the destination table on the fly. This parameter only works if the destination server is the SQL Server hosting EDIS. If the destination table is a permanent table, it cannot exist prior to this running, or an error will occur. If the destination is a global temp table (##), the table will get dropped and recreated if it already exists. 0 (False)
@batch_size Batch Size for destination server to process incoming rows 10,000
@keep_ident Indicates to preserve source identity columns if the source table has an identity seed. Works with OLEDB SQL Server Destinations 0 (False)
@keep_nulls Indicates to preserve source nulls 0 (False)
@is_key_ordered Flag indicating if the source query is sorted in the same order as the clustered index on the destination table. This parameter only helps if the destination table is a SQL Server table with a clustered index. 0 (False)
@file_path File path for source or destination file. Applies to Excel, Access, Flat Files, and Raw Files Null
@append_to_existing_file Flag indicating if you want to append the data to an existing flat file. This parameter only applies if the destination is a flat file. 0 (False)
@include_headers Indicates if the source file has headers or if the destination file should include headers 1 (True)
@header_rows_to_skip Value indicating header rows to skip on a flat file 0
@data_rows_to_skip Value indicating data rows to skip on a flat file 0
@text_qual Text qualifier character for source and destination flat files. Empty String
@col_delim Column delimiter for flat file source or destination. Note: For special white-space values, use the following:
1) {CR}{LF}
2) {CR}
3) {LF}
4) {TAB}
Additionally, if the whitespace value is a specific ASCII char not in the list above or combination of chars, denote as a semi-colon delimited list with the ASCII marker:
Example: If you have a separator of ASCII chars 8 and 7, type “ASCII::8;7”
{TAB}
@row_term Column delimiter for flat file source or destination. Note: For special white-space values, use the following:

1) {CR}{LF}
2) {CR}
3) {LF}
4) {TAB}

For all other row terminators, enter them as-is. You can even enter hex values such as “0x0a”. Additionally, if the whitespace value is a specific ASCII char not in the list above or combination of chars, denote as a semi-colon delimited list with the ASCII marker: Example: If you have a separator of ASCII chars 8 and 7, type “ASCII::8;7”

{CR}{LF}
@fixed_width_row_len Value indicating how wide a row is for fixed width flat files. If this value is not supplied, EDIS will attempt to guess it at run time by reading a couple rows prior to execution. 0
@fixed_width_intervals A semi-colon delimited list indicating the breakpoints in a fixed width flat file based on the character count Empty String
@ragged_right_intervals A semi-colon delimited list indicating the breakpoints in a ragged right flat file based on the character count Empty String
@is_unicode Flag to indicate of a source flat file is Unicode 0 (False)
@add_ghost_col Flag indicating if you want to add an additional column at the end of a source flat file. This is helpful when you have files coming from systems that format in such a way where the column delimiter shows up in the last column 0 (False)
@include_row_id_col Flag indicating if you want a column added to the destination global temp table indicating the physical row that the source row came from in the flat file. This only applies if the destination is the hosting MSSQL Server creating a global temp table on the fly 0 (False)
@max_col_width Defines max column width for source flat file. Maximum value is 4,000 characters. Note: If your source file is very wide, and you try to set the width to this maximum, you might receive an error that the transfer could not complete due to memory exception issues. 255
@load_on_ordinal When setting this flag to true, data transfers will match columns based on their ordinal position instead by name e.g. column 1 in the source will map to column 1 in the destination 0 (False)
@src_pre_sql_cmd You can use this field to run a SQL command against the source system if it is an OLEDB, ODBC, or ADO.NET RDBMS. This source pre-sql command will retain the same connection as the data transfer. This is useful when you need to do things such as setting context session settings prior to executing a data transfer Empty String
@use_32_bit_runtime Use this flag if the source or destination requires 32-bit driver. Otherwise, it will default to 64 bit runtime 0 (False)

 

Remarks

  1. Source and destination columns are matched based on the column name by default (non-case sensitive). To override this behavior and transfer on column ordinal position, use the @load_on_ordinal = 1 parameter setting.

Supported Sources and Destinations

Type As source As destination
OLEDB YES YES
ODBC YES YES
Flat file YES YES
Raw YES YES
Access* YES
ADO.Net** YES
Excel***

* For Access, you need to install the Microsoft ACE OLEDB 12.0 Driver
** For ADO.NET, supports ODBC and OLEDB facades
*** For Excel, please see the Excel tools section in this manual.

Examples

Server-To-Server
Example 1: This example queries data from service ID AVT_WORKS2014 and sends the results to the EDIS host SQL Server (@@servername), and creates the destination temp table on the fly

exec SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'AVT_WORKS2014', @dest_sys = @@servername

,@src_qry =

'

select *

from [AdventureWorks2014].[Production].[ProductCategory]

where ModifiedDate >= ''2008-03-01''

'

,@dest_tbl = '##avt_prodcat_imp'

,@crt_dest_tbl = 1

 

Example 2: This example imports data from an oracle server (ORA_P1) and loads to a SQL server table using the key ordered hint to speed up the insert. The order of the clustered index on the destination table is: fscl_wk, rgn_id, prod_cat_id

exec SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'ORA_P1', @dest_sys = @@servername

,@src_qry =

'

select fscl_wk, rgn_id, prod_cat_id, sls_amt

from SALES_WEEKLY_FACTS

WHERE fscl_wk between 3 and 6

and fscl_yr = 2014

order by fscl_wk, rgn_id, prod_cat_id

'

,@dest_tbl = 'Sandbox.dbo.sls_wkly'

,@is_key_ordered = 1

 

File-To-Server

Example 3: Importing a tab delimited flat file

EXEC SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'FLATFILE'

,@dest_sys = @@servername

,@dest_tbl = '##product_output_raw_imp'

,@crt_dest_tbl = 1

,@file_path = 'C:\Temp\product_output.txt'

,@col_delim = '{TAB}'

,@include_headers = 1

 

Example 4: Importing CSV file with text qualifier and 4 header rows to skip

EXEC SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'FLATFILE'

,@dest_sys = @@servername

,@dest_tbl = '##product_output_raw_imp'

,@crt_dest_tbl = 1

,@file_path = 'C:\Temp\product_output.csv'

,@col_delim = ','

,@text_qual = '"'

,@header_rows_to_skip = 4

,@include_headers = 1

 

Example 5: Import a Fixed Width Flat File
For fixed width flat files, you need to identify the break points (intervals) based on character count in the file where you want the rows divided. Below is a screenshot of sample fixed width data, and identifying the break points

In the example screenshot above, the intervals for the fixed width flat file occur at character 7 and at character 23. Also, the width of a single row is 34 characters. When determining the row length, you do not have to account for the row terminator. EDIS will add that on. Also, if the row length is not supplied, EDIS will attempt to guess it based on reading a couple rows prior to execution.

EXEC SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'FLATFILE'

,@dest_sys = @@servername

,@file_path = 'C:\temp\sls_data.txt'

,@dest_tbl = '##sls_data_import'

,@crt_dest_tbl = 1

,@fixed_width_row_len = 34

,@fixed_width_intervals = '7;23'

 

Example 6: Import a Ragged Right Flat File
For ragged right flat files, you need to identify the break points (intervals) based on character count in the file where you want the rows divided, similar to fixed width. Also, since the last column on a ragged right file can have varying lengths, you will want to define the @max_col_width parameter based on what the maximum length of that column could be. The default for this parameter if not specified is 255. In the example below, since the max length is 85, we will not worry about specifying it since it is lower than 255. If it was higher, you would want to specify that parameter.

EXEC SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'FLATFILE'

,@dest_sys = @@servername

,@file_path = 'C:\temp\sls_data_rr.txt'

,@dest_tbl = '##sls_data_import_rr'

,@crt_dest_tbl = 1

,@ragged_right_intervals = '7;18;32'

,@max_col_width = 85

 

Access-to-Server
Example 7:

EXEC SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'ACCESS'

,@dest_sys = @@servername

,@src_qry = 'SELECT * FROM [Sales]'

,@dest_tbl = '##sales_imp_raw'

,@crt_dest_tbl = 1

,@file_path = 'C:\Temp\Northwind.accdb'

 

Server-to-File
Example 8: Exporting data to a tab delimited flat file with headers automatically included

EXEC SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'ADV_WORKS'

,@dest_sys = 'FLATFILE'

,@src_qry = 'select top 10 * from adventureworks2014.products.product_line'

,@file_path = 'C:\Temp\product_output.txt'

,@col_delim = '{TAB}'

,@include_headers = 1

 

Example 9: Exporting data to a pipe delimited flat file with no headers

EXEC SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'ADV_WORKS'

,@dest_sys = 'FLATFILE'

,@src_qry = 'select top 10 * from adventureworks2014.products.product_line'

,@file_path = 'C:\Temp\product_output.txt'

,@col_delim = '|'

,@include_headers = 0

 

Raw Files
Example 10: Exporting to a raw file

Exec SSISDB.EDIS.usp_run_data_transfer

@src_sys = @@servername, @dest_sys = 'RAW'

,@src_qry = 'select * from AdventureWorks2014.[HumanResources].[Department]'

,@file_path = 'C:\temp\avt_depts.raw'

 

Example 11: Importing from a Raw File

Exec SSISDB.EDIS.usp_run_data_transfer

@src_sys = 'RAW', @dest_sys = @@SERVERNAME

,@file_path = 'C:\temp\avt_depts.raw'

,@dest_tbl = '##depts_import'

,@crt_dest_tbl = 1