FTP

EDIS Online User Guide – FTP

EDIS has tasks to perform FTP and Secure FTP operations. To connect to an FTP server, you first need to setup an FTP service ID. Below is an example to setup an FTP service ID.

Remarks
When a new FTP 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.

Procedure: SSISDB.EDIS.usp_config_ftp_id

Parameters

Name Description Default Value
@ftp_id Service ID alias to use to connect to remote server None; Required
@uid FTP user ID None; Required
@pwd FTP password None; Required

 

Example:
Creating a new FTP service ID

exec SSISDB.EDIS.usp_config_ftp_id

 @ftp_id = 'SDRIVE_PR'

,@uid = 'puser@contoso.com'

,@pwd = 'sl%1_zx1'

 

Updating the password for an FTP service ID

exec SSISDB.EDIS.usp_config_ftp_id

 @ftp_id = 'SDRIVE_PR'

,@uid = 'puser@contoso.com'

,@pwd = 'new_p@2x_1R4'

 

Listing Files on Remote Directory
The FTP File directory listing procedure grabs a list of files on an FTP directory and loads the file list into a global temporary table. This procedure is great for situations where you need to FTP files over from a directory, but need to first scan what files are out there. Once you get a list, you can then cross-reference it against a reference table in your database to determine if you’ve already imported the file, and only initiate an FTP download (See FTP Section “Downloading Files”) on the files that you have not imported yet.

Procedure: SSISDB.EDIS.usp_get_ftp_file_list

Parameters

Name Description Default Value
@ftp_id FTP ID used to connect to remote server None; Required
@ftp_srvr FTP Server to connect to None; Required
@ftp_dir FTP Directory None; Required
@file_crit File criteria. You can supply wildcards such as *, ?, #, and []. For example, if you want to search for all files ending in “.txt”, set @file_crit = “*.txt” ‘*’
@tmp_tbl_nm Global Temporary Table Name (##) to load the file list to. This table will get created automatically when the procedure runs None; Required
@show_details Flag indicating if you want additional file details like size and last modify date 0 (False)
@use_ssh Flag indicating if you want to use S-FTP for the file transfer 0 (False)
@use_ssl Flag indicating if you want to use SSL for FTP-S 0 (False)
@use_passive Flag indicating if you want to use a passive connection 1 (True)
@port_nbr Port number to connect to FTP Site None

 

Remarks
1. FTP Directories are case-sensitive.

Example: This example searches for files on an FTP directory that have a naming convention of “sls_YYYY-MM-DD.txt” i.e. “sls_2014-01-05”. It will filter for files that are only in the year 2014, and for August (month 8), by leveraging the “#” wildcard

exec SSISDB.EDIS.usp_get_ftp_file_list

 @ftp_id = 'SDRIVE_PR'

,@ftp_srvr = 'contoso.com'

,@ftp_dir = '/main1/Files/Sales Output/'

,@file_crit = 'sls_2014-08-##.txt'

,@tmp_tbl_nm = '##sls_file_list'

 

Uploading Files
The FTP Upload files procedure uploads files from a local directory to an FTP directory. This procedure also has the capabilities of archiving files (See @archive_flag description in the parameter’s section below).

Procedure: SSISDB.EDIS.usp_ftp_upload_files

Parameters

Name Description Default Value
@ftp_id FTP ID used to connect to remote server None; Required
@ftp_srvr FTP Server to connect to None; Required
@ftp_dir FTP Directory None; Required
@local_dir Local Directory to search for files in for uploading to the FTP directory
@file_crit File criteria. You can supply wildcards such as *, ?, #, and []. For example, if you want to search for all files ending in “.txt”, set @file_crit = “*.txt” ‘’
@file_list_tbl Local or global temp table (#,##) name that holds a list of file names.
When supplying this temp table name, it is expected that the first column is named “file_nm” and is of type varchar(1000).
‘’
@archive_flag Flag indicating if you want to archive a file. When you flip this flag on, the existing local file name will be renamed with the abbreviation “prcd” appended for processed, and will append the timestamp in “yyyyMMddhhmmss” to the file name. The file will then go one folder lower than the FTP directory into a subfolder, which you specify in the @archive_folder parameter below. As an example, if a file on the local directory that you are going to upload to the FTP directory is listed as “SLS_2015-08-01.txt” and you set the @archive_flag to 1 (True), the local file will get renamed to “SLS_2015-08-01_prcd_20150802072315.txt”, assuming the file gets processed for archive on 2015-08-02 at 7:23.15 AM. Also, once the file is uploaded to the FTP Directory, the existing original file in the FTP directory will get deleted, since it has been moved into a subfolder. 0 (False)
@archive_folder Sub-folder in the FTP directory you would like to archive the file to. This parameter is only relevant if you set the @archive_flag to 1 (True). If the sub folder does not exist, one will be created automatically. “archive”
@use_binary Flag indicating if you want the upload to run as a binary FTP transfer 0 (False)
@use_ssh Flag indicating if you want to use S-FTP for the file transfer 0 (False)
@use_ssl Flag indicating if you want to use SSL for FTP-S 0 (False)
@use_passive Flag indicating if you want to use a passive connection 1 (True)
@port_nbr Port number to connect to FTP Site None


Remarks

1. FTP Directories are case-sensitive.

2. If your source file name has wildcard characters imbedded in the name, and you have used a wildcard search for what files to upload, and you have set the archive flag to true, the file in the parent ftp directory might not be deleted after the archive is performed, since true wildcards cannot be differentiated from wildcard characters within a file name.

a. Avoid naming files with wildcard characters embedded in the name e.g. do not name a file “SLS_Week_5_[ID1].txt”
b. As a work-around, you can run the FTP Delete stored procedure after running the archive

3. If a file become corrupt once the upload has finished, try flipping on the @use_binary flag to 1 (True). The @use_binary is typically only needed for Excel files. Normal text and CSV files don’t require binary transfers.

Examples

Example 1: This example uses a file criteria search to look for all text files that start with “sls” and end in “.txt”.

exec SSISDB.EDIS.usp_ftp_upload_files

 @ftp_id = 'test'

,@ftp_srvr = 'contoso.com'

,@ftp_dir = '/pub/parts/'

,@local_dir = 'C:\temp'

,@file_crit = 'sls*.txt'

 

Example 2: This example takes a pre-defined list of files and uploads them to an FTP server

create table #files_to_upload (file_nm varchar(1000));

insert #files_to_upload

values

('sls_20150601.txt')

,('sls_20150602.txt')

,('parts_20150601.csv')

exec SSISDB.EDIS.usp_ftp_upload_files

@ftp_id = 'test'

,@ftp_srvr = 'contoso.com'

,@ftp_dir = '/pub/parts/'

,@local_dir = 'C:\temp'

,@file_list_tbl = '#files_to_upload'

 

Example 3: This example uploads any file that starts with “sls” and ends in “.txt” and archives the files to a sub directory called “sls_archive”. If the sub folder “sls_archive” does not exist, it will automatically be created.

exec SSISDB.EDIS.usp_ftp_upload_files

@ftp_id = 'test'

,@ftp_srvr = 'contoso.com'

,@ftp_dir = '/pub/parts/'

,@local_dir = 'C:\temp'

,@file_crit = 'sls*.txt'

,@archive_folder = 'sls_archive'

,@archive_flag = 1

 

Downloading Files
This procedure downloads files from an FTP directory to a local directory. Files can be downloaded either via a wildcard search or using a pre-defined file list temp table.

Procedure: SSISDB.EDIS.usp_ftp_download_files

Parameters

Name Description Default Value
@ftp_id FTP ID used to connect to remote server None; Required
@ftp_srvr FTP Server to connect to None; Required
@ftp_dir FTP Directory None; Required
@local_dir Local directory to download files from the FTP directory to None; Required
@file_crit File criteria. You can supply wildcards such as *, ?, #, and []. For example, if you want to search for all files ending in “.txt”, set @file_crit = “*.txt” Empty string
@file_list_tbl Local or global temp table (#,##) name that holds a list of file names.
When supplying this temp table name, it is expected that the first column is named “file_nm” and is of type varchar(1000).
Empty string
@use_ssh Flag indicating if you want to use S-FTP for the file transfer 0 (False)
@use_ssl Flag indicating if you want to use SSL for FTP-S 0 (False)
@use_passive Flag indicating if you want to use a passive connection 1 (True)
@port_nbr Port number to connect to FTP Site None

 

Remarks
1. FTP Directories are case-sensitive.

Examples

Example 1: This example downloads files from an FTP site based on searching for files that end in “txt”.

EXEC SSISDB.EDIS.usp_ftp_download_files

 @ftp_id = 'CONTOSO_TEST'

,@ftp_srvr = 'contoso.com'

,@ftp_dir = '/pub/sales_archives/'

,@local_dir = 'C:\work\bin\'

,@file_crit = '*.txt'

 

Example 2: This example downloads files from an FTP site using a pre-defined file list. Even though this example creates and builds the file list temp table first, you can create the temp table using other methods and pass it in.

if OBJECT_ID('tempdb..#file_list') is not null drop table #file_list;

create table #file_list (file_nm nvarchar(1000))

insert #file_list

values

('SALES_RGN5_20150201.txt')

,('SALES_RGN8_20150203.txt')

EXEC SSISDB.EDIS.usp_ftp_download_files

@ftp_id = 'CONTOSO_TEST'

,@ftp_srvr = 'contoso.com'

,@ftp_dir = '/pub/sales_archives/'

,@local_dir = 'C:\work\bin\'

,@file_list_tbl = '#file_list'

 

Deleting Files
This procedure deletes files from an FTP directory. Files can be deleted either via a wildcard search or using a pre-defined file list temp table.

Procedure: SSISDB.EDIS.usp_ftp_delete_files

Parameters

Name Description Default Value
@ftp_id FTP ID used to connect to remote server None; Required
@ftp_srvr FTP Server to connect to None; Required
@ftp_dir FTP Directory None; Required
@local_dir Local directory to download files from the FTP directory to None; Required
@file_crit File criteria. You can supply wildcards such as *, ?, #, and []. For example, if you want to search for all files ending in “.txt”, set @file_crit = “*.txt” Empty string
@file_list_tbl Local or global temp table (#,##) name that holds a list of file names. When supplying this temp table name, it is expected that the first column is named “file_nm” and is of type varchar(1000). “file_nm” and is of type varchar(1000). Empty string
@use_ssh Flag indicating if you want to use S-FTP for the file transfer 0 (False)
@use_ssl Flag indicating if you want to use SSL for FTP-S 0 (False)
@use_passive Flag indicating if you want to use a passive connection 1 (True)
@port_nbr Port number to connect to FTP Site None

 

Remarks
1. FTP Directories are case-sensitive
2. Deletes do not drill through sub-folders.

Examples

Example 1: This example deletes files that end in “.txt”, were in August of 2014, and were representative of any region number, by leveraging the wildcard “#” character.

exec SSISDB.EDIS.usp_ftp_delete_files

 @ftp_id = 'CONTOSO'

,@ftp_srvr = 'contoso.com'

,@ftp_dir = '/pub/sales_archive/'

,@file_crit = 'SALES_RGN#_201408##.txt'

 

Creating a Sub Directory
This procedure creates a new subfolder on an existing FTP directory

Procedure: SSISDB.EDIS.usp_ftp_create_dir

Parameters

Name Description Default Value
@ftp_id FTP ID used to connect to remote server None; Required
@ftp_srvr FTP Server to connect to None; Required
@ftp_dir FTP Directory None; Required
@folder_nm New folder name you want to have created in the FTP Directory None; Required
@use_ssh Flag indicating if you want to use S-FTP for the file transfer 0 (False)
@use_ssl Flag indicating if you want to use SSL for FTP-S 0 (False)
@use_passive Flag indicating if you want to use a passive connection 1 (True)
@port_nbr Port number to connect to FTP Site None

 

Remarks
1. FTP Directories are case-sensitive

Examples

Example 1: This example creates a new folder titled “sls_archives”

exec SSISDB.EDIS.usp_ftp_create_dir

 @ftp_id = 'CONTOSO'

,@ftp_srvr = 'contoso.com'

,@ftp_dir = '/pub/regions/sales/'

,@folder_nm = 'sls_archive'