Post reply

Extract dates from the file name in SSIS

  • June 6, 2019 at 9:40 am

    #3650689

    Hi,

    I have a requirement to extract the 2 dates from the file name in SSIS (2010) as a derived column in the data flow task. The filename is as follows:

    Abdr_FC_BHYUK_Weekly_Physical_SAVERSHOP_IBLUPGE_All Links Roll up_050519_110519.xlsx

    Can someone please help on this ?

    Thanks.

     

  • June 6, 2019 at 10:05 am

    #3650692

    Will the file name always be the same length, or different lengths? If different, will the dates always be in the same position at the end? Also, there is no SSIS 2010. Perhaps you're using VS 2010 with SSDT 2012 for SQL Server 2012?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 6, 2019 at 10:07 am

    #3650693

    Hi,

    Thanks.

    The  file format will always remain the same with the same length. Also, the dates will always be positioned at the end.

    I am using VS2010 with SQL Server 2012. Thanks.

  • June 6, 2019 at 10:23 am

    #3650696

    Create 2 new variables in your packages (I have called them StartDate and EndDate) with the Data type DateTime, and then set their expressions to the below respectively (obviously replace @[User::@FileName] with your variable's name):

    (DT_DATE)  ("20" + SUBSTRING( @[User::FileName] , 71, 2 ) + "-" + SUBSTRING( @[User::FileName] , 69, 2 ) + "-" + SUBSTRING( @[User::FileName] , 67, 2))

    (DT_DATE)  ("20" + SUBSTRING( @[User::FileName] , 78, 2 ) + "-" + SUBSTRING( @[User::FileName] , 76, 2 ) + "-" + SUBSTRING( @[User::FileName] , 74, 2))

    Then you can add a Derived Column Transformation in your Data Flow, add 2 new columns (again, I called them StartDate and EndDate respectively) and set the value for their expression as @[User::StartDate] and @[User::EndDate] respectively:

    Derived Columns

    • This reply was modified 4 years, 10 months ago by  Thom A.
    • This reply was modified 4 years, 10 months ago by  Thom A.
    • This reply was modified 4 years, 10 months ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 8, 2019 at 12:52 pm

    #3651383

    Thanks very much, it helped immensely !

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply