ssis xml 配置 - 配置覆盖包 - ssis 是否会在没有您告知的情况下更改配置? [英] ssis xml configurations - configs overwrite packages - does ssis change config without you telling it to?

问题描述

昨天我工作了一整天，试图消除我的 XML 配置文件中不正确的连接字符串的影响.我浏览了每个包，删除了有问题的连接管理器并使用正确的服务器名称重新添加它.每次我这样做时，我都会通过删除它来明确更新配置，然后使用覆盖选项重新添加它.但后来我犯了一个可怕的错误，拉起父包，它使用了不同的 xml 配置，它再次将连接管理器更新为错误的服务器名称.

我将配置从父包中取出并再次开始修复子包.修复了子包后，我从它现在的 xml config free 父包中调用了其中一个.看到它再次在连接中连接了错误的服务器，我感到非常震惊和失望.

怎么会这样?

我还通过 wordpad++ 注意到配置文件已更新.我的假设是，当父配置更新连接管理器时，SSIS自动更新现在更正的配置文件并再次破坏它.

当 SSIS 的组件发生变化时，它真的会自动更新配置文件吗?真的是双向的吗?如果是这样，那完全是令人困惑的行为——就像打乒乓球，或者把手指放在堤坝上.我准备建议我们永远不要使用 XML 配置！！

解决方案

更新: 我错了.请参阅下面的评论.

所以我认为您对 SSIS 中的配置如何工作有一个(可能是两个)根本性的误解.

使连接管理器可配置的标准流程是:

1. 创建连接管理器，将其指向您在 BIDS 中的本地/开发连接.
2. 为连接管理器的 ConnectionString、ServerName 和 DatabaseName 属性创建一个配置.(如果是文件，添加filePath等)
3. 在后续包中，只需创建相同的连接管理器，添加配置，将其指向您现有的配置文件，然后选择重用现有..."(不是覆盖"，这会抛出完成您在第 2 步中所做的所有工作.)
4. 将包部署到服务器并将 XML 文件部署到服务器上的同一本地位置.
5. 修改服务器上的 XML 文件以指向该服务器的正确数据库连接(开发、登台、生产等)
6. 当您在该服务器上运行程序包时，它将在运行时使用该 XML 文件，并在运行时使用服务器各自的数据库.
7. 完全可选:我们实际上为每个数据源使用一个 XML 文件并显式命名连接管理器.这意味着我们可以通过连接管理器的命名约定保证"所有需要使用某个数据库的包都访问完全相同的数据库.我们将这些数据源存储在项目级别，只需从数据源创建新连接，然后添加具有匹配名称的配置重用现有"，点击确定"，一切就绪.

因此，您的一个误解是删除连接管理器.配置文件不会更新"连接管理器.连接管理器的属性在设计时设置，并且是您在 BIDS 中双击它时看到的任何内容.这些被硬编码到包中(查看代码并亲自查看)并且只能在连接管理器编辑器本身中进行更改.所以没有有问题"的连接管理器这样的东西，删除它也没有意义——只要你在编辑器中测试连接时成功"，那个连接管理器就可以了.

您的第二个误解是配置文件的工作原理.配置文件仅在运行时用自己的值替换包执行中的属性.它根本不修改包.相反，SSIS 包本身永远不会修改配置文件.这只能使用 BIDS 流程之外的文本编辑器或通过 BIDS 配置编辑器来完成 - 我相信这是发生的事情.

我无法根据您提供的一般时间表准确判断，但是您使用覆盖选项表明您基本上允许最后发生的任何配置编辑赢得"设置所有值的特权文件将用于该特定连接.

无论如何，我会(正如您可能猜到的)完全推荐使用 XML 配置，因为它们(或者曾经，我认为！)非常简单，并且在我看来是多层 SSIS 环境的最简单的部署选项.

i worked all day yesterday trying to back out the impacts of an incorrect connection string in my XML config file. i went through each package, deleting the faulty connection manager and re-adding it with the correct server name. each time i did this i explicitly updated the configuration by removing it, then re-adding it with the overwrite option. but then i made the horrible mistake of pulling up the parent package, which used a different xml config, which updated the connection manager again to the wrong server name .

i pulled the configurations out the parent package and started fixing the child packages again. having fixed the child packages, i called one of them from its now xml config free parent. i was really shocked and disappointed to see that it again had the wrong server in the connection.

How could this happen?

i also noticed through wordpad++ that the config file was updated. my hypothesis is that when the parent's configuration updated the connection manager, SSIS automatically updated the now corrected config file and broke it again.

does SSIS really update config files automatically when components of it change? is it really bidirectional like this? if so its totally confusing behavior - like a ping pong ball, or putting fingers in dykes. i am getting ready to recommend that we never use XML configurations!!.

解决方案

UPDATE: I was wrong. See my comment below.

So I think you have one (possibly two) fundamental misunderstandings of how configurations in SSIS work.

The standard process for making a connection manager configurable would be:

1. Create connection manager, point it to your local / dev connection in BIDS.
2. Create one configuration for the connection manager's ConnectionString, ServerName, and DatabaseName properties. (If it's a file, add filePath, etc.)
3. In subsequent packages, just create the same connection manager, add a configuration, point it to your existing configuration file, and choose "Reuse Existing ..." (not "Overwrite", that throws out all the work you did in step 2. )
4. Deploy packages out to server and XML file to the same local location on the server.
5. Modify the XML file on the server to point to the proper database connection for that server (dev, staging, production, etc.)
6. When you run the package on that server, it'll use that XML file at run time and use the server's respective databases when running.
7. TOTALLY OPTIONAL: We actually use one XML file per data source and explicitly named connection manager. This means we can "guarantee" via naming conventions of our connection managers that all our packages which need to use a certain database are hitting the exact same database. We store these data sources at the project level and just create New Connection from Data Source, then add the configuration with the matching name, "Reuse Existing", hit OK, and you're all set.

So one misunderstanding you have is deleting the connection manager. A configuration file does not "update" a connection manager. A connection manager's properties are set at design time and are whatever you see when you double-click it in BIDS. These are hardcoded into the package (view the code and see for yourself) and can only be changed in the connection manager editor itself. So there's no such thing as a "faulty" connection manager, and no point in deleting it - as long as when you get a "success" when you test the connection in the editor, that connection manager is good to go.

Your second misunderstanding is how configuration files work. A configuration file merely replaces properties in the package execution with its own values at runtime. It doesn't modify the package at all. And conversely, the SSIS package itself will never modify a configuration file. That can only be done using a text editor outside the BIDS process or via the BIDS Configurations editor - which is I what believe happened.

I can't tell exactly by the kind of general timeline you've provided, but your using the Overwrite option suggests that you basically allowed whichever configuration edit that occurred last to "win" the privilege of setting the value all of your files would use for that particular connection.

Anyway, I would (as you can probably guess) totally recommend using XML Configurations as they are (or were, I thought!) pretty straightforward and in my opinion the easiest deployment option for a multi-tiered SSIS environment.

这篇关于ssis xml 配置 - 配置覆盖包 - ssis 是否会在没有您告知的情况下更改配置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！