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.
我有一个在Business Intellegience Development Studio中内置的SSIS包，它同时指定了源数据库和目标数据库。现在我想使用一些变量来参数化数据库连接并在命令行中运行包。
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.
我尝试用我的变量@ [User :: SourceDb]替换数据库名称和sql server实例。 @ [User :: SourceHost]，但无法连接到数据库。
Is that possible to paramterize the database and is there anything wrong with my variable useage? Thanks in advance!
是否可以对数据库进行参数化，并且我的变量使用有什么问题？提前致谢！
3 个解决方案
#1
5
There is a nice post here that details one way of doing this.
这里有一篇很好的帖子详细说明了这样做的一种方法。
You will need to use a ConnectionManager and set the ConnectionString property of that from a Configuration Package.
您将需要使用ConnectionManager并从配置包中设置它的ConnectionString属性。
The ConnectionString property is a fully qualified database connection string, like
ConnectionString属性是完全限定的数据库连接字符串，如
Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
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.
使用配置包有一些烦恼和烦恼，所以你可能不得不摆弄一下。我会更详细，但自从我上次参与SSIS项目以来已经有一年了。
If you have any specific problems, please come back and comment about what you've hit. I'll try and refire the old memories.
如果您有任何具体问题，请回来评论您遇到的问题。我会试着改变旧的记忆。
#2
3
You need to define "expressions" in the connection manager which then substitute into server/database.
您需要在连接管理器中定义“表达式”，然后将其替换为服务器/数据库。
You can't use variables directly quite often in SSIS
您不能在SSIS中直接使用变量
See "Using Variables in Packages" whcih links to Using Property Expressions in Packages.
请参阅“在包中使用变量”链接到在包中使用属性表达式。
And after a quick Bingle: http://sqlrs.blogspot.com/2006/03/using-expression-variables-in-ssis.html
快速的Bingle之后：http：//sqlrs.blogspot.com/2006/03/using-expression-variables-in-ssis.html
#3
0
You can right-click on any connection on the project or package and "Parameterize" all the fields from connection, in project or in package parameters. Connection string differs for OLE DB connection to .NET Providers(SqlClient Data Provider) type, so be careful. We have manage to have one centralized place for connection in Project.params
您可以右键单击项目或包上的任何连接，并“参数化”连接，项目或包参数中的所有字段。对于.NET提供程序（SqlClient数据提供程序）类型的OLE DB连接，连接字符串不同，因此请小心。我们已经设法在Project.params中有一个集中的连接位置
#1
5
There is a nice post here that details one way of doing this.
这里有一篇很好的帖子详细说明了这样做的一种方法。
You will need to use a ConnectionManager and set the ConnectionString property of that from a Configuration Package.
您将需要使用ConnectionManager并从配置包中设置它的ConnectionString属性。
The ConnectionString property is a fully qualified database connection string, like
ConnectionString属性是完全限定的数据库连接字符串，如
Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
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.
使用配置包有一些烦恼和烦恼，所以你可能不得不摆弄一下。我会更详细，但自从我上次参与SSIS项目以来已经有一年了。
If you have any specific problems, please come back and comment about what you've hit. I'll try and refire the old memories.
如果您有任何具体问题，请回来评论您遇到的问题。我会试着改变旧的记忆。
#2
3
You need to define "expressions" in the connection manager which then substitute into server/database.
您需要在连接管理器中定义“表达式”，然后将其替换为服务器/数据库。
You can't use variables directly quite often in SSIS
您不能在SSIS中直接使用变量
See "Using Variables in Packages" whcih links to Using Property Expressions in Packages.
请参阅“在包中使用变量”链接到在包中使用属性表达式。
And after a quick Bingle: http://sqlrs.blogspot.com/2006/03/using-expression-variables-in-ssis.html
快速的Bingle之后：http：//sqlrs.blogspot.com/2006/03/using-expression-variables-in-ssis.html
#3
0
You can right-click on any connection on the project or package and "Parameterize" all the fields from connection, in project or in package parameters. Connection string differs for OLE DB connection to .NET Providers(SqlClient Data Provider) type, so be careful. We have manage to have one centralized place for connection in Project.params
您可以右键单击项目或包上的任何连接，并“参数化”连接，项目或包参数中的所有字段。对于.NET提供程序（SqlClient数据提供程序）类型的OLE DB连接，连接字符串不同，因此请小心。我们已经设法在Project.params中有一个集中的连接位置