Does anyone know how to set up a Foreach Loop container in an SSIS 2005 package to transfer (truncate & insert) a set of tables from server A to server B derived from a dynamic list that is housed on Server B?
Is this even possible?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thanks PedroCGD,
I may have not worded my previous question well.
Although the example you shared with me clears up the Foreach Loop part, I have had difficulty setting the dynamic Dataflow properties. After researching online have read in more than one site that it can not be done. One site suggests using bcp which I will be trying, unless you know of another way.
Thanks again
http://www.proteanit.
This is something that I've always wanted to have the ability to do, but have never found a way to do. You basically want the source and destination components to dynamically change for specific table names. You could try using the Transfer SQL Server Objects task and try to set the properties for it dynamically but I've never tried that so it might not work.
The reason you can't do it normally with OLE DB components is because the meta data is usually different for every table (even if the columns are exactly the same) so the pipeline won't be able to make the connection between them unless it knows the meta data ahead of time. The reason the Transfer SQL Server Objects task may work is because it moves whole objects and doesn't rely on the meta data underneath.
I just checked out the Transfer SQL Server Objects task and it is possible, but it requires copying the whole table over, not just truncating and re-inserting.
There's a property called TablesList that you can set using a variable. Because the property is a collection, you have to specify the string in a collection format. Say you have a table named "MyTable". The variable starts with the number of tables you're dynamically pulling. Then there's a comma, and then the length of the table name, then the table name itself. So your full string would be:
"2,7,MyTable"
This is a little complicated but it works. The problem is you would need to query a list of tables somehow and the for each loop doesn't really have a way to do that. I'm sure you could figure that out on your own though.
Guys,
I split the points between you two, since you both contributed to the conclusion, which is; that this can not be done exactly as I wished. However, using the FE Loop example provided by PedroCGD and instructions provided by stelth240 I should be able to build something slightly different.
Thanks
Business Accounts
Answer for Membership
by: PedroCGDPosted on 2009-06-09 at 07:47:01ID: 24581711
reymadrid,
I made an example for you.
Add the attached package to an existent SSIS Solution and update the cmListSource connection manager to the attached example textfile that has the example data.
Helped?
Regards,
Pedro
SSIS Interface
TextFile example
SSIS Package (rename to dtsx)