I have an SSIS package which grabs an Excel file and loads the data to my 2012 SQL Server. I tried to schedule this job but couldn't because my connection string to the Excel file kept failing. I discovered that the file I am dropped names the first(and only) tab of the spreadsheet to reflect the count of rows in the data which is changing constantly. I read that a script task can write the VBA or C# code to rename the tab on an Excel workbook. I simply want to change the tab name to 'Sheet' instead of '1-48' or '1-70' so my connection string won't fail.
Can anyone show me the code I need to put in the Script Task to achieve this? Or what variables I might need to add to the SSIS solution?
I am not wise to VBA or C# at all...
Thanks everyone in advance!