SSIS pick csv file

Question

hi experts, I need some tips on the below request.

I have a ssis task in which I use SFTP task to download files from FTP to D:\Dowload\ and the file format is CSV with a date.
Filename_22020930_Final.csv, and next week or day the file will be Filename_22021007_Final.csv or something like that (filename_date.csv)

I am wondering how to get data from files with dates. source CSV and destination SQL table
Currently- I have a ssis package with the file name Filename.csv and it's working fine but the requirement changed that the file will have dates.
can someone help me get the source CSV files with the date?

Thanks

Answer 1

Hi @NickK ,

If the file name is based on the date, we could first save below expression to a variable first.

"D:\\Dowload\\Filename_"  
+ (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())  
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)  
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) + "_Final.csv"  

On the Flat File connection manager, add the string variable as the expression for the connection string. This can be done from the Properties window (press F4) on the connection manager, going to the Expressions field, pressing the ellipsis next to it, choosing the ConnectionString property on the next window and selecting the recently created string variable as the expression for this.

Also remember to set the DelayValidation Property to true.

A sample here: http://sqlknowledgebank.blogspot.com/2013/05/ssis-dynamic-flat-file-connection.html
Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

You can try to import a file dynamically without knowing the file name in this discussion:
[https://stackoverflow.com/questions/46798513/import-data-from-excel-using-ssis-without-knowing-the-file-name][1]