SSIS Learning (1) Overview

As the saying goes: Microsoft has made every effort in five years. In 2005, Microsoft launched SQL Server 2005, which is another masterpiece after SQL Server 2000. This enterprise-level database solution mainly includes the following aspects: Database Engine service, data mining, analysis services, integration services, and reporting services, integration Services (SSIS) is a transfer station and link between them. It ETL data from various sources to a data warehouse and establishes multi-dimensional data sets, it then analyzes and mines the results and delivers the results to users at all levels of the enterprise through reporting services, ensuring the planning and decision-making and supervision of the enterprise.

The full name of SSIS is SQL server integration services. It is a Failover tool for Microsoft BI solutions and an upgrade to DTs in SQL Server 2000. Both functional, performance, and operational aspects have greatly improved. See the following operation interface.

SQL Server 2000 DTS

SQL Server 2008 SSIS

Nowadays, many people refer to SSIs as an ETL (extract-transform-load) tool. I personally think it is not accurate. Maybe everyone basically uses SSIS as an ETL tool, in fact, SSIS has surpassed the ETL function. ETL is only one of them, and it also has outstanding performance in other aspects:

(1) system maintenance:

A) database maintenance:

I. Database Backup;

Ii. update statistics;

Iii. database integrity check;

Iv. Index Reconstruction

V. SSIS package execution;

Vi. Ssas task processing.

B) business processing:

I. Execute an SQL task.

Ii. Web service task.

C) Operating System Maintenance:

I. WMI event observer task

Ii. File System task.

D) Others:

I. Execute SQL tasks

Ii. Execute Process tasks

Iii. ActiveX script task

Iv. Script task (Vb/C #).

V. Run the Web Service

In particular, the fourth point above allows you to execute SQL tasks, Web Service services, system processes, and (Vb/C #) script tasks, how much space does this give us to imagine? What are the exceptions? Strong. I have to admire you.

The SSIS architecture consists of four parts: Integration Services Service, integration services object model, integration services runtime and runtime executable files, and data flow tasks that encapsulate the data flow engine and data flow components ():

 

This is what beginners must understand. As long as they understand the system structure of the body, understand the relationship between the components, and understand what is the control flow and what is the data flow, it is not difficult to learn SSIs.

In short, SSIS is not simply an upgraded version of DTs. In addition to the improvements mentioned above, Microsoft continues to exert its advantages in the development environment, closely integrated with Visual Studio, developers can design and develop on a more familiar and convenient platform, greatly reducing the entry threshold and accelerating the learning and development progress. Its components are more object-oriented. Each package, task, control flow, and data flow are independent objects with corresponding attributes and events. Vb/C # script tasks; parameterization of variables and attributes is even more shocking, almost omnipotent and all-encompassing (I'm not an exaggeration, it just feels much more powerful than before ). It is not complex to use. As long as you have installed the SQL server integration services 10.0 Service (SQL 2005 should be integration services 9.0), new project, and select the integration services project, you will be able to fully utilize it, feeling His breadth and depth.