dynamically exporting data to Access file

I am looking a way to export SQL Server 2005 DB tables to Access file dynamically. Like if i have added or removed any tables from SQL DB then when i run SSIS package it should export that table with data to Access file. is there any easy way to do this. if it is not them please someone please tell me which controls should i look at and which technique i should use to do this.

The only way I can think of is to generate packages programatically; as on each run you have to check what tables are available and whether they exists or not in the Access DB. There is section in BOL that talks about creating packages programatically in case you are interested.

I don't know what is your ultimate goal, but perhaps a DB backup may be what you need?

|||

Thanks Rafael, it is actully a process for extraction of data from SQL Server DB and then export that data to Access file. so the end result should be a package which get tables and data from SQL Server and push that information to Access file every time we run it. will you please give me more inforamtion about BOL and if you have got any good scource about it please point towords it. that would be really helpfull. or any other suggestion

Regards,

Haroon

|||

I google search gives: http://www.google.com/search?q=SSIS+package+programmatically&hl=en&pwst=1&start=10&sa=N

This is the topic in BOL:

http://msdn2.microsoft.com/en-us/library/ms136025(SQL.90).aspx

I Suggested to look into building packages programmatically given the fact that the number and name of tables in not known at run time. Other approach may be to take out the 'dinamically' part of your requirment and yous build one package/dataflow per each table you want to move.

Dynamically creating cube

Hi guys

I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.

I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.

Any help in pointing me to the right direction is appreciated.

Thankyou
TomCan you please explain the scenario where you think we may need such a functionality.|||The reason I ask is because I need to generate sales data to various company however each company requries different sets of cube data being generated. Where some should not see the sales figures of another company. As a result i need to generate this dynamic cube and have it filled and exported as a local cube so that it can be downloaded for offline browsing.

any idea how I can achieve this is appreciated?
Thanks
Tom|||Did you ever figure out how to do accomplish this?|||yes, using CREATE CUBE statment, and specify the path and it will create it automatically

Dynamically creating cube

Hi guys

I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.

I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.

Any help in pointing me to the right direction is appreciated.

Thankyou
TomCan you please explain the scenario where you think we may need such a functionality.|||The reason I ask is because I need to generate sales data to various company however each company requries different sets of cube data being generated. Where some should not see the sales figures of another company. As a result i need to generate this dynamic cube and have it filled and exported as a local cube so that it can be downloaded for offline browsing.

any idea how I can achieve this is appreciated?
Thanks
Tom|||Did you ever figure out how to do accomplish this?|||yes, using CREATE CUBE statment, and specify the path and it will create it automatically