 RE: Exporting SQL Server to Access -
08-29-2008
, 03:27 AM
Hi,
Thank you for using Microsoft MSDN Managed Newsgroup. This is Charles. I am glad to work with you on this issue.
From your description, I understand that you would like to know what the easiest way is to daily export all the tables in your SQL Server database into an Access database in a package.
If I have misunderstood, please let me know.
If your SQL Server is 2000, you can use DTS Import/Export Wizard.
Right click your database in Enterprise Manager, select Tasks->Export data, choose your SQL Server instance and the database as the data source, click Next, choose your Access database as
the destination, click Next, select all the tables, click Next, check Schedule DTS package for later execution, click the button [...] to schedule the job to run daily; after you configure the schedule,
click OK, click Next, input a package name, specify the package location to your current SQL Server, choose "Use Windows authentication", click Next, and click Finish.
If your SQL Server is 2005, similarly you can use "Export Data..." wizard to create a SSIS package. The process is similar, however there are some different things here.
Right click your database in SQL Server Management Studio (SSMS), select Tasks->Export data. During the Export Wizard, on the "Save and Execute Package" window, please uncheck "Execute
immediately", check "Save SSIS Package", check SQL Server, click Next, input the SSIS package name, your SQL Server name and check Windows Authentication, click Next and click Finish.
After that, you need to manually create a SQL job in SQL Server Agent. In SSMS, expand SQL Server Agent, right click Jobs, click New Job..., input the job name, select Steps, click New..., input a
step name, change Type to SQL Server Integration Services Package, input your SQL Server instance name to the Server field, check Use Windows Authentication, click the button [...] to choose
your SSIS package, and then click OK to return to the New Job window; select Schedules, click New..., input a schedule name, set Schedule type as Recurring, set Frequency-Occurs to Daily and
specify the time when you want to run the job, click OK to return to the New Job window and click OK.
Hope this helps. Please feel free to let me know if you have any other questions or concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
 |
Linear Mode
