Initial knowledge of SQL Server Integration Services (SSIS)

1 What is Microsoft SQL Server Integration Services (SSIS)

Microsoft SSIS is the platform for building enterprise-class data integration and Data transformation solutions. You can use Integration services to solve complex business problems by copying or downloading files, sending e-mail in response to events, updating the Data Warehouse, cleaning and mining data, and managing SQL Server objects and data. Packages can be complex business requirements, either individually or with other packages. Integration Services can extract and transform data from a variety of sources, such as XML data files, flat files, and relational data sources, and then load the data into one or more target data carriers.

SSIS includes a rich set of built-in tasks and transformations, tools for building packages, and integration services for running and managing packages. You can use the Graphical Integration Services tool to create a solution without writing a single line of code, or you can extensively integrate the service object model to programmatically create custom tasks and other package objects for packages and code.

2 developing SSIS with Visual Studio

In the Start menu of the operating system, locate SQL Server Data Tools, which I am using in SQL Server 2012.

2.1 Creating a new SSIS Project

In the new project, we create a new Integration Services project, then fill in the project name, click OK to start the development of our SSIS.??

2.2 Development Tools Settings

Set up our work environment, the first time you load should have some irrelevant window, basically do not need to shut down. Too many useless windows will affect our work,??。

The next step is to create our first Data Flow task.

3 Start SSIS Development 3.1 Create an SSIS Data Flow task

In the SSIS Toolbox, locate the Data Flow task and drag it to the design panel.

3.2 Creating a Data Flow task

You can open the development design panel for the data flow by double-clicking the Data Flow task.

3.3 Adding a data source

Because my test project is using SQL Server as a data source, we need to add an OLE db source.

3.4 Data Connection Manager 3.4.1 Add Connection Manager

In the solution Manager, we can see a connection manager, right-click New manager, and in the Add SSIS Connection Manager pop-up window, locate the OLE DB connection Manager click Add.

3.4.2 Configuring OLE DB data Connections

Configure connection information for the source database

3.5 Configuring a connection to a data source

We see that our OLE db source has a red error message because we did not add a data connection to the data source. We double-click the icon for the database to open the OLE DB Source Editor. The following steps are described:

1) in the OLE DB connection manager, we select the data connection we just built

2) There are many "data access modes", namely "table or view", "table name variable or view variable", "SQL command", "SQL command in Variable". Here I choose "table or view", because I just do simple data manipulation, if it is complicated related operation can choose other way.

3) in the drop-down selection for table or view name, select the data source table that we want to manipulate.

After the configuration is complete, the red error message disappears.

3.6 Data Destination Configuration 3.6.1 Add OLE DB data destination

In the SSIS Toolbox, we can find the OLE DB destination, then drag and drop it into the design panel and connect the OLE DB source green data line to the OLE DB destination data source.

3.6.2 Configuring connection information for OLE DB destinations

Note: The connection configuration information is the same as the OLE DB source configuration, so you can refer to the OLE DB data source connection information operation.

3.6.3 Data Map

Double-click Open OLE DB target, we can find "map", we can see "input column" and target column after selecting, we will connect "input column" to "Target column". Note: Drag lines directly

3.4 SSIS Package

After the successful operation of each of the above steps, we can run the SSIS package to verify that our functionality is running correctly and produce the correct results.

3.4.1 Running the SSIS package

Right-click the SSIS package, and then select Execute Package.

3.4.2 Successful execution

When we see the green tick, it means that our SSIS package is running successfully!

The first knowledge of our SQL Server Integration Services (SSIS) is here, and then there are more exciting content, need to be concerned about.??

Initial knowledge of SQL Server Integration Services (SSIS)