Step By Step - Importing SQL Server Data to Access Using SSIS

Yesterday, my bos asked how to importing data from SQL Server to Access. Well, we can import or export data on SQL Server 2005 using SSIS (SQL Server Integration Services). In this article i try to write that i've done on it. Here we are

Step 1 :
Installing the SQL Server 2005 Service Pack 2 and SQL Server Hotfix.

Step 2:
Creating Package using Microsoft Bussines Intelligent Development Studio.

1. Open the Microsoft Bussines Intelligent Development Studio
2. On the Solution Explorer, Right click on SSIS Packages
3. Click next on the Splash Screen
4. Choose a data source from which to copy data and click next
5. Choose a destination where to copy data to and click next
6. Specify table copy or query and click next
7. Select source tables and views and click next.
8. And then click finish. This step will create a package to be executed

Step 3:
Creating user to access Integration Services job on SQL Server.

1. Create a new Credentials : Security > Credentials > New Credential. The Identity should be the computer administrator.
2. Create a new Proxy : SQL Server Agent > Proxies > New Proxy. Using the credential that you’ve created. And tick ‘SQL Server Integration Package’ on Active Subsystem.

Step 4:
Creating a job to importing data.

1. Create a job on SQL Server 2005.
2. Clik steps on ‘Select Page’ and Create new step.
Note: On package, please select package that you’ve created on BI Development Studio. And on Run as, Pick the proxy that you’ve created.
3. Clik schedules on ‘Select Page’ and Create new schedule. And click Insert.
And then click Ok.