access_time 10 years ago language中文
more_vert

[Business Intelligence]使用SQL Server Integration Services(SSIS)

visibility 1,330 comment 0

image

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

在源数据库中插入数据:

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

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

image

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

image

创建FTP网站:

image

image

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

image

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

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

  • 创建项目

image

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

image

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

image

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

image

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

image

image

同样的方式添加Target:

image

映射关系如下:

image

最终Data Flow 如下

image

  • 在Control Flow中添加Ftp Task

设置其Connetion为:

image

创建变量,用于存储本地文件夹地址:

image

修改其属性:

image

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

最终Control Flow 如下:

image

在BIDS中运行

按F5开始运行:

image

上图表示运行结果成功。

在Execution Results 中可以查看结果:

image

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

image

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

image

部署到SQL Server

通过SSMS创建SSIS的Catalog:

image

(需要输入加密需要用到的密码)

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

image

image

image

image

部署成功!

在SSMS中管理运行Package

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

image

image

image

在SSMS中运行Package:

image

点击Ok开始运行:

image

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

image

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

image

Steps中添加SSIS的Package:

image

添加Schedule:

image

可以查看Job的状态:

image

更多说明

如果我们在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

结语

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

info Last modified by Raymond 7 years ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 1029
thumb_up 0
access_time 9 years ago
Create Business Reports using SSRS
visibility 1016
thumb_up 0
access_time 8 years ago
visibility 405
thumb_up 0
access_time 8 years ago