本篇介绍如何使用SSIS和作业完成自动更新目标数据任务。 

 

** 温馨提示:如需转载本文,请注明内容出处。**

本文链接:https://www.cnblogs.com/grom/p/9018978.html 

 

  笔者需要定期从服务器更新N家客户的远程服务器数据,上一篇的存储过程是其中一个更新方法,后来随着数据量逐渐增大,受网络环境的影响,批量远程插入消耗的时间已经无法承受,后来在导出数据时发现了保存SSIS选项,然后展开了测试开发,现将成果分享出来供大家参考,类似的资料不少,所以就简单写写操作步骤。

 

首先,尽量可能使用部署到环境的Windows账号,比如部署到服务器,就远程到服务器后使用Windows账号登录,可以避免很多权限上的麻烦。

另外,注意客户端的版本,SSMS一定不能低于SQL服务版本,不然会出异常。

 

微软官网文档https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/bb522535(v%3dsql.105)

 

导出导入数据,SSIS包部署在源库还是目标库均可,下面以导出数据为例。

 

右键导出数据的库 任务 导出数据

 

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

选择导出数据库(数据源库)

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

 选择目标库

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

编辑要传输的表,有不同需求(如需要删除目标表内原数据实现全覆盖等)时,可在编辑映射里设置。

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

 批量设置Sql Server 部署SSIS包完成远程数据传输-DESTLIVE或单表设置Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

最后保存SSIS包,为了保证可移植性,笔者这里将保护级别设置成了密码保护

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

SSIS包保存方式有两种,一种是保存在SQL Server内部,另一种是保存成.dtsx的文件

保存在SQL Server里可以在系统数据库msdb里的sysssispackages表内看到

或者保存成文件Sql Server 部署SSIS包完成远程数据传输-DESTLIVE 双击执行后可开启执行包的实用工具(前提是安装了)

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

跟着向导走就可以执行包了,无论是包文件还是Sql Server内的均可使用此工具执行。

 

继续导出

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

点击完成后完成包的保存。

 

SSIS可视化界面

管理SSIS微软提供了两种可视化管理工具,SQL Server Data Tools (SSDT)和大家用的 SQL Server Management Studio(SSMS)

官方文档:https://docs.microsoft.com/zh-cn/sql/integration-services/integration-services-ssis-development-and-management-tools?view=sql-server-2017

这里只以SSMS界面举例

点击连接,选择 Integration Services(这里只能使用Windows身份连接,所以)

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

连接后可进行管理配置

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

 

 

 

为了可维护性,楼主在这里新建了项目,如无此需求的可以跳过本步骤

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

在Integration Services目录上右键创建目录

创建此目录时会自动在数据库内创建同名库,记录相关信息,红色圈内为数据库名称

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

密码必须设置,并且要符合安全策略,默认的好像是字母+数字+特殊符号

 * 可能会出现以下错误

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

解决方案:https://blog.csdn.net/yy13210520/article/details/6438457 下面附张解决方案截图

  Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

创建完成如图

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

创建文件夹

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

好了,一个解决方案建好了

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

下面是导入包

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

保存为文件选第一个,保存在SQL Server选第三个,第二个笔者没用过,推测是装了SSDT后创建的,有经验的大神希望可以留下教程

 

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

找不到包直接选择SSIS包的根目录文件夹(如图所选)即可

 

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

 

把项目里需要的包都放进去,记得吧密码输进去

 

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

选择项目的保存路径

 

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

完成后会自动弹出项目部署向导,没有的话可以右击项目选择部署项目

 

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

部署完成

 

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

 

设置作业自动执行包

新建作业,没有SQL Server代理的可在SQL配置中开启

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

新建步骤 类型选择 SQL Server Integration Services 包

包源:保存文件的选择文件系统,没有建项目只保存在SQL Server上的选SQL Server,按上面步骤建立项目的选SSIS目录

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

选择每个包建立每个步骤,再设置好计划

Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

大功告成

 Sql Server 部署SSIS包完成远程数据传输-DESTLIVE

 

 

关于SSISDB数据库的表,笔者从网上查了下表的作用:

  • [internal].[executables] -- 记录每个版本的包里面的可执行任务
  • [internal].[operations] -- 该表记录了SSIS包的操作记录
  • [internal].[operation_permissions] --记录的每个操作记录的权限
  • [internal].[operation_os_sys_info] --记录每个操作的系统资源使用
  • [internal].[operation_messages] --记录每个操作的运行时状态,具体到包里面的各个任务的执行情况
  • [internal].[executions] --记录每个SSIS包的执行信息
  • [internal].[execution_parameter_values] --记录执行时各个参数的值
  • [internal].[executable_statistics] -- 记录每个包里面可执行任务的执行状态
  • [internal].[event_messages] -- 记录每个可执行任务的消息代码
  • [internal].[event_message_context] -- 记录每个可执行消息的上下文属性
  • [internal].[catalog_encryption_keys] -- 记录创建SSISDB时使用的密码和加密的Key值。