Hi Prashant,
While working with Excel for looping the same mapping for multiple input files has a slight different than the same example for processing multiple flat files.
For Excel metadata, you can keep the URL as is (no need to put parameter).
When you expand the Excel Connection, you see your Excel Datastore, Click on that and you will see, Name/CatalogueName:-
Change these with the parameter.
But as per your post, one thing to note that :{FILE_NAME}: stores the complete filename, including extension. So when using it you have to remember that it contains the extension as well and not just the name.
So the difference here is that you need just the excel file name, whereas :{FILE_NAME}: will give you name with extension.
So inside the Process which you are going to put in Execute Delivery action, place a SQLToParameter action and write a convert statement and assign the parameter name (in my case "~/excel_name"):-
Select SUBSTRING('${~/p_excel_name}$',1,LEN('${~/p_excel_name}$')-5)
where p_excel_name is the parameter defined on Execute Delivery Action which store yuor :{FILE_NAME}:
Please let me know if that helps!
Thanks,
Vaibhav
