May 2, 2008 at 9:41 am
I'd like some help with this, as I don't have much experience with SQL Server.
I use Access a lot, and I have a particular application which runs about 50 queries and VBA procedures to extract and massage data into 4 tables.
I hope to transfer all of this processing into SQL Server at some point, but one step at a time....
This extract/process process is run daily. I'd like to export the resultant tables into SQL Server every day, but ideally I'd like to clear down what's already in SQL Server and import the data into existing tables, in order to retain the indexes, as it's read-only information to be used in a bespoke reporting package.
Should I be looking to SQL Server to pull the data in, or should I be looking at a SSIS package for this ?
May 2, 2008 at 9:52 am
This is what SSIS is best at. A package with an Execute SQL task to empty the SQL Server tables and a series of Data Flow tasks to import the data from the Access tables is basically what you'd use. The package can be run from a step in a scheduled SQL Server Agent job.
Greg
May 2, 2008 at 9:54 am
I'd look into using SSIS for this. You can create a pretty straightforward package that reloads the SQL Server tables from the Access database and then schedule the package to run with SQL server agent at a certain interval or specific times.
May 6, 2008 at 12:59 am
Thanks for the help, guys.
That's saved me wasted effort.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply