Microsoft SQL Server Integration Services also called either SQL Integration Service or SSIS. The SSIS is a business intelligence tool that provides data transformation solutions for various organizations.
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. Furthermore, transform (slice and dice) them as per your requirements and finally load the data into the destination.
What is SSIS Work environment?
The below screenshot shows, What is an SSIS work environment, and how this is used to create SQL Integration Services projects.
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 every task.
- Toolbox: SSIS Toolbox provides a lot of built-in tasks, containers, transformations, sources, destinations, and administrative tasks to solve complex business problems. Use these graphical SSIS tools by drag and drop those tasks in the work environment. It means we do not have to write a single line of code to perform most of the operations.
- Information: Shows the information about toolbox items
- Connection Managers: This window is to create a package level connection managers
- Package: Design SSIS package
What is 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 a 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. They are Package Level (created in a 5th window) and Project Level (created in Solution Explorer window). Refer Introduction to Connection Managers article to understand them.
It 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, we can deploy the package into the production environment.