Sql Command from a variable to import data dynamically using ForEach Loop and Variablesin 2012

SSIS and Import/Export
1

Hi,

I am new to SSIS. I need to move all the data and objects from one server to the other server. We actually archive the data onto a different server. So I need to move the entire data into new database ( the database name varies depending on the year of the archive ex: Test2013, Test2014 etc). For this I have created Execute SQL Task to create new database and copied the table structures from the package. Now to copy the data from multiple tables into the same tables on the new database, I have done the following steps:

  1. created a Execute SQL task to get the list of table system objects.

  2. Created a ForEach Loop container to process these table one by one.

  3. Created a Data Flow task in the ForEach Loop container and I am stuck here in creating the sql command from variable as I do know the database name ( as it varies every year) and also the table that Foreach Loop is passing. I have followed the steps from http://beyondrelational.com/modules/2/blogs/101/posts/13354/ssis-full-result-set-to-a-set-variable-of-object.aspx till here.

  4. This is the query I have in my OLE DB Source "SELECT * FROM ServerA.Database1" + @[User::DestTableName]. For this step I followed

http://blog.stevienova.com/2009/04/30/ssis-two-ways-using-expressions-can-make-your-life-easier-multi-db-select-non-standard-db-select/.

  1. The problem here is I do not have the value for DestTableName variable @[User::DestTableName] as I get this from ForEach Loop.

  2. I get the query from the variable like this SELECT * FROM ServerA.Database1. and obviously it is unable to prepare the statement. It gives me error saying "Statement could not be prepared".

  3. I have created 3 variables. One for the result set object for all systables, one for TableName and the other one for SQL query variable.

I really appreciate if someone help with this.

Thanks in advance

ylsv