SSIS is an extremely powerful tool by Microsoft for performing ETL. ETL or Extract, Transform and Load is simply the process of consolidating data from multiple sources. The transform part is optional but can be very useful for making sure data is in the correct format also known as data cleansing.

Here is an example of a SSIS project for copying data from one site using FTP to another site and then inserting it into a database which can be used for reporting and data analysis. The first piece uses what’s called a control flow where it uploads the data from the other site via FTP. The next part is the Data Flow, this is where the ETL happens. Data is read from the file in the previous step and compared with the existing data from the database. It is then split into different parts depending on if it’s a new row an update to an existing row or a previous row that is flagged for deletion. This is the process that is run after a new registration or an update to an existing profile.

Get registration gets the file using sftp and Ingest Data is the ETL part for inserting, updating or deleting data into the database.
This is the ETL Piece that updates the database accordingly.

SSIS is a powerful tool for prepping data for Business Intelligence purposes.