What is ‘SSIS’:
- · SQL Server integration services (SSIS) is a tool used to Extract, Transform and Load (ETL) data in different databases.
- · SSIS is a Microsoft product lunch with SQL Server 2005. Prior to this it was known as DTS services.
- · Install SQL Server 2005 or any other version later to it. This will automatically install SSIS Engine to your system.
- · Open SQL Server Business Intelligence Development studio (BIDS) to create SSIS package.
- · To create a SSIS package first a Solution and a SSIS Project need to be created.
- · A solution is a collection of different types of project like: SSIS project, SSMS project, SSAS project and different C# projects.
- · A SSIS Project is a collection of SSIS packages and other files related to the project.
- · To create a solution go to File->New->Project
- · Click on Integration Services Project.
Getting familiar with BIDS:
- · The following screen will appear once you click OK.
- · The following panes are present inside the BIDS:
o Solution Explorer
§ Control Flow
§ Data Flow
§ Event Handlers
§ Package Explorer
§ Connection Manager
· Shows all the Data Sources, Data views, Packages and files preset inside the solution.
· Shows the property of the selected component.
· Contains different tasks when the Control flow tab is open.
· Contains different transformations when the Data flow tab is open.
· Contains all the user defied and system defined variables present inside the package.
· Package is a collection of task that is to be performed to do the ETL.
· It’s a XML file containing all the ETL instructions inside the XML tags and saved with .dtsx extension.
There are four tabs present inside the package area:
· Contains all tasks present inside a package.
· Task is the granular level of work that a package performs. For example: Execute SQL Task, Data Flow task, Send mail task
· Data flow contains source where the data need to be extracted, transformations which modifies data according to the business requirement and target where the transformed data going to reside.
· Event handler also contains task like the control flow. The only difference is the tasks will trigger only when the associated event happens.
· The events can be associated on the package level, container level or on the task level.
· Contains the hierarchical structure of all the components present inside a package.