22 Import tables from different servers using SSIS | Select data from multiple servers
ฝัง
- เผยแพร่เมื่อ 9 ก.ย. 2024
- Import tables from different servers using SSIS | Select data from multiple servers
Download the file\script used in the Video from below link
drive.google.c...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
Import tables from different servers using SSIS
How do I merge two tables in SSIS with different servers?
How do I import data from one server to another in SQL?
How do I transfer data from one database to another database in SQL Server using SSIS?
How do I move SSIS from one server to another?
If you have any questions or suggestions please comment on the video or write to me at “aqil33@gmail.com”
Hi Aqil, Your videos are all awesome, Kindly upload a video for how to increase the performance for package and also upload what is the best way to import millions of records to SQL destination table in SSIS.
You have column named "Active" but it doesn't show up in the table. Why?
If you go to this location at google drive
drive.google.com/drive/u/1/folders/1eRTAFvfpE30NGBhiOlZaaucFo3RIfwlb
and open the 01_CreateTable.sql file, the config_Server sql server table contains the Active column. This column is created incase you don't want the ssis package to execute for all servers so you can set them to 0.
But if I need to create Config server table in different destination server? Not sorce server?
Then how the ssis package will know to which server to connect to get the config table ?
Hi, How to import Excel with a lot of merged columns and merged rows into SSIS and connect them into MySql database?
It's an interesting question and honestly speaking I never imported such kind of file. At the moment I am out of station but will check once I am back.
Can you please share a sample excel file with few records to aqil33@gmail.com so that I can test it.
thank you very much
You are most welcome.
Hi Sir,
Suppose have 6 servers, every server having 3 databases in 3 databases having one same table. from this table using 3 select queries we will get different data like activeyes,activeNo, and activetotal. this whole data needs to load into one CSV file.
After this will get new server.for this server also use same machanism. Without changing in the package how to add new server configuration in Package.
If you have only one table from which the data will be exported then you can do this using a foreach loop container with one data flow task.
1. Create a sql table like Config_Server on any of the server. The table will have ServerName, DatabaseName. Now insert a record for each server and corresponding database. Thus for 6 servers the total entries will be 6*3 = 18 records.
2. Now use a foreach loop container with ado enumerator, and in the execute sql task before foreach loop container, use a select query and select the ServerName and databasename from the above config_server table. And create 2 ssis variable ServerName and Databasename and assign the value of server name and database name to the ssis variables inside the foreach loop container.
3. Now take a data flow task inside the foreach loop container and use the OLE Db source where you can select data from the sql table. Create an OLE Db connection manager, and make the connection manager dynamic by setting the ServerName property in expression from SSIS variable servername and InitialCatalog property dynamic from DatabaseName ssis variable.
4. Use the flat file destination to write the data to a csv file, make sure you uncheck the option owerwrite data in the flat file destination.
5. This way the loop will run 18 times and will append the data to a single csv file.
In below video, I have taken data from multiple databases and merged to a single csv file, you will get an idea what I am talking about here
th-cam.com/video/4fnTxqWoPIc/w-d-xo.html
If you are using the Cofig_Server table, then in future if you get a new server then you don't need to change the ssis package instead you can just add an entry for new server into config_server table.
What is a SQL Server query? Opening all tables in databases across all servers
You can use below query. This is for current server.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
select top 0 *
into #temp
from INFORMATION_SCHEMA.TABLES
insert into #temp
exec sp_msforeachdb 'select * from [?].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN (''internal'') AND TABLE_CATALOG NOT IN (''master'',''tempdb'',''msdb'') '
--select * from #temp
DECLARE @TABLE_CATALOG NVARCHAR(MAX), @TABLE_SCHEMA NVARCHAR(MAX), @TABLE_NAME NVARCHAR(MAX)
DECLARE cursor_query CURSOR
FOR select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME from #temp
OPEN cursor_query;
FETCH NEXT FROM cursor_query INTO
@TABLE_CATALOG,
@TABLE_SCHEMA,
@TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM ' +@TABLE_CATALOG+ '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME +''
EXECUTE sp_executesql @query
--PRINT @query
FETCH NEXT FROM cursor_query INTO
@TABLE_CATALOG,
@TABLE_SCHEMA,
@TABLE_NAME
END;
CLOSE cursor_query;
DEALLOCATE cursor_query;
This works well when you the package from Visual Studio On Dev machine because the variable that you assigned has the server or database name that you connect to. Consider a scenario when you deploy this package on the production or UAT environment. The variable which holds the database name does not exist or has connection to the one we assigned at the time of development. The package fails saying the connection error or TNS error in case of oracle server connection. If you try to assign the servername variable.
I am not sure if this will work for Oracle or not as I don't have experience working with Oracle, but this will surely work for sql server connections. As the OLE DB connection manager can easily be changed at runtime with the help of ssis variable. You just need to pass the ServerName property to the OLE DB connection manager.
th-cam.com/video/QDSRzGxLe1g/w-d-xo.html
In Azure ADF we have Copy Data, I can use it to copy data from multiple source tables to many specific table within 1 pipeline because of it's dynamic variables. So I wonder if SSIS have the right tool for that?
In SSIS we have transfer database object task, where you can select the tables you want to copy from one database server to another
th-cam.com/video/YXeEZsGmJrI/w-d-xo.html
Hi How do you Extract information from AdventureWorks2017 database and load it to another Warehouse Database using SSIS?
You can create an OLE DB Connection pointing to AdventureWorks2017 database and create another OLE DB Connection pointing to WareHouse database.
Now in the Data flow task, take an OLE DB Source and use the OLE DB Connection for AdventureWorks2017 database, select the table to copy the data from.
Take an OLE DB destination, use the connection for WareHouse database and select the table to which you want the data inserted to and you can execute the package.
@@learnssis if Let's say AdventureWorks2017 database has 4 tables and the destination database eg a database called DateWarehose has has no tables, how could you extract information from Adventureworks database to Warehouse database which has no tables?
@@angieotuke4167 Before loading the data into datawarehouse database, first you would need to create the empty tables in the destination database only then you would be able to load the data into those tables.
Awesome video. Thank you. I am wondering how this can be executed if the table names from the different servers are different. In your scenario, the table names are all ZipCode. What if One was [dbo].[ZipCode_India_Staging] and the Other was [dbo].[ZipCode_USA_Staging]?
If the column names and their data type are same then this can be done using the same approach, the only change will be that we will need to pass the table as SSIS variable and we need to give appropriate value of SSIS variable while fetching the data and while inserting it to a destination table.
Hi bhaiyya .. 100 tables every time drop and create... How to insert the data one server data base 100 tables to another server data base 100 tables... Tell me good approach bhaiyya?
Hi Suresh,
in below 2 videos I have shown the methods to transfer multiple tables from one server to another
th-cam.com/video/YXeEZsGmJrI/w-d-xo.html
th-cam.com/video/-GEShPKmlTw/w-d-xo.html
@@learnssis thanqsomuch bhaiyya.. I will go through the below videos
how can we do same using ado.net connection which created through ODBC driver?
In the foreach loop container, inside data flow task, You can take the ado.net source and there you can select a .sql file which will contain the source sql query to select data from sql table. Before the data flow task, use a script task which can write data to .sql file, here for each table the .sql file will be over written.
@@learnssis I'm reading connection I mean ado server name and ado database from table and create connection string and it function by assigning variables to server name and initial catalogue as you did in video but it's not working.. I'm using SQL task to do this activity because of my project requirement. Please share video link you have or suggest something..
@@gopika119 If you are using the ODBC driver then the server name and database name won't change in the foreach loop container. If you are trying to export the data from single server then you can generate the .sql file which will contain the sql query to pull the data and that .sql file can be read inside ado.net source, in the .sql file we will write the query like below
select * from databasename..tablename
But if you are trying to export data from multiple servers then it won't work and you should think of exporting it using C#.
sir,
How to import files from multiple sources into data warehouse?
It will depend what is your source, if your source is a flat file then you would need to use a foreach loop container with File enumerator to loop through files in a folder but if your source is a sql server database and you want to import data from multiple servers then you can use a foreach loop container with ado enumerator to loop through multiple servers\tables.
Could you please tell me How to incremental and full load from two different servers in package creation
You can watch below video how to do incremental load. You can use a connection which will point to one server in ole db source, while in destination you can use another server connection
th-cam.com/video/8dRIjVhKoKs/w-d-xo.html
How can we do it with servers which are connected through static ip?
If you are able to connect to another server from sql server management studio then you can connect from SSIS as well. If you are not able to connect to another sever from SSMS then it won't work from SSIS as well.
Hi Aftab,
Great video as always, can you please do the same thing for the multiple tables from two servers? and also add one extra column for each table and assign a value? thanks
Hi Muhsin,
My name is Aqil Ahmed. And if you want to import the same table from multiple servers then just add the details of both servers in the Config_Server sql table and it will fetch the data from that server.
Nice video , but I need to import data from one server database table to multiple server database table , is it possible?
Yes, you can do it, if you want to write it to multiple destination servers then you can insert the destination server details in a sql table and then loop through that table and get the server name from and change the destination ole db connection managers.
can i set dynamic database server and database name in OLEDB destination under foreach loop container?
@@avdheshkumar011 Yes you can do that.
th-cam.com/video/MZfpiEYHu3I/w-d-xo.html
This works only with same no of columns & same col name, can you please show with different column name & with difference in column number like 1 table having 3 column & other table having 5 column
Hi Surya,
If you want to import multiple tables with different column names then either you can try this method
th-cam.com/video/YXeEZsGmJrI/w-d-xo.html
or try this method
th-cam.com/video/-GEShPKmlTw/w-d-xo.html
@@learnssis thanks ❤️❤️
Can you please make a video on migrate SSIS package which was developed on older versions to new version.
How many ways we can do it.
Steps we need to consider while doing this.
Yeah its a point to make video on, I will try to install an older version of Visual Studio and then will try to make a video on this one. Thanks.
@@learnssis thank you
Can you please make a video on Protection levels in SSIS.
Yeah it is a good topic as well, I don't have a video on protection levels in SSIS on my channel, I will try to make one shortly. Thanks.
@@learnssis thank you