Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Import Data from Muiltiple Databases

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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!! :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 10:14:52
see an example below to import multiple files to sql server table

http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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...
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 also
You 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 below

the server data movement will also be in similar lines and will just require replacing file source with oledb source


http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

hasondea
Starting Member

3 Posts

Posted - 2013-03-19 : 04:35:58
all the SIIS packages in a scheduled job somehow?

_________________
runescape gold|wow gold|RS Gold|buy Runescape Gold

Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -