SQL Server 2005 Integration Service

SSIS (SQL Server integration Service) is a very large new tool for improvement on the basis of DTS in SQL Server2000. Using the data flow and control flow separation, design and deployment of the separation of the architecture design, introduced more new transformations, and finally to professional-level ETL tools to a large part.

Because of the relatively large improvement in DTS, the development and use of SSIS is slightly more complex than DTS. But for large projects, SSIS is actually much simpler than DTS.

This article demonstrates the entire process of developing and deploying an SSIS package with one of the simplest examples.

1. Development

SQL Server2005 a high degree of integration with the client's management and development tools. For database management and T-SQL development, use the new SQL Server Management Studio; for SSIS,SSRS (SQL Server Reporting Service), SSAS (SQL Server Analysis Service) is developed using SQL Server Business Intelligence Development Studio.

Open Business Intelligence Development Studio, create a new project, template Select Integration Services project, project name Ssistest.

Select the Data Flow tab to build a new stream. Select a source from the data flow sources, where we use the SQL Server database as the data source, so select Ole DB source and press the left ARROW to drag to the workspace. Because the source has not yet set the connection information, the icon has a red fork, indicating that its status is invalid.

No, let's start with a new connection. Right-click in the Connect Managers workspace, select New OLE DB Connect, and then click New to set the database you want to connect to.

Back to the Data Flow workspace, double-click Ole DB source, select the corresponding Connect,data Access mode in the pop-up window, select table or view, and then execute the source datasheet or views, OK.

Next, build a target data. Select OLE DB destination from the data flow destinations and drag to the data flow workspace.

In this case, I'm just going to import the data directly from one source table to the target table without making any conversions. So you can connect the output of the OLE DB Source component directly to the input of the OLE DB destination component. That is, the green arrow that pulls the source points to destination. The actual ETL process, but also to do a lot of data conversion and clean-up work, you can add various data flow transformations to achieve complex transformation logic.

After the data is connected to destination from source, double-click OLE DB destination to set the connection information, and if the target and source are not in the same database, you also need to create a new OLE DB connection in connect managers. Also select the data Access mode and the target table, if the target table does not exist, you can new one. In mappings, you can see that you have automatically mapped the columns for source and destination, or you can modify them manually.

After the data flow design, you also need to design the control flow. Because there is only one data stream in this example, there is only one part in the control flow, so one of the simplest SSIS package is developed.

2. Deployment

The developed SSIS Package also need to be deployed to the machine to be run. The deployment tools are built first.

In Project->ssis propertites, select Deployment Utility to set the Createdeploymentutility property to the bit true.

Then rebuild the project, which can be in the project directory bin (in the project properties can set the output of the directory, bin is the default directory), generated a deployment directory, copy the directory to the machine to be deployed, Then right click on the ssistest.ssisdeploymentmanifest, select Deploy, Start the Deployment Wizard, and follow the prompts to deploy package to SQL Server in one step.

3. Dispatch

Create a new job, select the type in step for SQL Server integration Services Package, and then perform the pacage that you want to dispatch.

4. Management

Use SQL Server Management Studio to connect to the integration service to manage the deployed package