mmartin1 (11/1/2012)
I'd like to copy these tables nightly to another database using SSIS. Since the amount of tables can increase I am wondering if there is a way to do so dynamically so that the program is aware of when another table gets added.
I would approach this slightly differently.
First, pull the tablelist into a package object for Recordsets with something similar to the following:
SELECT [name] FROM sys.sysobjects WHERE [name] like 'mytable_%'
Next, fire up the forEach loop like you've done, feeding said result into another string package variable. Now, for the internals of the loop:
Quick script to feed a full SELECT * FROM <packagevariable> statement (full query) into yet another string package variable. This will be fed into the dataflow for the OLEDB source as an expression for the SQL statement. You can adjust the target table in a similar way via expressions and the original table's name.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA