Hi There
I have a production server and a training server.On a monthly basis the training server needs to have data refreshed from the production server.This consists of approximately 1000 tables, with a few hundered thousand rows from many of the tables.
I was wondering what would be the best way to accomplish this in integration services.Obviously i do not want to create a task for each of the 1000 tables, and the data needs to be copied as fast as possible (it is a substatial amount of data).
I was thinking of loading a table with all the table names and using a loop to dynamically go throught the tables, but then i dont want to use a sql task to do a "select * from linked server into" as this would be very inefficient.
There are no real transformations needed, just copying many objects from one server to another.
There are other ways but i cant help thinking it is not the best way.
Any ideas on the best practice for this
Thanx
refresh data best way?
Hot Topic
- Using SSMS to Manage SSIS
- Is this method possible?
- Data-mining model process-error
- creating a database, tables, records, and fields in a Sql server 2005 express
- Replication Error 28559
- General network error. Check your network documentation
- Can't connect to Report Server via Management Studio
- DTC Transaction Abort error
- Performance - Slow load times for SSIS Packages
- Exception message: The operation was canceled.
SQL Server
- Dedupe Priority Logic (within Sort) - feature request?
- Upgrade to Advanced Services; full-text indexing not working
- Refresh the properties of as server
- How to Insert with select and additional value.
- Simultaneous DB access
- Another Sql 2005 Express Installation Problem
- Should this be an error rather than a warning?
- SQL error while installing CRM 3.0
- String truncate error
- Help-Forecasting Model Viewer
refresh data best way?
Timothy Butterfield
I have realized dynamically driving thorugh the table list will not work with a for each loop container using a data flow task , as the source can use dynamic sql but the ole db destination cannot be dynamic therefore i could change the destination with the source dynamically.
I tlooks like the only way is to loop through using a sql task with a linked server.
Any ideas
Thanx
Tim Williams
csekhar
There are no foreign keys, so that should not be an issue.
I agree i do not think it is feasible for SSIS in this situation, i have opted to go for Snapshot replication, easier to add and articles etc, just thought there may be a cool way to do it it in SSIS.
But thanx for the feedback!
Cheers
johnlinfa
Thanx for the assiatance.
Yes i did investigate the copy objects tasks. Sorry i should have mentioned that my concerns are that i do not want to copy all rows from these tables, is there a dynamic sql command one can use for object data extraction in this task
Also this list of over 100 tables may change, which would require me to change the package every time, is there a way to dynamically drive this task from perhaps a table holding the objects that need to be copied Perhaps this is possible in the copy objects task But i doubt it.
Thanx again for your help.
jhmckin
The other thing that woul worry me with only a partial data transfer would be integrity. If you transfer only 100 rows per table for example, what ensures that any foreign key relationships will not be violated, when you do not get matching data
I don't think a totally generic and automated solution is feasible, the type of data, static vs transactional will influence how they are treated. If the tables themselves are dynamic, with tables being added and removed, you will need to know what each table is, and therefore how to treat them.
Dessus
-Jamie