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

Tags:
Microsoft Access
SQL Server integration/interoperability
SSIS
Stored Procedures
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following