34 Load data in batches from one server to another server in SSIS
ฝัง
- เผยแพร่เมื่อ 4 ต.ค. 2024
- Load data in batches from one server to another server in SSIS
Download the file\script used in the Video from below link
drive.google.c...
Incremental load using lookup transformation: • 01 Incremental load in...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
Load data in batches from one server to another server in SSIS
How do I import data from one server to another in SSIS?
How do I load multiple files in SSIS?
How do I copy a SSIS package from one solution to another?
How do you do batch processing in SSIS?
Happy Learning.
If you have any questions or suggestions please comment on the video or write to me at “aqil33@gmail.com”
For a static table where you have ability to alter source this is viable, but for a table that could change during load or you can't alter source this won't work. Best to split the source into smaller chunks by primary key.
Hi Aqil, Thank u very much for considering my request and making this video.
It's my pleasure
super! Well explained on batch loading to destination
Thank you Abhilash.
I have a request to create a video on how to load multiple Xml files from sftp server to Sql server. please consider my request & make a video on this.
Thanks for your request but I don't have access to any SFTP server at the moment.
Hi Aqil, Excellent video, Thank you !!
Can we use 'Maximum insert commit size' property of OLE DB destination to do batch processing.
How to improve SSIS performance using 'Maximum insert commit size' property?
Can you pls tell me other different methods to do batch processing?
No 'Maximum insert commit size' can't be used for batch processing. You can try setting different values for 'Maximum insert commit size' and see whether it makes any difference or not. I have created few videos on batch processing and other than this video below are 2 more videos about batch processing or updating
88 How to export 50000 records to multiple csv files using SSIS: th-cam.com/video/Rh_jG_h_Q48/w-d-xo.html
33 How to update a large sql table in chunks: th-cam.com/video/lf65PhqE-vk/w-d-xo.html
I will try to make some more videos on it when got a chance.
@@learnssis Thank you Aqil sir !! 👍
You have mentioned that you will make a video for data migration with optimize way... I have been waiting for long time.when can I get it.Thanks alot
Hey Sohaib, I am so sorry for that. At the moment I am out of country and busy on some important task from office, thus not getting much time to do it. I will try to make video on this topic next month.
Hello Aqil,
Can you please make a Video on below.
1. Create SSIS package for initial load till a particular date, For example: if we have data from 01/01- 13/06 in source, for 1 offload. I want to load data till only 01/06. The remaining Data should be loaded through the incremental load.
2. Can you please show how to transfer a file using SFTP protocol using SSIS. I tried using WinSCP and FileZilla but it didn't work.
Thanks in Advance
Hi Sriramchandra,
1. For this requirement I would suggest you to write a stored procedure and in the stored procedure we can check the max date from table and then accordingly we can get the month from max date and if you want to select data till 1st day of that month, then you can easily put a where clause in the stored procedure and select only required data from it. Once this data has been loaded, rest of the data can be easily loaded using incremental load.
2. We don't have any inbuilt component to transfer a file to SFTP using SSIS. We would need to use C# code for this. Unfortunately at the moment I don't have access to any SFTP server thus I won't be able to test it right now. Sorry for that.
Instead can we not use fastload in OLEDB destination and set the maxrowsperbatch / max commitsize to do the same. which of these is better?
Fastload in OLEDB destination and set the maxrowsperbatch / max commitsize to do the same won't work like this. You can try that method it will pull all data once from source but will try to commit in chunks as per the maxrowsperbatch. And in case if it got failed in between then you will have hard time to identify which records got inserted to dest and which records did not get inserted.
Hi Aqil!
Can you make an SSIS package where you are going to extract data on SQL Server Database to excel per 1 million rows.
Like for example: 2.6 million rows will be extracted on 1 excel file with 3 sheets. The first 2 sheet will be 1 million and exceeding data (600k) is on the third sheet.
Or maybe instead of sheet. you will extract it to multiple excel files applying the logic there.
In this video I have shown how to extract data from a large sql table to multiple csv files and each containing 50 k records.
88 How to export 50000 records to multiple csv files using SSIS: th-cam.com/video/Rh_jG_h_Q48/w-d-xo.html
You can get the idea from here and can try to change 50 k with 1 Million and instead of CSV files, we can use excel file.
Awesome @@learnssis! This is what I needed. Thank you!
@@markmanalo6353 Glad to know that.
This video is excellent but unfortunately i can't implement simply because I only have a SELECT privilege on my source so I cannot create the part column. My source has over 50 million rows. I spool effortlessly sometimes but the data keeps growing and I will soon be out of memory. Is there a method I can use that will not require me to create the part column?
Hi, yes there is another method as well which does not require to add a column to the source table
th-cam.com/video/RbNMr4zKxYs/w-d-xo.html
I want to transfer million data into batches from Table A to Table B. Table A,B both are inside the same database. What would be the query?
What I would do is that I will create a staging table with Id column from source table and another column will be created using NTILE method from sql server. For example you can create 100 ntile for your data, ntile means parts, and now you can create an index on ID and ntile and then you can run a for loop whose max value will be 100 for this case and you will select the data from source table joining the staging table on id where stagingtable.ntile =1 for first iteration thus it will select only 1st part from the source table and you can insert that data into destination table and so on, I will try to make a video on this one.
@@learnssis Sure, please make video
@@tejaskurle5954 Here you can see how to do this using SSIS
th-cam.com/video/RbNMr4zKxYs/w-d-xo.html
@@learnssis Thank u so much