Question

How to create an SSIS package for importing an Access table to SQL Server, running SPs and exporting data to Excel

SSIS, SQL Server integration/interoperability, Stored procedures, Microsoft Access

I need to create a SQL Server Integration Services (SSIS) package for importing an MS Access table into SQL Server, then running some stored procedures on this database and finally exporting the result to Excel.

I am comfortable in writing queries and working in Management Studio but I don’t know how to actually design an SSIS package. I am asking because I need to automate these tasks for future employees, as I am leaving my current position.
I have already referred to Microsoft SQL Server Books Online but could not find the solution. Can you tell me how to do this?

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.