I am asking this question since I have not worked with ssis packages yet. Basically I would like to automate the following steps and what like recommendations on how to have the package and run the SSIS package again. In a sql server 2008 r2 professional edition database, I receive a daily file from the customer in an excel 2003 spreadsheet where I always need to obtain data from the fourth tab. The excel files I receive from the customer have the same name except the last part of the name includes the date of the file. I then use the import wizard to point the excel spreadsheet to a table in a specific database. Thus how would you setup an SSIS package to accomplish this task?

You are one step closer to making it a SSIS package: just save it to disk as a SSIS Package (an option on the Export Import Wizard), then create a SSIS project in say BIDS or VS2008, add this package to it and open it. Edit the Excel connection to make it parametrized (based on a new Package variable) if needed [I just do not know if the drop the files into a directory where there is no others], so if just one file to pick you can use a ForEach Loop to iterate over file mask MyExcelFile*.xls (example); Then when the package is tested, save it with package protection level rely on server storage and import into MSDB (need to connect to SSIS via SSMS). At this stage create a new Agent job and add a step of type SSIS package in which you would use this package to schedule. You are done.Arthur My Blog

Check this link the way it works is dynamic, it will pick up and excel file name and loops through excel sheets dynamically and to call the package use SQL Job to fire off the packageSincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).