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/.

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

6. 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".

7. 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

Hi ylsv,

I cannot see the image text

so not able to prepare probably stems from the fact there is no target db yet when the package runs.

Then set delayvalidation to true.

Besides, to move the database you can use the transfer database taks

There is also Transfer Objects Task, I just do not know what fits your scenario best.

Thanks. I did set DelayValidation = True but it still does not work. I do not want to use Transfer Database tasks a I want to create database and other things.