有没有办法以编程方式轻松更改多个 SSIS 包上的服务器名称?
作为发布周期的一部分,我们正在创建多个 SSIS 包来迁移大型数据库。 我们最终可能会得到大约 5-10 个 SSIS 包。
由于我们有 4 个环境(开发、QA、登台、生产等),是否有一种有效的方法可以在每个 SSIS 包经历不同的服务器环境时更改它们的目标服务器?理想情况下,可能有一个运行的脚本将所需的服务器作为参数。
We are creating several SSIS packages to migrate a large database as part of a release cycle.
We may end up with about 5-10 SSIS packages.
As we have 4 environments (dev, QA, staging, production, etc.), is there an efficient way to change the destination server for each SSIS package as they go through the different server environments? Ideally, there could be a script that is run that would take as a parameter the server that was needed.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您可以使用配置文件来存储服务器的连接字符串。然后,当您从一个环境移动到另一个环境时,您只需更改配置文件即可。要简单地在程序包的控制界面上创建一个配置文件,
1) 右键单击并从上下文菜单中选择包配置。
2) 如果尚未选中“启用包配置”复选框,
3) 然后单击“添加...”按钮。
4)单击对话框中的“下一步”,
5)然后添加配置文件名:并单击下一步。
6) 在“对象视图”中的“连接管理器”下,展开您的连接,然后展开“属性”并选中“ConnectionString”旁边的框。
7)然后点击下一步
8)然后完成。
您现在已经有了一个 xml 文件,其名称与您在上面第 5 步中的名称相同。您可以使用文本编辑器编辑此文件,并更改连接字符串以在每次运行之前映射到您需要的服务器。
创建后,您可以在多个包之间共享配置文件,只要包之间引用的对象命名相同即可。
这是一个关于配置的初级教程,保存配置的方法有很多种,这只是其中一种。有关配置的更多信息,请参阅您最喜欢的 SSIS 书籍
You could use a configuration file to store the connection strings for the servers. Then as you moved from environment to environment, you would simply change the config file. To simply create a config file, on the control surface of your package,
1) right click and choose Package Configurations from the context menu.
2) Check the box for Enable package configurations if it is not already selected,
3) then Click the Add... button.
4) Click next on the dialog,
5) then add a Configuration file name: and click next.
6) In the Objects View, Under Connection Managers, expand your connection, then expand Properties and check the box next to ConnectionString.
7) Then click next
8) then finish.
You now have an xml file named what you named it in step 5 above. You can edit this file with a text editor and change the connection string to map to whichever server you need it to before each run.
Once created you can share the config file between multiple packages as long as the objects referenced are named the same between the packages.
This is a rudimentary tutorial on configurations, there are many ways of saving configurations of which this is only one. For more information on configurations consult your favorite SSIS book
我们使用配置表来存储服务器的配置。但配置文件也能很好地工作。我们喜欢这个表,因为我们正在对 SSIS 包元数据进行报告,并且当存储在表中时更容易获取这些数据(以及我们存储的许多其他数据)。
We use a config table that stores the configurations for the server. But config files work well too. We like the table because we are doing reporting on SSIS package meta data and it's easier to grab this data (along with a lot of other data we store as well) when stored in a table.
威廉·托德·萨尔兹曼的回答涵盖了大部分要点。我还有一些要补充的内容:
William Todd Salzman's answer covers most points. I have a couple more to add: