SSIS 变量与参数 (SSIS Denali) [英] SSIS Variables vs Parameters (SSIS Denali)
问题描述
- SSIS Denali 中的变量和参数有什么区别?
- 如果有任何区别，那么变量不能做什么而参数可以做什么?反之亦然.
- 什么时候应该使用 SSIS 参数和变量?
我尝试在 Google 上搜索，但未能获得一些信息.
I tried searching on Google, but I failed to get some information.
非常感谢！
推荐答案
我认为了解一些背景知识将有助于理解 Parameter 概念.这里我将在与 Variables 进行比较的上下文中进行解释.要完全掌握参数概念，您可能还需要查找新的项目部署模型、环境、构建配置..
I think a little bit background will be beneficial to understand the Parameter concept. Here I will explain it in the context of comparing with Variables. To fully grasp the Parameter concept, you might need to look up for the new Project Deployment Model, Environment, Build Configuration as well..
在 2012 年之前的 SSIS 中，如果我们需要在执行之前将任何外部值传递给包(正如我们一直在做的那样)，我通常使用配置文件(或其他几种方式).假设我们有一个文件服务器，它将用于访问共享文件，我将使用变量来存储服务器名称，并将此变量公开给配置文件.如果实际文件服务器发生变化(dev env 到 test env 等)，我们只需要更改配置文件中该变量的值，SSIS 包保持不变.
With SSIS prior 2012, if we need to pass any external values to the package before the execution (as we all do all the time), I normally use configuration file (or a couple of other ways). Say we have a file server, which will be used to access a shared file, I will use variable to store the server name, and expose this variable to the configuration file. If the actual file server is changed (dev env to test env etc.), we just need the change the value of that variable in the configuration file and SSIS package remains intact.
一切看起来都不错，但有几件事我总是问自己为什么，但不知道为什么:
Everything looked good, but there are a couple of things that I always ask myself why and could not figure out why:
100% 当我将变量公开给配置文件时，我只公开值"属性.为什么 SSIS 允许公开所有其他变量属性?
100% of the time when I am exposing variables to configuration file, I just expose the "Value" properties. Why does SSIS allow to expose all the other variable properties?
为什么 SSIS 没有私有"变量?私有"是指当我选择要配置的变量时，私有"变量没有显示在选择列表中.SSIS 包可能有几十个变量，对于内部值持有者来说，公开它们有什么意义?为什么我必须一直滚动才能找到唯一需要公开的内容?
Why does SSIS not have "private" variable? By "private" I mean when I chose the variables to configure, the "private" ones just did not get shown on the pick list. The SSIS package could have dozens of variables, for the internal value-holders, what's the point to expose them? Why I have to scroll all the way to find the only one I need to expose?
新的项目部署模型
SSIS 2012 引入了一种新的部署模型，即项目部署模型.简而言之，此模型将 SSIS 项目作为单个单元部署到 SQL Server SSIS 目录中，并且此模型中不提供包配置(它在称为包部署模型的旧模型中可用，带有 SSIS2012你可以选择使用哪一个，2012默认为新型号).
New Project Deployment Model
SSIS 2012 introduces a new deployment model, Project Deployment Model. For short, this model deploys SSIS project as a single unit to SQL Server SSIS catalog, and package configuration is NOT available in this model (it is available in the old model referenced as Package Deployment Model, with SSIS 2012 you can choose which one to use, 2012 default to the new model).
如果我们希望将一些值传递到 SSIS 包中，我们必须通过参数将它们传递进来，并使用 SSMS 中的 SSIS 目录来配置参数的值(仅值，我们无法配置其他任何东西).参数和连接管理器在可以配置的 SSIS 目录中自动公开，以前通过配置文件可用的任何其他内容都不能在项目部署模型中配置(世界更干净).在 SSIS 包中，在构建表达式方面，可以像使用变量一样使用参数.但是，不能在 SSIS 包内修改参数，这是完全有道理的.(为什么我们需要更改从外部传入的值?如果必须，请将值传递给变量，然后在那里进行更改..)
If we want the pass some values into the SSIS packages, we have to pass them in via Parameters, and use SSIS catalog in SSMS to configure the value for the parameters(only the value, nothing else we can configure). Parameters and connection managers are exposed automatically in SSIS catalog which can be configured, nothing else previously available via configuration files can be configured in Project Deployment Model (The world is much cleaner). Inside SSIS package, parameters can be used in the same way as variables in terms of building up expressions. However, parameters can NOT be modified within the SSIS package, which makes perfect sense. (Why do we need to change a value which is passed in from external? If we have to, pass the value to an variable, and do the changes there..)
参数仅在项目部署模型中可用，并且它提供了将值从外部传递到此模型中的 SSIS 包的唯一机制.如果我们认为 SSIS pacakge 是一个 OO 类，那么参数可以被认为是公共属性，外部可以访问它并为其赋值(类本身可以/将使用它，但不能修改它).变量可以被认为是内部使用的私有变量，外部世界不需要了解它.
Parameter is only available in the Project Deployment Model, and it provides the only mechanism for passing values from external to SSIS packages in this model. If we think SSIS pacakge as an OO class, Parameters could be thought as public properties, which externals can access and assign value to it (the class itself can/will use it, but cannot modify it). Where Variables could be thought as private variables, which are used internally, external world does not need to know anything about it.
对于旧的包部署模型，没有参数，世界保持不变.
For the old Package Deployment model, there is no Parameter, and the world remains the same.
这篇关于SSIS 变量与参数 (SSIS Denali)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
