Below three important projects help to complete the business intelligence task.
SSIS: It helps to perform the wide range of data migration tasks and collect data from various data sources and store them into a central location.
It performs three important things. Extraction, Transformation, and Loading.
Extraction (E): Collection of data from different sources.
Transformation (T): A different form of data obtained from different sources and it converted according to business need.
Loading (L): Data warehouse contains the loaded data.
SQl Server Integration Services included two studios
SSDT: SQL Server Data Tools
It helps to develop the integration service packages and We can perform the following jobs in SSDT
Step 1: Open the SQL Server data tools.
Step 2: click on File –> New –> Project
SSMS: SQL Server Management Studio
It helps to manage packages in a production environment and we can perform the following jobs in SSMS.
An SSIS package is a collection of Control flow and data flow ,control flow includes the two things such as task and data flow task and Data flow includes source, transformation, and destination.
SSIS Expression is a combination of literals, identifiers, and operators
Literals: An interpreted variable is known as literal, different type of literals are
Creating event handler is similar to building a packages and SSIS event handlers are
Example: Let us consider two excel sheets as follows
Translate the File 1 into second excel i.e, File 2 sheet compatible format
Step 1: Create a new project
Click file –> new –> project. Select Integration service from the group.
It will open up the SSIS designer which we will use for creating and maintaining Integration service packages.
In the SSIS package folder, we can see the default package created with name “Package.dtsx”.
Step 2: Create a connection manager to excel sheet.
Select excel and click on add,Select the browser button and choose the excel file path
Step 3: Rename the connection manager.
Right click on the connection manager and change the name as SourceExcelManager.
Step 4: Repeat step 3 and create other connection manager and pointing to the resultant file. Change the name as DestinationExcelManager.
Step 5: Create control flow: transfer data from source excel to destination excel.
We should select the control flow in the SSIS designer.Drag data flow task from the tool box to designer
Rename the data flow task into “source excel to destination excel transfer task”.
Flow control: It helps to define the workflow and also control the flow of execution.
Step 6: Create the data flow
It helps to define the flow of data between source and destination.
Step 7: Create Excel Source
Choose excel source from source group and place it in the designer.
Step 8: Configure Excel sheet
Double click the excel source.
Select Data Source as “SourceExcelManager”, Data Access Mode as “Table or View” and Name of the sheet as “DataSheet1”
Step 9: Create derived column.
In this toolbox from Transformation group drag Derived column to SSIS designer.
Step 10: Connect Source to Derived column
Click on this arrow and connect it to derive column.
Step 11: Configure the derived column
Double click on derived column.
Write down Derived Column Name as Name, Select expression as Title + “ “ + FirstName + “ “ + LastName
Step 12: Create Excel Destination
Add excel designation in SSIS toolbox.
Step 13: Attach Derived column to excel destination.
Follow the step 10, connect derived column and excel destination.
Step 14: Configure Excel Destination
Double click on the excel destination
Locate connection Manager to “ExcelConnectionManager”, the Data Access mode to “Table or View” and Name of the excel sheet to “Datasheet1”.
Click on mapping
Step 15: Execute package
We can find the output like below
Download Interview Questions asked by top MNCs in 2019?