Hopefully someone can help, I have data from 60-70 tables I have to
move from one database to another on a weekly basis after a number of
tasks are performed on these tables, not all the tables in the database
are being copied though. Currently I have a package that does each
individual table in seperate data flow tasks (which works, but means I
would have to add new tasks or remove tasks if we have to add or remove
tables).

1) I have created a table inside the database that has a list of all
the tables that need to be copied, a SQL task then takes this list and
puts it into an object variable called oDELTA.

2)The next task is a ForEach Loop Container, which uses a ForEach ADO
Enumerator using the variable oDELTA ("Rows in First Table" enumerator
mode), their is one variable mapping which is a string variable called
strTable with an Index of 0.

This next step is where the error is occuring:
3)Inside the ForEach Container, there is an SQL task that has a
connection type of ADO.NET and has a parameter mapping of:
Variable name: User::strTable
Direction: Input
Data Type: String
Parameter name: @strTable

The SQL is a direct input statement "TRUNCATE TABLE @strTable"

4)The next step would be to copy the data across, I am looking at doing
an insert sql statement, but if I can't get step 3 to work there is no
point going any further.

Any assistance would be greatly appreciated, i have a feeling you can't
use a variable as a reference in a truncate statement, or you have to
state the variable another way.

No, in that case you'll have to build dynamic SQL like

DECLARE @t SYSNAME
set @t='Tablename'
EXEC('truncate table '+@t)

<bryan.dinning (AT) gmail (DOT) com> wrote

Hello bryan.dinning (AT) gmail (DOT) com,


Why are you using an ADO.NET connection manager? If you are going to SQL
Server then there are claims this is a lot slower than the OLEDB equivalent.
For your requirement though you should be looking at property expressions
for the SQLStatementSource property of the task.

If you need a hand with that then let us know.

allan