Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We currently have an SSIS package that exports about 70 large tables nightly, with truncate and load at the destination. Our tables keep changing, we sometimes add some tables and remove some and editing the SSIS package accordingly is tiring.

Is there a way to have a text file or a table with the list of table names in it and have an SSIS package to loop the table and perform the export? That way, it is easier to handle dynamic list of tables that get exported.

Any links, pointers are appreciated.

share|improve this question
    
Have you considered bcp/BULK INSERT instead? It's easily scriptable, as long as you don't need much transformation. –  Jon of All Trades May 21 '12 at 16:47
    
Are the tables that you are adding/removing always coming from the same set of tables? Or are you creating brand new tables? For how long do you anticipate you will have to be making these kinds of changes? –  WT_W May 23 '12 at 3:16

1 Answer 1

up vote 2 down vote accepted

what you are looking for is to dynamically create tables in the destination. or in other words Copying tables from Source to destination programmatically

here is a very useful article that transfers tables from access to sql dynamically but you can take the same concept and apply it on your source as well.

http://stackoverflow.com/questions/6298806/how-do-i-programmatically-get-the-list-of-ms-access-tables-within-an-ssis-packag

share|improve this answer
    
Thanks @AmmarR! this article answer is very useful! –  yogirk Jun 6 '12 at 12:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.