June 6, 2019 at 9:40 am
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
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
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
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:
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
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