如何在 SSIS 包中参数化数据库连接字符串? [英] How to parameterize database connection string in an SSIS package?

问题描述

我有一个内置于 Business Intellegience Development Studio 的 SSIS 包，其中指定了源数据库和目标数据库.现在我想使用一些变量来参数化数据库连接并在命令行中运行包.

I have a SSIS package built in Business Intellegience Development Studio which have both source and destination database specified. Now I want to use some variables to parameterize the database connections and run the package in a command line.

我尝试用我的变量@[User::SourceDb] 替换数据库名称和 sql server 实例.@[User::SourceHost]，但是连接数据库失败.

I try to replace the database name and sql server instance with my variable @[User::SourceDb]. @[User::SourceHost], but it failed to connect to the database.

是否可以对数据库进行参数化，我的变量使用有什么问题吗?提前致谢！

Is that possible to paramterize the database and is there anything wrong with my variable useage? Thanks in advance!

推荐答案

有一个不错的帖子 此处详细说明了一种方法.

There is a nice post here that details one way of doing this.

您将需要使用 ConnectionManager 并从配置包中设置它的 ConnectionString 属性.

You will need to use a ConnectionManager and set the ConnectionString property of that from a Configuration Package.

ConnectionString 属性是一个完全限定的数据库连接字符串，如

The ConnectionString property is a fully qualified database connection string, like

Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

使用配置包有一些问题和烦恼，所以你可能需要摆弄一下.我会更详细一些，但距离我上次参与 SSIS 项目已经有大约一年的时间了.

There are a few gotchas and annoyances with using configuration packages so you may have to fiddle around a bit. I'd be more detailed but it has been about a year since I last worked on an SSIS project.

如果您有任何具体问题，请回来评论您遇到的问题.我会试着重燃旧的回忆.

If you have any specific problems, please come back and comment about what you've hit. I'll try and refire the old memories.

这篇关于如何在 SSIS 包中参数化数据库连接字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！