Preliminary understanding of BI-SSIS and BI-SSIS

Preliminary understanding of BI-SSIS and BI-SSIS

In the previous article, I briefly introduced the overall framework of BI. In the next few articles, I will briefly explain some knowledge points. First, we will go to SSIS learning.

1. What is it?

SSIS (SQL Server Integration Service) is an intelligent solution of Microsoft, popular point and ASP. NET program, it also designed a lot of similar controls, but called tasks, the main function of these tasks is to convert data from the data source to the data purpose, there will be a query in the middle, aggregation and other more specific tasks.

Ii. how to use it?

So how can SSIS be used as an intelligent solution? Next, we will explain in detail based on the instance:

Question stem: two table Structures

Explanation:

(1) employee table: store employee information

Employee: id (primary key auto-increment) name (employee name) birthday (date of birth) idCard (id card number) idType (id card type 1) empNo is employee id

(2) Employee_internet (Network employee table): Network employee information

Employee_internet: id (primary key auto-increment) name (employee name) birthday (date of birth) idCard (document number) idType (Document Type: Chinese id card) empNo is employee id

ETL process: Merge the information in the employee_internet table to the employee table according to the idCard (ID card number ).

Solution Process:

Analysis:

First, we can merge two tables based on idCard according to the question stem, but the idtypes in the two tables are different. This is the key to the problem. Therefore, we will first derive another field based on the document type and then merge it.

Process:

1. Create an SSIS project file

(1) In the Start Menu, find the SQL Server BusinessIntelligence Development Studio program under the SQL database file, and click Run program.

(2) On the open page, click "File> New> Project" in the upper left corner, select the "IntegrationService" project under the "Business Intelligence" project directory, and enter the name and location, click OK.

 

2. Open an existing SSIS project file

Find the folder to open the SSIS project and open the file. Find the file whose suffix is. sln and run the file

3. Page Introduction

Menu Bar, toolbox, design area, Connection Manager, solution Resource Manager, etc.

Menu Bar: All menu options

Toolbox: All Components

Design area: development component area

Connection Manager: Data Source used by the current SSIS package

Solution Resource Manager: allows you to view data sources and project files.

Supplement: The overall style is similar to that of Microsoft's C/S page.

4. Create an SSIS package

All SSIS development is completed in the package.

Create a package: In Solution Explorer on the right, right-click the SSIS package folder and choose create SSIS package to start ETL.

5. Next, separate the control flow and data flow based on the above questions.

6. Double-click the data flow task to set the data flow.

Now we will give a detailed introduction to the above data stream integration questions:

(1) The ole db source is the data of the source data table. in the question, it is employee_internet.

(2) search is to find the target table employee based on the source data, and connect according to idCard.

(3) The derived column derives an ID card type of idType from a different Column Based on the unmatched search output.

(4) The ole db Target outputs the derived columns and information of the two tables to the ole db target employee

(5) Click execute and you will see that the operation is successful. searching from the database will show that the information of the connected tables has been merged.

The above operation completes this small instance! It's easy. Let's try it!

Iii. Features

1. SSIS is not only used for simple data transmission and conversion, but also for integration, transfer, expansion, filtering, and correction of data created on the Business Intelligence platform.

2. SSIS not only has many built-in data cleaning functions, but also can be expanded through third-party controls, allowing users to develop custom controls through Visual Studio. NET.

3. SSIS can directly import data to the Cube of the SQL Server Analysis Service.

4. SSIS can be seamlessly integrated with SQL Server Reporting Services, that is, SSIS packages can be used as data sources of the report service.

Iv. Summary

The above is a brief introduction to SSIS and will be explained based on a small instance to better understand it! What impressed me most from this is that if you want to better implement the ETL process, you must be familiar with SQL statements and use graphs to display SQL statements, this makes it easier and quicker to build the target data source. However, it also provides the script writing method to implement the required functions, but it is difficult to maintain in the future, so you must use the script with caution.