How to import the file that matches the file name patterns in SSIS?

Posted on 2011-10-15
Last Modified: 2013-11-10
I'd like to import the file from the Import folder. The system should only look for the files with certain patterns, such as CIS_ACCT_YYYYMMDD.csv, where YYYYMMDD are year, Month and Day.

How do I accomplish that?

BTW, I have already created the Foreach loop Task and File System Task. Do I need Script Task?
The path should be defined as avaraiable.
Question by:chuang4630
    LVL 21

    Expert Comment

    I do believe that you have a folder with multiple files and you need to grab only today's file?

    Use an expression in the
    "CIS_ACCT_" + (DT_WSTR, 4 )year(getdate())+ (DT_WSTR, 2)Month(getdate())+ (DT_WSTR, 2)Day(getdate())+".CSV"

    Open in new window

    LVL 1

    Author Comment

    There severl different type of data files:


    I have import all of them. Since they have different columns, it would be better if you create a task for each type.

    So for this particular type, I only need to import CIS_ACCT_XX_YYYYMMDD.csv, where YYYYMMDD is the date and XX can be anything.
    LVL 1

    Author Comment

    What if the file name is something like this:

    XXX_YYYYMMDD, and/or

    In other words, the identifier could be either three or four letters. How do i define the pattern in the expression?
    LVL 1

    Author Comment

    Any comments?
    LVL 21

    Accepted Solution

    Sorry I missed you replies.
    It's easy, we'll evaluate from the right side then....or even reverse the string.

    Right([YourColumn] , 13) == "_" + (DT_WSTR,4)YEAR(GETDATE())+Right( "0" + (DT_WSTR, 2 ) MONTH(Getdate()),2) +Right( "0" + (DT_WSTR, 2 ) DAY(Getdate()),2)  +".csv"

    Open in new window

    Here the condition will give you TRUE or False, you can use that in conditional split or precedence constraint .

    Let me know if you need more help.
    LVL 1

    Author Comment

    I'll try

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now