First you’ll need to fire up BIDS (Business Intelligence Development Studio) from the SQL Server 2005 Program Group and create a new project.
You’ll need to drag a data flow task into the package. This is where you will setup the import from Access to the SQL Server. Double click the data flow task. You’ll need to setup a connection for each, then drag a source and destination object into the data flow task. In the data needs to be converted into Unicode for Unicode data types or anything it’s done in here.
Then go back to the main part of the package. You’ll need a SQL Task for each stored procedure you need to run.
Then another data flow task for the export to Excel. Same idea as before but this time the SQL Server is the source and the Excel sheet is the destination.
Back on the main part of the package drag the green arrows from one object to another to control the order that things happen in.