SSIS pick csv file

NickK 176 Reputation points
2022-10-09T19:26:29.227+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,883 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,466 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 33,386 Reputation points
    2022-10-10T01:57:54.203+00:00

    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.

    248830-untitled.png

    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.


  2. Jingyang Li 5,891 Reputation points
    2022-10-10T02:36:42.993+00:00

    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]

    0 comments No comments