Kontext Labs Kontext Labs
Console

使用SQL Server Integration Services(SSIS)

2011-11-05 sql-server ssis

SQL Server中的SSIS作为BI的一部分，可以帮助我们将数据从一个数据源导出到另外一个地方，同时在这个过程中还可以对数据进行转换修改等；如果导入导出遇到错误，可以通过处理Error或者事件的进行相应处理；SSIS控制流程任务包括了数据流任务，还有发送Email,FTP等等常用的任务；我们可以通过事务或者Breakpoints来控制整个的流程的执行；为了保护敏感的数据，我们可以对SSIS的Package进行部分或者全部加密；为了更好的部署，SSIS在BIDS中提供可配置的功能（如XML配置，环境变量配置，Package继承配置，SQL Server配置，注册表配置等）；SSIS的Package可以部署到服务器上的文件系统中或者SQL Server 中，需要服务器运行SSIS的服务，如果使用了事务，需要MSDTS服务也同时运行；部署后我们可以通过数据库角色或者文件系统权限等对权限进行相应的控制；执行SSIS Package 可以通过命令行工具或者相应UI工具，还可以通过SSMS进行执行管理；我们还可以通过SQL Server Agent创建Job定期的执行SSIS的Package。

本文不会对SSIS的每个细节进行阐述，而是通过一个例子来运用SSIS；本例将运用SSIS周期性的同步远程的SQL Server数据库到本地的数据库，同时通过FTP对某一文件夹进行数据的同步，最终将任务部署在SQL Server中并且每天运行。

准备工作

1.创建源数据库MySSISSourceDb以及本地数据库MySSISLocalDb（本例均创建在我的PC上）；同时初始化数据表Notes，SQL 语句如下：

create table Notes   
(    
NoteID int not null identity primary key,    
NoteTile varchar(50) not null,    
NoteAddDate datetime not null default getdate()    
)

image_thumb_1.png

在源数据库中插入数据:

insert into dbo.Notes(NoteTile) values('Test Note 1')   
insert into dbo.Notes(NoteTile) values('Test Note 2')    
insert into dbo.Notes(NoteTile) values('Test Note 3')    
insert into dbo.Notes(NoteTile) values('Test Note 4')

image_thumb_2.png

2.创建FTP网站MySSISWebSite，如果未安装FTP支持，请开启FTP的功能：

image_thumb_3.png

同时创建相应文件夹，如下图，FTP是网站文件夹，Local是本地需要同步的文件夹：

image_thumb_4.png

创建FTP网站:

image_thumb_5.png

image_thumb_6.png

在FTP文件下添加几个测试文件：

image_thumb_7.png

至此，我们的准备工作就完成了。

在BIDS(Business Intelligence Development Studio )中创建SSIS项目MySSISProject

  • 创建项目

image_thumb_8.png

  • 在默认的Package.dtsx中创建到数据库的链接

image_thumb_9.png

按照同样的方式创建对MySSISLocalDb的链接：

image_thumb_10.png

  • 在Control Flow中添加一个Sequence Container
  • 在Sequence Container中添加一个Data Flow Task

image_thumb_11.png

  • 双击此Data Flow Task进入编辑
  • 添加Soure和Target:

image_thumb_12.png

image_thumb_13.png

同样的方式添加Target：

image_thumb_14.png

映射关系如下：

image_thumb_18.png

最终Data Flow 如下

image_thumb_19.png

  • 在Control Flow中添加Ftp Task

设置其Connetion为：

image_thumb_20.png

创建变量，用于存储本地文件夹地址：

image_thumb_21.png

修改其属性:

image_thumb_24.png

此任务同步A.txt这个文件。

最终Control Flow 如下：

image_thumb_25.png

在BIDS中运行

按F5开始运行:

image_thumb_26.png

上图表示运行结果成功。

在Execution Results 中可以查看结果:

image_thumb_27.png

查询MySSISLocalDb可以获得相同的结果，代表数据已经成功的同步：

image_thumb_28.png

而Local文件夹也已经包含了A.txt文件:

image_thumb_29.png

部署到SQL Server

通过SSMS创建SSIS的Catalog：

image_thumb_32.png

（需要输入加密需要用到的密码）

在解决方案管理中右击项目，选择Deploy开始部署向导 ：

image_thumb_31.png

image_thumb_33.png

image_thumb_34.png

image_thumb_35.png

部署成功！

在SSMS中管理运行Package

用SSMS查看/管理/运行部署的Package：

image_thumb_38.png

image_thumb_37.png

image_thumb_36.png

在SSMS中运行Package：

image_thumb_40.png

点击Ok开始运行：

image_thumb_41.png

点击Yes可以查看运行的结果报表：

image_thumb_42.png

在SQL Server Agent中创建Job周期性的运行Package

image_thumb_43.png

Steps中添加SSIS的Package：

image_thumb_44.png

添加Schedule：

image_thumb_45.png

可以查看Job的状态：

image_thumb_46.png

更多说明

如果我们在Data Flow Task Error处理选择的Fail Component 那么在第二次运行的时候就会出现问题，因为自增长主键NoteID的缘故，比如如下：

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK__Notes__EACE357F27098140'. Cannot insert duplicate key in object 'dbo.Notes'. The duplicate key value is (1).".

在这种情况下我们可以运用Conditional Split筛选没有同步的数据，同时对同步的数据进行更新处理即可；或者通过Lookup来实现：

image_thumb_47.png

结语

SSIS的功能很强大，本例仅给出基本的示例，更多的操作和流程大家可以继续去实践。