Microsoft SQL Server Integration Services also called as either SQL Integration Service or SSIS. The SSIS is a business intelligence tool that provides data transformation solutions for various organisations.
SQL Server Integration Services is an ETL (Extract, Transform and Load) tool. It means SSIS can be used to extract data from a wide variety of sources such as Excel Files, Flat Files, XML Files, Relational databases, and transform (slice and dice) them as per your requirements and finally load the data into the destination.
What is SSIS Work environment
Below screenshot shows you, What is SSIS work environment, and how this is used to create SQL Integration Services projects.
As you can see from the above screenshot, we divided the window into different parts
- Solution Explorer: This is a combination of project level connection managers, actual packages, and project parameters.
- Properties: Use this window to change the properties of each and every task.
- Toolbox: SSIS Toolbox provides a lot of built-in tasks, containers, transformations, sources, destinations, and administrative tasks to solve complex business problems. You can use these graphical SSIS tools by drag and drop those tasks in the work environment. It means you do not have to write a single line of code to perform most of the operations.
- Information: You can see the information of each and every item in a toolbox by clicking the toolbox items
- Connection Managers: This window is to create a package level connection managers
- Package: Design your package
SSIS Project Development
SQL Server Integration Services project is a combination of Connections Managers, Packages, and project parameters (optional).
SSIS is all about extract data from different sources transform data and load it in a completely different destination. We need connection manager to establish the connection between the SSIS package and source, and package and destination.
In SQL Server Integration Services, there are two types of connection managers and they are Package Level (created in a 5th window) and Project Level (created in Solution Explorer window). I suggest you to refer Introduction to Connection Managers article to understand them.
This is where we design the complete data flow. Here is the place where we perform all the transformations.
After the completion of the package development, you can deploy the package into the production environment.