SSIS search and return function
I am trying to get the date from the below filename using ssis expression. i get this filename from a variable
I tried using findstring and reverse function, but it still throws the call.
Any recommendations pls
Variable
@[User::CurrentFile] : Z:\RETAIL DATA\HN_NLG\Supplier_report_by_Brand_Trend Micro_WC_20170327.xlsx
Need to get 20170327
and convert to2017-03-27
Thank you in advance
source to share
You can use LEFT
and RIGHT
and SUBSTRING
to achieve this, use the following expression to get the expected result:
SUBSTRING(LEFT(right(@[User::CurrentFile],13),8),1,4) + "-" + SUBSTRING(LEFT(right(@[User::CurrentFile],13),8),5,2) + "-" + SUBSTRING(LEFT(right(@[User::CurrentFile],13),8),7,2)
If @[User::CurrentFile] = 'Z:\RETAIL DATA\HN_NLG\Supplier_report_by_Brand_Trend Micro_WC_20170327.xlsx'
you get2017-03-27
source to share
Try something like below maybe in exec sql or script task and assign it to a variable for later use.
select Cast(CONVERT(date,
Reverse(Substring(Reverse('Z:\RETAIL DATA\HN_NLG\Supplier_report_by_Brand_Trend Micro_WC_20170327.xlsx'),
CHARINDEX('.',Reverse('Z:\RETAIL DATA\HN_NLG\Supplier_report_by_Brand_Trend Micro_WC_20170327.xlsx'),1)+1,
8))) as varchar(10))
source to share