Clever Geek Handbook

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

+3


source to share


2 answers


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

+1


source


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))
+1


source






More articles:


All Articles