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.

Dynamic transformation mapping in DTS

Hi,

I need to export data from SQL tables to AS400 files(the SQL table has the
same file name and column names as the file on the AS400) .
I created a DTS that has the following tasks: dynamic properties task, SQL
server connection, transform data task and a other connection(ODBC data
source).
I'm using global variables to dynamically set the source and destination
tables names on the transform data task. The problem is the transformations
are not automatically mapped and I get an error message when the
DTS package is executed with a source and destination that has
different columns than the ones specified in the transformation.

Any ideas or possible workaround would be greatly appreciated.
Thank you very much.http://www.sqlservercentral.com/columnists/bknight/convertdb2.asp

HTH|||Hello,

Thank you for your reply, however I read the article for the link you supplied
and it doesn't solve my problem. My fault. I will elaborate and try to be clearer.

As you know, I need to export data from SQL tables to AS400 files(the SQL table has the same structure and column names as the file on the AS400).
I created a DTS that has 2 global variables for the source file and the destination table. I'm supplying a different source and destination file name every time I execute the DTS package(executed from a stored procedure).
In other words, on the first execution of the DTS, the global variables SQL server table A(source) and AS400 file B(destination) will be passed. On the second run, the variables will contain table name C and file name D. So on and so forth.
I need to re-create mapping every time the package is executed because I don't know the column names ahead of time.

In a nutshell, the data transformation task allows you to dynamically specify
a source and destination table name, but consequently, doesn't automatically generate the column mappings.

I don't think the problem lies in the fact that it's an SQL server to AS400 data transfer, but rather, how to create auto mappings based on a given source and destination.

Sorry if I'm being repetitive and I hope all this clarifies my issue.

Still hoping for a solution or workaround!
Thanks once again.|||In my experience Dynamic Mapping is not possible.

Can you link the databases and dynamically write the insert statement?

Can you create a vbsript to create a text file that always has the same layout but different sql source?

Then do your load on AS400 the same way?

Allow each system to control process on itself.

Just some possible suggestions.|||I'm afraid you're right about dynamic mapping not being possible.

Also, you seem to have some good suggestions to a workaround, however
you would need to elaborate because I don't know how to implement them.

Can you provide one or more examples?

Appreciate the response.