Extract dates from the file name in SSIS

  • pwalter83

    SSChampion

    Points: 14525

    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.

     

  • Thom A

    SSC Guru

    Points: 98326

    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.

  • pwalter83

    SSChampion

    Points: 14525

    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.

  • Thom A

    SSC Guru

    Points: 98326

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

    Thom~

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

  • pwalter83

    SSChampion

    Points: 14525

    Thanks very much, it helped immensely !

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

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