SSIS: How to create and export data to Microsoft Access 2003 database?

Question

Goals - Automate exporting SQL Server 2005 views to Microsoft Access 2003 database (no transformation required) through SQL Server Job (with SSIS package)

There is no "Access DB Destination" in BI toolbox. So the question is,
How can I create an Access database (for me to load data to)?

Solution

There is an a destination for Access database, as long as the Access database already exist. You would add your SQL Server 2005 and the Access database to the connection manager. Then chose the OLE DB source and destination within your data flow. It will allow you to chose the Access connection as your destination.

Now if you are wanting to create the Access database on the fly, that is something different and would require a bit of scripting to accomplish.

Your Connection Manager properties and OLE DB destination editor for your access database would look similar to this:

Comments

[dance2die]

@Andy: I should have been more thorough in my question that I actually want to automate the process. By creating a SSIS package, I was thinking about creating a sql server job that runs weekly.