How to export tables from sql server to ms access on the fly?

Question

I would like to publish some data of a sql server 2k to msaccess databases. I'd like to do that given a table supplying datatransformation info, for example :

tablenameOnServer   | pathToPub 
------------------------------------------------------
Clients             | D:\Data\Pub1\ClientData1.mdb
Orders              | D:\OtherData\Pub\Sales.mdb

The given mdb file should be created (or an empty one copied of course) and the table should be created each time the script runs.

I of course don't need a full blown solution, but some pointers as where to start are very welcome. I thought I'd use SSIS for this, but am new to it and I like to know where I start best in order to avoid too much loss of time :

• Do I use SSIS with BIDS (vs2008), can I read data in a package and create tables on the fly?
• Do I use C# and manipulate and create packages in code?
• Or what do I do best? Is SSIS the obvious solotion anyway?

In any case : some pointers to get me started would be very welcome...

UPDATE : This question is about publishing data, so it can be shipped on CD for example. It's not about linking to an sql server.

Answer 1

The simplest solution is to simply link the sql server table in access. Then you can see the data in realtime.

Answer 2

You can create an Access database to do this fairly easily.

Here is a basic algorithm

• Within access, create a linked table pointing to your table.
• create an Access query to filter data according to your criteria called NewQuery
• Use VBA to create new database NewDB
• export your NewQuery with structure and columns to the NewDB

Answer 3

If you want to keep the SSIS packages, I would make a template SSIS package and then using the SSIS objects from C# create the packages. I've done this to start off packages which are later customized.

Alternatively, if you aren't going to keep the packages after they are generated and aren't going to use a lot of SSIS features (logging etc.), I would consider doing the whole thing from C# (or other language), because SSIS isn't a great tool for connection managers (source or destination) which change.

From C#, you can simply read the schema, create the table in Access with matching types and columns and populate it.