SSIS如何从配置文件动态设置连接字符串 [英] SSIS how to set connection string dynamically from a config file
问题描述
SSIS如何从配置文件中动态设置连接字符串
SSIS how to set connection string dynamically from a config file
推荐答案
在变量选项卡中创建变量，我的是ProjectServer（String），DBName（String）
创建直接连接到实际服务器和数据库的数据库连接对象...测试连接
右键单击连接对象并选择属性
在属性窗口中单击带有3个点的灰色框选择表达式...
从第1列的下拉列表中选择ConnectionString，然后单击带有3个点的灰色框。
在打开e的表达式编辑器中以下内容：Data Source =+ @ [User :: ProjectServer] +; Initial Catalog = FinancialProcessing; Integrated Security = True;
Create your variables in the Variable tab, mine are ProjectServer (String), DBName (String)
Create your DB Connection Object connecting directly to the actual Server and Database...Test your Connection
Right Click on the Connection Object and Select Properties
In the Properties Window Select Expressions by clicking the grey box with 3 dots ...
Select ConnectionString from the dropdown in the 1st column, then click the grey box with 3 dots.
In the Expression Editor that opens enter the following: "Data Source=" + @[User::ProjectServer] + ";Initial Catalog=FinancialProcessing;Integrated Security=True;"
请参考以下链接：
动态连接字符串SSIS [ ^ ]
动态使用SSIS ForEach循环容器的数据库连接 [ ^ ]
希望有帮助。
--Amit
Refer the links below:
Dynamic Connection Strings in SSIS[^]
Dynamic Database Connection using SSIS ForEach Loop Container [^]
Hope it helps.
--Amit
我的解决方案是：
插入[脚本任务]，在脚本任务编辑器中你可以得到来自Dts的连接经理如下：
ConnectionManager cm = Dts.Connections [YOUR_CONNECTION_NAME]作为ConnectionManager;
然后您可以更改连接字符串动态如下：
cm.ConnectionString =Provider = XXX;数据来源= XXX;用户ID = XXX;密码= XXX;初始目录= XXX;
希望对您有用。
PS：
当我尝试使用表达式编辑器时，它总是显示错误，有人可以描述怎么做吗？
My solution is:
insert a [Script Task], and in script task editor you can get connection manager from Dts as follow:
ConnectionManager cm = Dts.Connections["YOUR_CONNECTION_NAME"] as ConnectionManager;
then you can change connection string dynamic as follow:
cm.ConnectionString="Provider=XXX;Data Source=XXX;User ID=XXX;Password=XXX;Initial Catalog=XXX";
Hope useful for you.
PS:
When I try Expression Editor, it always show me error, Does somebody can describe how to do?
这篇关于SSIS如何从配置文件动态设置连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
