图片 11

SSISDB1:使用SSISDB管理Package

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

本文转自:

使用工程部署模式(Project Deployment
Model),对管理SSIS工程的部署,排除ETL运行过程中出现的异常等问题,很有帮助。开发人员把SSIS工程部署到“Integration
Services
Catalog”中之后,集成服务引擎把Package的配置数据,ETL执行的情况等都记录在SSISDB数据库中。数据库SSISDB包含两个内置的框架(schema)中:internal和catalog。internal
框架下的数据表、视图等对象是集成服务引擎内部使用的,catalog框架下的数据表,视图等对象是提供给用户使用的。在数据库SSISDB中,用户可以使用TSQL脚本调用和执行package,通过视图查看package的执行情况,根据package的历史消息对package进行故障排除,管理package执行的参数或环境变量等。

 

为了保护 Integration
Services
包中的数据,可以设置保护级别,以帮助仅保护包中的敏感数据或包中的所有数据。 另外,可以采用密码或用户密钥对数据加密,或依靠数据库对数据进行加密。 另外,您对包所采用的保护级别不一定是静态的,而是在包的整个生命周期内可能变化。 通常,您可以在包开发阶段设置一个保护级别,在包部署阶段设置另一个保护级别。

一,基本元素

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

 注意

除了本主题中所述的保护级别外,您还可以使用固定数据库级角色保护保存到 Integration Services 服务器的包。

用户可以查看架构catalog下的视图,来监控SSISDB的运行情况。SSISDB数据库的基本构成是:Folder、Project、Package、Parameter和环境(Environment):

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

定义敏感信息)

  • [catalog].[folders]:用于查看在Integration Services
    Catalog->SSISDB目录下创建的文件夹(Folder);
  • [catalog].[projects] :用于查看存储在文件夹(Folder)下的SSIS
    工程(Project);
  • [catalog].[packages] :用于查看位于SSIS Project下的Package;
  • [catalog].[object_parameters] :用于查看Package或Project的参数,字段object_type=20表示Project参数;字段object_type=30,表示Package参数;
  • [catalog].[execution_parameter_values] :用于查看Package或Project在执行时的参数值;用户在执行package之前,可以修改Package参数的默认值,在Package执行时的参数的值是参数的执行值。
  • [catalog].[environments]、[catalog].[environment_variables]和[catalog].[environment_references]
    用于查看环境,环境中的变量,以及环境的引用,环境引用表示Project和Environment之间的映射关系;

 

 

在Package执行时,SSISDB会创建一个Operation,Execution,并统计每一个Package中的每一个可执行任务组件(Executable)的执行情况:

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

在 Integration Services
包中,下列信息定义为“敏感”信息:

  •  [catalog].[operations]
    :用于查看操作信息,常见的操作类型是:部署工程(deploy_project),执行包(create_execution
    and
    start_execution),停止包(stop_operation),维护窗口(Retention
    window);
  • [catalog].[executions]:用于查看Package的执行情况;
  • [catalog].[executables]:用于查看Package中的可执行任务组件(Executable),Executable
    是package的可执行组件,在控制流(Control Flow)选项卡中,是一个Task
    或者 Container;
  • [catalog].[executable_statistics] :用于查看可执行任务组件的执行情况;

 

  • 连接字符串的密码部分。 但是,如果选择加密所有数据的选项,则整个连接字符串都将被视为敏感信息。

  • 标记为敏感的任务生成的
    XML 节点。 XML 节点的标记由
    Integration Services 控制,用户无法更改。

  • 标记为敏感的所有变量。 标记的变量由
    Integration Services 控制。

二,参数

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

Integration Services
认为属性是否敏感,主要取决于 Integration Services
组件(连接管理器或任务)的开发人员是否将该属性指定为敏感。 用户不能向被视为敏感的属性列表添加属性,也不能从该列表删除属性。

SSISDB中的参数分为默认参数和执行参数,对象参数可以从视图[catalog].[object_parameters]
查看,该视图用于存储所有Package和Project的参数的信息。parameter_name表示参数名称,data_type表示参数的数据类型,必需属性(required)为1,表示在执行package时,该参数必须赋值,敏感属性(sensitive)为1,表示参数值是敏感的,SSIS会隐藏明文,显示为•••;value_set字段为1,表示参数被赋值,如果为0,表示参数未被赋值。

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

加密)

1,使用参数的对象

 

 

根据参数的作用域,可以把参数分为Package参数和Project参数,字段object_type=20表示Project级别的参数;字段object_type=30,表示Package级别的参数,

微软官网文档:

加密(如包保护级别所使用的加密)是通过使用
Microsoft 数据保护 API (DPAPI) 来执行的,DPAPI 是 Cryptography API
(CryptoAPI) 的一部分。

字段object_name是对象的名称,如果字段object_type=20表示Project名称;字段object_type=30,表示Package名称。

 

使用密码加密包的包保护级别还要求您提供密码。 如果将保护级别从不使用密码的级别更改为使用密码的级别,则系统将提示您输入密码。

还有一种特殊的对象,它的object_type为50,配置相应的参数,能够改变Package在执行时的特殊行为。

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

另外,对于使用密码的保护级别,Integration
Services 会使用 Triple DES 加密算法(其密钥长度为 192 位),.NET
Framework 类库 (FCL) 中提供该算法。

2,参数值

 

保护级别)

参数有三类值: design_default_value,default_value, 和
execution_value,设置时的默认值(design_default_value)是指在VS中设计Package时为参数指定的值;默认值(default_value)是指Package部署到SSIS中为参数设置的默认值;如果没有修改参数的默认值,那么默认值就是设计时的默认值。执行时(execution_value)是指Package在运行时为参数设置的值。由于在执行package时,可以选择设置参数的Value,或选择使用默认值,所以执行值是Package在某一次执行(Execution)时所使用的值。

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

 

3,参数值的类型

 

下表介绍 Integration
Services 提供的保护级别。 括号中的值是来自 DTSProtectionLevel 枚举的值。 在 SQL Server Data Tools
(SSDT) 中处理包时,这些值出现在用来配置包属性的“属性”窗口中。

字段 value_type 表示参数值的类型,如果value_type
为V,表示参数值就是default_value 或
execution_value字段的字面值,如果value_type
为R,表示参数的值是引用环境中的变量的值。

图片 1

 

字段referenced_variable_name 是引用的环境的名称。

 

保护级别

说明

不保存敏感数据 (DontSaveSensitive)

保存包时不保存包中敏感属性的值。 这种保护级别不进行加密,但它防止标记为敏感的属性随包一起保存,因此其他用户将无法使用这些敏感数据。 如果其他用户打开该包,敏感信息将被替换为空白,用户必须提供这些敏感信息。

当与 dtutil 实用工具 (dtutil.exe) 一起使用时,此保护级别对应的值为 0。

使用密码加密所有数据 (EncryptAllWithPassword)

使用密码加密整个包。 使用用户在创建包或导出包时提供的密码加密包。 若要在 SSIS 设计器中打开包或使用 dtexec 命令提示实用工具运行包,用户必须提供包密码。 如果没有密码,用户将无法访问或运行包。

当与 dtutil 实用工具一起使用时,此保护级别对应的值为 3。

使用用户密钥加密所有数据 (EncryptAllWithUserKey)

使用基于当前用户配置文件的密钥加密整个包。 只有创建包或导出包的用户才可以在 SSIS 设计器中打开包或使用 dtexec 命令提示实用工具运行包。

当与 dtutil 实用工具一起使用时,此保护级别对应的值为 4。

 注意

对于使用用户密钥的保护级别,Integration Services 使用 DPAPI 标准。 有关 DPAPI 的详细信息,请参阅位于 http://msdn.microsoft.com/library 的 MSDN Library。

使用密码加密敏感数据 (EncryptSensitiveWithPassword)

使用密码只加密包中敏感属性的值。 DPAPI 用于此加密。 敏感数据作为包的一部分保存,但数据是使用当前用户在创建包或导出包时提供的密码加密的。 若要在 SSIS 设计器中打开包,用户必须提供包密码。 如果不提供该密码,则包虽然可以打开但其中不包含敏感数据,当前用户必须为敏感数据提供新值。 如果用户试图在不提供密码的情况下执行包,则包执行将会失败。 有关密码和命令行执行的详细信息,请参阅dtexec 实用工具

当与 dtutil 实用工具一起使用时,此保护级别对应的值为 2。

使用用户密钥加密敏感数据 (EncryptSensitiveWithUserKey)

使用基于当前用户配置文件的密钥只加密包中敏感属性的值。 只有使用同一配置文件的同一个用户才能加载此包。 如果其他用户打开该包,敏感信息将被替换为空白,当前用户必须为敏感数据提供新值。 如果用户试图执行该包,则包执行将会失败。 DPAPI 用于此加密。

当与 dtutil 实用工具一起使用时,此保护级别对应的值为 1。

 注意

对于使用用户密钥的保护级别,Integration Services 使用 DPAPI 标准。 有关 DPAPI 的详细信息,请参阅位于 http://msdn.microsoft.com/library 的 MSDN Library。

依靠服务器存储进行加密 (ServerStorage)

使用 SQL Server 数据库角色保护整个包。 在将包保存到 SQL Server msdb 数据库后,支持此选项。 此外,SSISDB 目录使用 ServerStorage 保护级别。

在将包从 SQL Server Data Tools (SSDT) 保存到文件系统时,不支持此选项。

4,特殊参数的值

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

保护级别设置和
SSISDB 目录)

通过catalog.execution_parameter_values查看Package的执行参数,当object_type为50时,可以为package配置特殊的参数:

图片 2

 

  • LOGGING_LEVEL
  • DUMP_ON_ERROR
  • DUMP_ON_EVENT
  • DUMP_EVENT_CODE
  • CALLER_INFO
  • SYNCHRONIZED

 

SSISDB 目录使用 ServerStorage 保护级别。 在向 Integration Services
服务器部署 Integration Services
项目时,该目录会自动对包数据和敏感值加密。 该目录还会在检索数据时自动解密数据。

这些参数会影响Package运行的行为,例如 LOGGING_LEVEL会影响Package运行时,系统记录Package的日志级别。

 选择目标库

若将项目(.ispac 文件)从
Integration Services
服务器导出到文件系统,该系统会将保护级别自动更改为 EncryptSensitiveWithUserKey。 如果使用 SQL Server Data
Tools (SSDT) 中的“Integration
Services 导入项目向导”导入项目,“属性”窗口中的 ProtectionLevel 属性将显示值 EncryptSensitiveWithUserKey。

三,配置SSIS维护代理(Agent)的属性

图片 3

基于包的生命周期设置保护级别)

SSIS引擎使用Agent来维护SSISDB记录的数据,用户可以使用视图 [catalog].[catalog_properties]
查看代理的配置信息:

 

 

  • ENCRYPTION_ALGORITHM:加密算法的名称
  • MAX_PROJECT_VERSIONS:每个工程保持的最大版本数量
  • OPERATION_CLEANUP_ENABLED:周期性地删除旧版本
  • RETENTION_WINDOW:保持窗口
  • SCHEMA_BUILD:
  • SCHEMA_VERSION:
  • SERVER_CUSTOMIZED_LOGGING_LEVEL:
  • SERVER_LOGGING_LEVEL:服务器级别的日志级别
  • SERVER_OPERATION_ENCRYPTION_LEVEL:
  • VERSION_CLEANUP_ENABLED:启用版本清除功能

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

在 SQL Server Data Tools
(SSDT) 中初次开发 SQL Server Integration Services
包时,可以设置该包的保护级别。 以后当部署包时,在 SQL
Server Management Studio 中将包导入 Integration Services
或从中导出包时,或者在将包从 SQL Server Data Tools (SSDT) 复制到 SQL
Server、SSIS 包存储区或文件系统时,都可以更新包的保护级别。 例如,如果在计算机上使用某个用户密钥保护级别选项创建并保存包,则在将包提供给其他用户时,很可能需要更改保护级别,否则,他们将无法打开该包。

SSIS引擎会追踪对Package操作的历史记录和消息,包括Package的执行情况,工程的部署,以及执行的消息等,为了维护操作数据的size,SSIS创建了一个维护Job,叫做“SSIS
Server Maintenance Job”,这个Agent Job在Integration
Services安装时创建。SSIS通过该Job删除旧版本的Project,Package的操作记录和事件产生的消息。

图片 4

通常,您可以按下面列出的步骤更改保护级别:

四,部署版本的控制

 

  1. 在部署期间,将包的保护级别保留为默认值 EncryptSensitiveWithUserKey。 此设置可以保证只有开发人员可以看到包中的敏感值。 或者,您可以考虑使用 EncryptAllWithUserKey 或 DontSaveSensitive。

  2. 部署包时,您需要将保护级别更改为不依靠开发人员用户密钥的保护级别。 因此,通常需要选择 EncryptSensitiveWithPassword 或 EncryptAllWithPassword。 通过分配一个生产环境中运营团队也知道的临时强密码来加密包。

  3. 在将包部署到生产环境后,运营团队可以通过分配一个只有他们自己知道的强密码来重新加密部署的包。 他们也可以通过选择 EncryptSensitiveWithUserKey 或 EncryptAllWithUserKey,并使用要运行包的帐户的本地凭据来加密部署的包。

在每次每次工程部署SSIS时,集成服务引擎都会记录工程的版本,用户可以通过SSISDB查看部署的版本,并可以根据需要切换到老的版本。用户可以通过“版本历史”查看部署的版本,或者查询视图catalog.object_versions
查看部署的历史版本。

 批量设置图片 5或单表设置图片 6

相关任务)

1,通过Integration Services Catalogs来查看

 

 

打开SSISDB-》Projects,查看指定project的version
history,这种方式查看Deployed Time 比较慢

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

 

图片 7

图片 8

  • 设置或更改包的保护级别

2,当前版本

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

 

查询SSISDB的表projects,查看最后一次部署的时间

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

select
    f.name as foler_name, 
    --p.project_id,
    p.name as project_name,
    p.deployed_by_name,
    p.last_deployed_time
from catalog.projects p
inner join catalog.folders f
    on p.folder_id=f.folder_id

或者保存成文件图片 9 双击执行后可开启执行包的实用工具(前提是安装了)

3,查看部署的历史版本

图片 10

通过查看SSISDB的表catalog.object_versions,查看部署的历史版本

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

select 
    f.name as foler_name, 
    p.project_id,
    p.name as project_name,
    ov.created_by as deployed_by,
    ov.created_time as deployed_at
from catalog.object_versions ov 
inner join catalog.projects p 
    on ov.object_id=p.project_id
inner join catalog.folders f 
    on p.folder_id=f.folder_id
where ov.object_type=20  --object type: 20 project
    and p.project_id=8
order by deployed_at desc

 

 

继续导出

参考文档:

图片 11

SSIS
Catalog.aspx)