Local File Tasks

EDIS Online User Guide – Local File Tasks

Listing Files on Local Directory
This table valued function returns a list of files and the file metadata based on the directory passed in.
Table Valued Function: SSISDB.EDIS.ufn_get_files

Parameters

Name Description Default Value
@src_dir Source Directory you want to scan for files None; Required
@filter Filter that tells the function to only return files based on a search pattern provided, You can supply wildcards such as *, ?, #, and []. For example, if you want to search for all files ending in “.txt”, pass in “*.txt” as the filter. If you want all files returned, supply NULL as the value NULL

Examples

Example 1: The example below scans the local directory “C:\SSIS\temp” for all files.

SELECT * FROM SSISDB.EDIS.ufn_get_files('C:\SSIS\temp',NULL)

 

Example 2: The example below scans the local directory “C:\SSIS\temp” for all Excel files that end with extension “.xlsx” and loads the results into a temp table.

SELECT * INTO #files FROM SSISDB.EDIS.ufn_get_files('C:\SSIS\temp','*.xlsx')

SELECT * FROM #files

 

Reading a single line from a file
This function returns the contents of a specific line from a text file. This is helpful when you need to retrieve contents from a file and do not need to process the entire file
Scalar Function: SSISDB.EDIS.ufn_read_file_line

Parameters

Name Description Default Value
@file_path The path of the file you want to read from. None; Required
@line_nbr The line number from the file you want to read None; Required

Examples

Example 1: The example reads line 4 of a file and saves the results to a variable that you can later use.

DECLARE @line_4 varchar(max) = SSISDB.EDIS.ufn_read_file_line('C:\bin\daily_sales.txt',4)

 

Moving a File
This procedure moves a file from one directory to another, with the option of renaming the file after it is moved.
Procedure: SSISDB.EDIS.usp_move_loc_file

Parameters

Name Description Default Value
@src_path The path of the source file you want to move None; Required
@tgt_dir The target directory you want to move the file to None; Required
@new_file_nm [Optional] You can supply a new file name to rename the file to after it is moved. NULL
@force [Optional] If you set the @force parameter to 1, if the target directory does not exist, it will get created. Otherwise, if this parameter is not set to 1 and the target directory does not exist, a run time error will occur. 0 (False)

Examples

Example 1: The example moves file “C:\temp\daily_sales.txt” to folder “C\processed\sales_data\”.

EXEC SSISDB.EDIS.usp_move_loc_file

 @src_path = 'C:\temp\daily_sales.txt'

,@tgt_dir = 'C:\processed\sales_data\'

 

Example 2: The example moves file “C:\temp\daily_sales.txt” to folder “C\processed\sales_data\” and renames the file appending the current date to it.

DECLARE @prcd_file_nm nvarchar(255) = concat('daily_sales_',FORMAT(getdate(),'yyyy_MM_dd'),'.txt')

EXEC SSISDB.EDIS.usp_move_loc_file

 @src_path = 'C:\temp\daily_sales.txt'

,@tgt_dir = 'C:\processed\sales_data\'

,@new_file_nm = @prcd_file_nm

 

Copy File
This procedure copies a file to another directory.
Procedure: SSISDB.EDIS.usp_copy_loc_file

Parameters

Name Description Default Value
@src_file_path The file path of the file you want to copy None; Required
@tgt_folder_path The target directory you want to copy the file to None; Required

Examples
This example copies file “users.txt” to directory “C:\temp\archive”

Exec SSISDB.EDIS.usp_copy_loc_file

 @src_file_path = 'C:\temp\users.txt'

,@tgt_folder_path = 'C:\temp\archive\'

 

Renaming Files
This procedure renames a local file. You can also perform a file move with this procedure by changing the path of the file. If you change the path of the file, the original file does not get deleted. In order to delete files, see “Deleting Files” section.

Procedure: SSISDB.EDIS.usp_rename_loc_file

Parameters

Name Description Default Value
@curr_file_path Current full file path that you want to rename None; Required
@new_file_path New File Path that you want to rename the file to None; Required

Examples
This example renames a text file

Exec SSISDB.EDIS.usp_rename_loc_file

 @curr_file_path = 'C:\temp\sls_data.txt'

,@new_file_path = 'C:\temp\sls_data_processed.txt'

 

Deleting Files
This procedure deletes files on local directory, either by supplying a search criteria or an explicit file list

Procedure: SSISDB.EDIS.usp_delete_loc_files

Parameters

Name Description Default Value
@folder_path Local Folder Path 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 ‘’

Examples

Example 1: This example deletes files that end with “.txt”

EXEC SSISDB.EDIS.usp_delete_loc_files

 @folder_path = 'C:\temp'

,@file_crit = '*.txt'

 

Example 2: This example deletes a specific file list. To illustrate how to supply a file list, we first create the temp table, insert some file names into, and then run the stored procedure.

if object_id('tempdb..#files_to_purge') is not null drop table

#files_to_purge;

create table #files_to_purge (file_nm varchar(250))

insert into #files_to_purge

values ('test.txt'), ('report.csv')

EXEC SSISDB.EDIS.usp_delete_loc_files

 @folder_path = 'C:\temp'

,@file_list_tbl = '#files_to_purge'

 

Create folder
This procedure creates a new local folder.

Procedure: SSISDB.EDIS.usp_create_loc_dir

Parameters

Name Description Default Value
@folder_path The folder you want to create None; Required

Examples
This example creates a new folder called “FY_2016” in folder “C:\data\”

Exec SSISDB.EDIS.usp_create_loc_dir

 @folder_path = 'C:\data\FY_2016'

 

Delete folder
This procedure deletes a local folder.

Procedure: SSISDB.EDIS.usp_delete_loc_dir

Parameters

Name Description Default Value
@folder_path The folder you want to delete None; Required

Examples
This example deletes folder “FY_2016” in directory “C:\data\”

Exec SSISDB.EDIS.usp_delete_loc_dir

 @folder_path = 'C:\data\FY_2016'

 

File Watcher Task
This tasks scans a folder for a specific file and will stop once the file is available. You can use this when you need to process a file immediately when it arrives.

Procedure: SSISDB.EDIS.usp_watch_for_file

Parameters

Name Description Default Value
@folder_path The folder you want to scan 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” None; Required
@max_timeout Maximum number of seconds for the tax to run before cancelling if no file is found 0 – Indefinite run until user cancels the process or the file is found.

Example 1
This example invokes a file watcher for a text file “sales.txt”. Once it arrives, the file watcher task will end and you can execute a data transfer as seen below to load it to the target database.

Exec SSISDB.EDIS.usp_watch_for_file @folder_path = 'C:\temp\staging\', @file_crit = 'sales.txt';

 

Exec SSISDB.EDIS.usp_run_data_transfer @src_sys = 'FLATFILE'

,@dest_sys = @@servername

,@file_path = 'C:\temp\staging\sales.txt'

,@dest_tbl = '##sales_raw'

,@crt_dest_tbl = 1

,@col_delim = '|'

;

 

Example 2
This example waits for a file with pattern “sales*.txt” to arrive. Once it arrives, the file is processed and loaded to the target using a cursor to loop through all files that were loaded to the folder.

Exec SSISDB.EDIS.usp_watch_for_file @folder_path = 'C:\temp\staging\'

,@file_crit = 'sales*.txt'

;

-- Get List of Files

Exec SSISDB.EDIS.usp_get_loc_file_list @folder_path = 'C:\temp\fw'

,@file_crit = 'sales*.txt'

,@tmp_tbl_nm = '##file_list'

;

-- Loop through and load files to target db

DECLARE cs CURSOR LOCAL FAST_FORWARD FOR SELECT file_nm FROM ##file_list;

DECLARE @curr_file NVARCHAR(1000);

OPEN cs

FETCH NEXT FROM cs INTO @curr_file

WHILE @@FETCH_STATUS = 0

BEGIN

Exec SSISDB.EDIS.usp_run_data_transfer @src_sys = 'FLATFILE'

,@dest_sys = @@servername

,@file_path = @curr_file

,@dest_tbl = '##sales_raw'

,@crt_dest_tbl = 1

,@col_delim = '|'

;

END

CLOSE cs;

DEALLOCATE cs;

DROP TABLE ##file_list;