I have an SSIS package in which I am attempting to import select Excel files into SQL Server based on the date they were run. The worksheet format of the nightly drop is consistent, with field names and types being uniform. The date of each run is specified in the file name and worksheet name. The file name is of the type "ABC_YYYYMMDD.xls", and the worksheet name is "ABC_YYYYMMDD".
In Visual Studio, I have a variable SetDir, which establishes the directory in which the files are located. I also have the variable SetDate, which determines the date I want to get in the YYYYMMDD format. I have set Excel File Path to the followoing expression:
@[User::SetDir] + "ABC_" + @[User::SetDate] + ".xls"
Finally, I created a WorksheetName variable that translates to the following expression:
"ABC_" + @[User::SetDate] + "$"
I have assigned my worksheet name to a variable (see the screen), but at this point, I run into an error stating "A destination table name has not been provided." What seems to be happening is that the expression to which WorksheetName translates is not translating properly, but I don't understand why. I would appreciate some feedback on this.
Sincerely, ~Peter Ferber