| Author |
Topic |
|
mitin
Yak Posting Veteran
81 Posts |
 Posted - 2013-03-05 : 06:48:44
|
| How would Iimport data into a SQL Server table from multiple other instances of SQL Server, all on different boxes on the same network?What the easiest method. This would be a scheduled import that happened at midnight every day...Also, whats the best option if I want to import from multiple flat files (csv or txt) that are also on different servers, how is this done?Info/opinions appreciated.Many Thanks,Adam |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 08:46:26
|
| 1. you can use ssis or sql stored procedures after setting up linked server connections.I prefer doing this in ssis as linked server would require admin granting you permissions For automated execution you can create a sql agent job------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-05 : 10:09:51
|
| Any recommended tutorials/articles for doing this in SSIS?Completely new to it.....Thanks!! :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-07 : 06:29:37
|
| How ca tis be done in SISS, to import from multiple remote databases into one database table, the only tutorials I can find seem to cover data frm just one database to another, not multiple databases to one....cheers |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 06:50:49
|
quote:
Originally posted by mitin
How ca tis be done in SISS, to import from multiple remote databases into one database table, the only tutorials I can find seem to cover data frm just one database to another, not multiple databases to one....cheers
are tables in all the databases have same structure?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-07 : 07:54:44
|
| each remote sql instance has two different databases, that have a slightly different table structure each. i want to extact from each of these two tables, for every instance on the network (theres over 60 instances)is this doable?thaks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 08:47:55
|
| you need separate data flow task for each of tables.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-07 : 09:08:23
|
| ok, well just for simplicitys sake, say it was jus one table from each of these remote databases, then how would I achieve this?I can't find anything on this... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 11:03:31
|
| use a single data flow task with OLEDB source and OLEDB destination. OLEDB source points to your source db and OLEDB destination points to destination db. for looping through your dbs you need a for each loop container and populate a object variable with list of dbs from query or hardcode values inside the for each loop for it to loop on. The data flow task will be inside this for each loop------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-10 : 17:52:41
|
| Can this be done in SQL Server 2005? (sorry for asking that I know that this is 2008 section)I've never written a for each loop before, not sure how this would be done atm, I' ll look into it, but any help/links to lessons/tutorials appreciated. And thanks for the input so far...Btw I think that it will be fine if I just hard code the Database names into the loop, theyre not going to change any time soon. I don't get how the 'data flow task' would be 'included' in the for loop though, I don't understand how it will be referenced in the code?One additonal question, as mentioned in addition to importing directly from 60 databases, I also need to import from CSV files from a similar number of servers, in order to do this, could I just create a seperate SISS package for each and then incude all the SIIS packages in a scheduled job somehow?thanks again |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 01:24:10
|
quote:
Originally posted by mitin
Can this be done in SQL Server 2005? (sorry for asking that I know that this is 2008 section)I've never written a for each loop before, not sure how this would be done atm, I' ll look into it, but any help/links to lessons/tutorials appreciated. And thanks for the input so far...Btw I think that it will be fine if I just hard code the Database names into the loop, theyre not going to change any time soon. I don't get how the 'data flow task' would be 'included' in the for loop though, I don't understand how it will be referenced in the code?One additonal question, as mentioned in addition to importing directly from 60 databases, I also need to import from CSV files from a similar number of servers, in order to do this, could I just create a seperate SISS package for each and then incude all the SIIS packages in a scheduled job somehow?thanks again
It can be done in sql 2005 alsoYou just need to drag and drop and data flow task inside for each loop container for that.yep you can create a seperate ssis package for moving csvs and put it inside a new job step inside same job. you can add steps dependeing on your sequence (server data movement package followed by csv data movement or viceversa)for csv transfer package see similar package i've created belowthe server data movement will also be in similar lines and will just require replacing file source with oledb sourcehttp://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
hasondea
Starting Member
3 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-19 : 11:40:07
|
quote:
Originally posted by hasondea
all the SIIS packages in a scheduled job somehow?
Yep You can add all the ssis packages as steps within same sql agent job .------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Import Data from Muiltiple Databases