INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

(OP)
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.

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

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

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

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

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

(OP)
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.

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

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)

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

Quote (tdrclan)

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)

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

(OP)
I have the names in one table.

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

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close