Extract dates from the file name in SSIS

  • 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.

     

  • 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

  • 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.

  • 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 5 years, 5 months ago by  Thom A.
    • This reply was modified 5 years, 5 months ago by  Thom A.
    • This reply was modified 5 years, 5 months ago by  Thom A.

    Thom~

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

  • 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