快速更改SSIS包数据源参数以方便迁移 [英] Quickly changing SSIS-packages data source parameters for easy migration
问题描述
我需要将SQL数据库从Sybase迁移到MS SQL Server.在生产服务器上进行实际迁移之前，我首先使用SQL Server Management Studio的导入/导出向导"创建了一个SSIS程序包，以便与其他数据库进行测试.测试迁移已成功完成，我现在想将SSIS程序包部署到真实服务器上.
I would need to migrate a SQL database from Sybase to MS SQL Server. Before doing the actual migration on the production server I first created an SSIS-package with SQL Server Management Studio's Import/Export Wizard for testing with other databases. The test migration was successful and I would now like to deploy my SSIS-package to the real servers.
但是，似乎我不能简单地在Management Studio中运行该程序包，为其选择不同的数据源-它只能在创建该程序包的相同数据库上运行.现在，可以在名为"SQL Server商业智能开发工作室"(或简称为BIDS)的文件中进行编辑(我正在使用SQL Server 2008版本)，但是要完成每个数据流任务，分别手动更改〜150个目标源我要移动的桌子效果不佳，并且还可能出现错误.
However, it seems I cannot simply run the package in Management Studio choosing different data sources for it - it only runs on the same databases for which it was created. Now, it can be edited in something called SQL Server Business Intelligence Development Studio (or BIDS for short)(I am using the SQL Server 2008 version), but going through every data flow task changing the destination source manually for each of the ~ 150 tables I am moving is ineffective and also introduces a possibility for error.
我有一种方法可以快速更改将SSIS包的所有流任务中的所有目标源使用什么数据源?如果没有，那么有什么简单的方法可以首先使用测试数据库测试迁移，然后在部署时简单地更改数据源呢?
I there a way to quickly change what data source is to be used for ALL destination sources in ALL the flow tasks of an SSIS-package? If not, what simple method is there for testing migration with test databases first and simply changing the data sources when deploying?
我正在使用ODBC数据源，但对于某些软件包，它在BIDS中显示了OLE源.
I am using ODBC data sources, but for some the package shows OLE-sources in BIDS instead.
我希望我足够清楚.如果您还有其他问题，请询问！谢谢！
I hope I was clear enough. If you have additional questions, please ask! Thank you!
推荐答案
我将为连接管理器的ConnectionString属性使用一个变量.程序包级别的配置对于完成此任务可能非常有用.有几种方法可以做到这一点.我更喜欢在SQL Server中使用一个表，其中包含所有程序包的所有配置.如果您有多个程序包，并且需要在多个程序包之间动态更改一组连接管理器，则此方法特别有效.
I would use a variable for the ConnectionString property of the connection manager. A package level configuration can be very useful for accomplishing this task. Several ways to do this. I prefer to use a table in SQL Server that holds all the configurations for all packages. This can be especially effective if you have multiple packages and need to dynamically change a set of connection managers across those multiple packages.
基本步骤是:
- 在SSIS设计图的对面单击，然后选择包配置..."
- 创建配置类型为"SQL Server"的程序包级配置
- 将连接存储在SQL Server的配置表中
- 更改连接管理器以将变量用于ConnectionString属性
- 通过您的程序包级别配置从配置"表中填充该变量
- 要从测试"切换到生产"时，只需更新配置表中的连接字符串即可.
这些屏幕截图可以帮助...
These screenshots can help...
这是我使用本书实现的更大的程序包管理框架的一部分:
This is part of a larger package management framework that I implemented using this book:
Microsoft SQL Server 2008集成服务:问题，设计，解决方案
我强烈推荐它.只需不到一天的时间即可完成设置.本书有分步说明.
I highly recommend it. Should take less than a day to set it up. Book has step by step instructions.
这篇关于快速更改SSIS包数据源参数以方便迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
