What is SELF JOIN and when would you use it?

A self join is simply when you join a table with itself. There is no SELF JOIN keyword, you just write an ordinary join where both tables involved in the join are the same table. One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous.

A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.

It is useful when you want to correlate pairs of rows from the same table, for example a parent - child relationship.

Some practical uses of SELF JOIN are to obtain running counts and totals, identify which data under a particular column satisfy a certain set of conditions, and generate another table by extracting data from the original table. 

select * FROM Table t1, Table t2 WHERE t1.Id = t2.ID

Example: Using SELF JOIN to find the products supplied by more than one vendor.

Because this query involves a join of the ProductVendor table with itself, two different aliases (pv1 and pv2) are used in the FROM clause. These aliases are used to qualify the column names in the rest of the query.

Syntax:
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID <> pv2.VendorID
ORDER BY pv1.ProductID

Why we use SSIS?

Here, are key reasons for using SSIS tool:

• SSIS tool helps you to merge data from various data stores
• Automates Administrative Functions and Data Loading
• Populates Data Marts & Data Warehouses
• Helps you to clean and standardize data
• Building BI into a Data Transformation Process
• Automating Administrative Functions and Data Loading
• SIS contains a GUI that helps users to transform data easily rather than writing large programs
• It can load millions of rows from one data source to another in very few minutes
• Identifying, capturing, and processing data changes
• Coordinating data maintenance, processing, or analysis
• SSIS eliminates the need of hardcore programmers
• SSIS offers robust error and event handling

What is SSIS?

SQL Server Integration Service (SSIS) is a component of the Microsoft SQL Server database software that can be used to execute a wide range of data migration tasks. SSIS is a fast & flexible data warehousing tool used for data extraction, loading and transformation like cleaning, aggregating, merging data, etc.

It makes it easy to move data from one database to another database. SSIS can extract data from a wide variety of sources like SQL Server databases, Excel files, Oracle and DB2 databases, etc.

SSIS also includes graphical tools & wizards for performing workflow functions like sending email messages, FTP operations, data sources, and destinations.

Source- https://www.guru99.com/ssis-tutorial.html