在SQL Server Integration Services中参数化数据库连接
SQL Server Integration Services (SSIS) has been growing into an enterprise solution over the last three releases. Variables were a step in that direction, but with Project deployments to the SSIS Catalog in SQL Server 2012 Microsoft has stepped it up, even more. Project parameters have become the go to solution for changing variable values in packages at run time. This article is going to dive into an example that parameterizes the Server Name and Initial Catalog (database name) in a connection string for packages in a project.
在过去的三个版本中，SQL Server集成服务（SSIS）已经发展成为企业解决方案。 变量是朝这个方向迈出的一步，但是随着将项目部署到SQL Server 2012中的SSIS目录中，Microsoft进一步加强了它。 项目参数已成为在运行时更改程序包中变量值的解决方案。 本文将深入探讨一个示例，该示例在项目中的程序包的连接字符串中参数化服务器名称和初始目录（数据库名称）。
The Connection String for a database can be created for an individual package or for a project in Visual Studio. These Connection Managers are available to either just the package or any package in a project. If the connection to the database needs to be used in multiple packages, use the Project Connection Manager.
可以为单个程序包或Visual Studio中的项目创建数据库的连接字符串。 这些连接管理器仅可用于程序包或项目中的任何程序包。 如果需要在多个程序包中使用到数据库的连接，请使用项目连接管理器。
Once a connection is created in the Project Connection Manager, it is available to the packages in the project. The connection properties are changed from the Package Connection Manager which can be confusing. Figure 2 shows the prefix (project) on the connection name in the package DimProduct. A connection (DW) created just in the package will not have this prefix.
在“项目连接管理器”中创建连接后，该连接可用于项目中的程序包。 连接属性是从Package Connection Manager中更改的，这可能会造成混淆。 图2显示了包DimProduct中连接名称的前缀（项目）。 仅在程序包中创建的连接（DW）将没有此前缀。
The properties available when selecting the connection in the Package Connection Manager can be changed to parameterize the connection string. The Expression property is going to be used to change the connection string. The entire string can be changed or just parts like Server Name and Initial Catalog (database name). Figure 3 shows right-clicking the (project) ADVDW14 database connection and selecting the Parameterize… submenu, which is a shortcut.
可以更改在“包连接管理器”中选择连接时可用的属性，以参数化连接字符串。 Expression属性将用于更改连接字符串。 整个字符串都可以更改，也可以更改诸如服务器名称和初始目录（数据库名称）之类的部分。 图3显示右键单击（项目）ADVDW14数据库连接并选择Parameterize…子菜单，这是一个快捷方式。
But before we use shortcuts, it is better to learn the areas used by SSIS to complete this parameterization. The first new area is Project Parameters in the Solution Explorer of the project. Project Parameters are global to all Packages in a single SSIS Project. So, if you use the parameter in more than one package in a project, and the value is changed before executing, then all packages will see and use the new value.
但是在使用快捷方式之前，最好先学习SSIS用来完成此参数化的区域。 第一个新区域是项目的解决方案资源管理器中的项目参数。 项目参数是单个SSIS项目中所有程序包的全局参数。 因此，如果在一个项目中的多个包中使用了该参数，并且在执行之前更改了该值，则所有包都将看到并使用新值。
There will be 2 new parameters created: ServerName and DatabaseName. The ServerName and DatabaseName project parameters will enable us to deploy this package to a different SSIS instance (QA or Production) and change the parameters when executing. The values used when developing and testing this package will be different than when deployed to production.
将创建两个新参数：ServerName和DatabaseName。 ServerName和DatabaseName项目参数将使我们能够将此程序包部署到其他SSIS实例（QA或Production），并在执行时更改参数。 开发和测试此程序包时使用的值将不同于部署到生产中时使用的值。
To create Project Parameters, just double-click the Project.param file in the solution explorer. There will be a toolbar button available to create new parameters.
要创建项目参数，只需在解决方案资源管理器中双击Project.param文件。 将有一个工具栏按钮可用于创建新参数。
Place the name of the parameter in the name column. The Data Type in this case is string. Value is used for initial value. Column Required is used to force a value in the parameter before executing. An error is returned when the Required parameter has no value.
将参数的名称放在名称列中。 在这种情况下，数据类型是字符串。 值用作初始值。 必填列用于在执行之前强制参数中的值。 当Required参数没有值时，将返回错误。
To change the connection string to use the project parameters, select the connection in the package Connection Managers, and go to the properties window. If the properties window is not already visible, right-click the connection and select properties. It is convenient to have the properties docked to the right and under the solution explorer.
要将连接字符串更改为使用项目参数，请在包“连接管理器”中选择连接，然后转到属性窗口。 如果属性窗口尚不可见，请右键单击连接并选择属性。 将属性停靠在解决方案资源管理器的右侧和下方很方便。
Click the ellipse to the right of the Expressions property and the Property Expression Editor window will pop up.
单击“表达式”属性右侧的椭圆，将弹出“属性表达式编辑器”窗口。
One of the available properties is ConnectionString, but this makes you edit the whole string rather than just parts of the string. The ServerName property for the new Project Parameter is obvious, but the DatabaseName is not unless you are familiar with SQL Server database connection strings. The property used for changing the database is InitialCatalog. This property can be traced back to the original OBDC driver days, which by the way are making a return.
可用属性之一是ConnectionString，但这使您可以编辑整个字符串，而不仅仅是部分字符串。 新的Project Parameter的ServerName属性是显而易见的，但是除非您熟悉SQL Server数据库连接字符串，否则DatabaseName不会。 用于更改数据库的属性是InitialCatalog。 该属性可以追溯到原始的OBDC驱动程序日期，顺便说一句，该日期已返回。
When InitialCatalog is selected, the next step is to associate the Project Parameter DatabaseName with this Expression property. This is done by clicking the ellipse under the Expression column of the Property.
选择InitialCatalog后，下一步是将项目参数DatabaseName与此表达式属性相关联。 这是通过单击“属性”的“表达式”列下的椭圆来完成的。
Figure 11 shows the Expression Builder window. This window enables the dragging and dropping of the Project Parameters to the Expression text box. Once dragged and dropped, the Project Parameter will have a @ symbol prefixed on the parameter name. There is an Evaluate Expression button available to show the results of the expression. The expression can contain other variables, expressions or hard-coded text as part of the value. The button is useful to make sure the expression created is correct and valid.
图11显示了Expression Builder窗口。 通过此窗口，可以将项目参数拖放到“表达式”文本框中。 拖放后，项目参数将在参数名称前带有@符号。 有一个“评估表达式”按钮可用于显示表达式的结果。 表达式可以包含其他变量，表达式或硬编码文本作为值的一部分。 该按钮可用于确保创建的表达式正确和有效。
Repeat these steps for the ServerName property. The Project and Packages are ready to be deployed.
对ServerName属性重复这些步骤。 项目和包已准备好进行部署。
The project can be deployed to the SSIS instance by right-clicking the project and selecting deploy from the submenu. The wizard will prompt for an SSIS instance to deploy the project. There will be a different selection area for the folder in the SSIS Catalog.
右键单击该项目，然后从子菜单中选择“部署”，可以将该项目部署到SSIS实例。 该向导将提示您提供一个SSIS实例来部署项目。 SSIS目录中的文件夹将有一个不同的选择区域。
Figure 14 shows the package configured in SQL Server Agent as step in a job. The configuration tab of this Job Step can be used to change the values of a Project Parameter.
图14显示了在作业中作为步骤在SQL Server代理中配置的软件包。 该作业步骤的配置选项卡可用于更改项目参数的值。
Figure 15 shows the areas to change these values for the Project Parameters. The first is the Parameter itself. The second is an Environment that is associated with the Project. If the value needs to change for multiple packages in the project, use the Environment. Environments help with managing the change for multiple packages in a deployed project.
图15显示了更改项目参数的这些值的区域。 第一个是参数本身。 第二个是与项目关联的环境。 如果需要为项目中的多个软件包更改值，请使用环境。 环境有助于管理已部署项目中多个程序包的更改。
It may seem that there are a lot of steps to accomplish the desired result. But once this is setup, the configuration can be changed in the Environment of the project once deployed to the SSIS Catalog. More about the SSIS Catalog of Environments will be covered in another article.
似乎有很多步骤可以实现所需的结果。 但是一旦设置完成，一旦部署到SSIS目录中，就可以在项目的环境中更改配置。 关于SSIS环境目录的更多信息将在另一篇文章中介绍。
Side Bar:
侧栏：
The SSIS Catalog can be created in a new installation of SQL Server 2012, 2014 or 2016. In SQL Server Management Studio (SSMS), the object explorer will show a Folder called Integration Services Catalog. If you Right-Click on this folder, a Create Catalog… submenu will be available to create the SSIS database on this instance. Figure 16 shows the menu choice disabled because the Catalog has already been created.
可以在新安装SQL Server 2012、2014或2016中创建SSIS目录。在SQL Server Management Studio（SSMS）中，对象资源管理器将显示一个名为Integration Services Catalog的文件夹。 如果右键单击该文件夹，将可以使用“创建目录...”子菜单在此实例上创建SSIS数据库。 图16显示了由于已创建目录而禁用的菜单选项。
参考资料 (References)
- Create the SSIS Catalog 创建SSIS目录
- Project and Package Parameters 项目和包装参数
- Project Connection Managers 项目连接经理
翻译自: https://www.sqlshack.com/parameterizing-database-connection-sql-server-integration-services/