Full Load in ETL is loading ALL the data from the source to the destination. A target table is truncated before loading everything from the source. That’s why this technique is also known as Destructive Load.
In full load first we truncate the destination table and then we load all the data from source to destination. It is the simplest method to load the data from source to destination.
Suppose if the file is very large, for example there are 200m to 500m records to load, so is not possible to load much amount of data in a very small time because some time we have very small duration so we can just update the data during night-time and in the night-time, there are very limited hours, and the file is very huge it is not possible to just reload everything.
In those scenarios where the actual updated records are very less but the whole data is very huge, we go with the incremental load, or you can say differential load.
In the incremental load, we figure out how many are the once which can be updated to destination table and how many records are the once in the source file or source table those can be inserted to the destination table, we just update or insert to the destination table, so this is called incremental or differential load.
The ETL Incremental Loading technique is a fractional loading method. It reduces the amount of data that you add or change and that may need to be rectified in the event of any irregularity. Because less data is reviewed, it also takes less time to validate the data and review changes.
Now you all understand about Full Load and Incremental Load in ETL, Let’s understand with SQL server integration (SSIS).
SQL server integration (SSIS):
SQL Server Integration Services is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a data warehousing tool used for data extraction, transformation, and loading.
Step1: FULL LOAD
First, we create a package for full load and understand how full load is work in SSIS.
Using execute SQL task we can truncate the table. In the SQL command we write the query for that.
Step 2: then we use data flow task for our ETL process.
In the data flow task, we used flat file source and OLE DB destination.
Then we configure our source and browse of source file from location.
General – used to fetch file from the location.
Columns – we can check our datasets.
Advanced – we can check datatype of each column and update them also.
Step 3: Now we configure our destination connection with selecting proper server name, database name, and table name.
Using mapping tab, we can check mapping of column between source and destination.
Step4: Now we create one table in our database.
When we execute the package, it first truncate the destination table and load all the data into destination table.
So, full load is straightforward way to load full data, lets connect with you in next blog with Incremental load example in SSIS.
Please share your thoughts and suggestions in the space below, and I’ll do my best to respond to all of them as time allows.
for more such blogs click here