use one ssis package to copy multipule tables (w same columns) to another DB (linkserver)

[tdrclan]

we have a third party DB that creates a table for every new group added.

ex group1 will have a table clmsgroup1
group2 will have a table clmsgroup2
etc

all these tables in this database have the same column layout.

how do I create a ssis package use a list of table names as a var? ( to load data from third party DB into SQL)

these table are listed in the linkserver.
or are views (using the linkserver)

I tried to create an ssis package with a loop but I can't get the ssis package to compile without complaining about the ? used in the select * from ? statement.

this is the only thing I can't figure out. I need to send the table name as a var and have the select use that name.

TIA

Tim

 

[fredericofonseca]

use execute sql from variable, and build a variable containing the SQL you desire

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[tdrclan]

do you know of any examples, videos, tutorals, etc... I think I tried that but was not able to get it to compile with having issues with the variable, is the whole select statement need to be the variable or only the table name.


Tia
Tim.

 

[tdrclan]

I'm using SQL 2008 and VS 2008 and not sure where the 'exec sql as var" is located

 

[fredericofonseca]

exemple

http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/2480.aspx

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[tdrclan]

Thanks, this is a start.
I'm still not sure how to build this or where to create the variable.

 

[tdrclan]

execute SQL task
resultSetType ResultSetType_None
sqlsourcetype variable
sourceVariable insert into luminxclaims_temp select *, CLYEAR + CLMONTH + CLDAY + CLACLMNO from @User::TablNames


ExecValueVariable User::TablNames


validation error
failed to lock variable "insert into luminxclaims_temp select *, CLYEAR + CLMONTH + CLDAY + CLACLMNO from @User::TablNames"
for read access with error 0cc0010001 "the variable cannot be found this occurs when an attempt is made to retrieve a veriable from the
variables collection on a container during exection of the package and the variable in not there.

 

[TheBugSlayer]

Try this

1- In the variables' definition, set
"insert into luminxclaims_temp select *, CLYEAR + CLMONTH + CLDAY + CLACLMNO from " + @User::TablNames
as the expression for sourceVariable, which you should probably name SQLStatementVar or something along those lines. Notice how the TablName variable is NOT enclosed in quotation marks.

2- In the variables' set MyTableName as the value for TablNames (this value can be changed in a loop then).



MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

these table are listed in the linkserver.
or are views (using the linkserver)

Are the names of the tables you need to process contained in another table? Or is it that you know which tables from that server to process? The solution I am going to propose depends on the answer.



MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[tdrclan]

I have the names in one table.

I think I should close this post and only withthe new post I have started.

 

[tdrclan]

we can close this

I recreated the package from scratch and it is now working.

it appears that when the variable was changed/added after the package was built the messed up the variable so that the package could not work.