在 SSIS 包中使用临时表
我正在编写从一个数据库到另一个数据库的基本文件转储。我正在使用 SSIS 2008 并创建几个包来将我的数据从 MSSQL 2010 数据库转换为 MYSQL 5.1 数据库。
所有连接均已设置，记录可以在两个数据库之间传输，但我想在转换过程中使用临时表，并使用临时表作为数据流任务中的 MSSQL 源，以将表转储到等待的 MYSQL 表中。
我在设置此问题时遇到了问题。我正在使用 OLEDB 连接，并将 RetainSameConnection 属性和 DelayValidation 属性设置为 true。将源图设置为 MSSQL 数据库的源时，我无法从控制流中找到在早期任务中创建的临时表。我对这两项任务使用相同的连接管理器。
有人对此有什么想法或经验吗？
作为一个简单的例子，一个任务确实......
SELECT *
INTO #TMP
FROM CUSTOMERS
（这是一个简化的例子，我意识到在这种情况下我可以只使用客户表，所以请耐心等待） 是否可以在数据流操作中使用此临时表作为源表？
I am writing a basic file dump from one database to another. I am using SSIS 2008 and creating several packages to transform the data I have from a MSSQL 2010 database to a MYSQL 5.1 database.
All the connections are set up and records can be tranfered between the two databases but I would like to use temp tables in the transform processes and use the temp table as the MSSQL source in a dataflow task to dump the table in an awaiting MYSQL table.
I have been having problems setting this up. I am using an OLEDB connection and have set the RetainSameConnection property as well as the DelayValidation property to true. When setting up the source figure as the source from the MSSQL database I cannot find the temp table I have created in an earlier task from the control flow. I am using the same connection manager for these two tasks.
Anyone have any ideas or experience with this?
As a simple example one task does..
SELECT *
INTO #TMP
FROM CUSTOMERS
(This is a simplified example and I relize in this case I could just use the Customers table so bear with me)
Is it possible to use this temp table in a dataflow operation as the source table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
正如我在评论中提到的，这不是解决方案，而是解决方法。 SSIS 使用结果集的形状来绑定任务中的属性。由于临时表在数据库中并不总是可用，即使您将
DelayValidation设置为true，这也可能会在 SSIS 中导致错误。我的解决方案是在您要连接的任何数据库中创建一个
SSIS架构。这样做的原因是安全性以及仅在 SSIS 包中使用的对象（主要是临时表）的明确分离。与其将表扔到您的
dbo架构中（无论如何您都不应该这样做，真丢脸），您可以在SSIS架构中创建它们。典型的数据流会在开始时截断表、加载值并执行所需的任何操作，并可选择在完成时截断它。只要表始终可用，SSIS 就可以检查结果集的形状。As I mentioned in my comment, not much of a solution and more of a workaround. SSIS uses the shape of result sets to bind properties in tasks. As temp tables are not always available in the database this can cause errors in SSIS even if you set
DelayValidationtotrue.My solution is to create an
SSISschema in whichever database you're connecting to. The reasons for doing so are security and clear separation of objects that are only used within SSIS packages - primarily staging tables.Instead of throwing tables in your
dboschema (you shouldn't be anyway, shame on you) you'd create them in theSSISschema. A typical data flow would truncate the table when it begins, load values and perform whatever operations are required, optionally truncating it when complete. As long as the table is always available SSIS can examine the shape of result sets.您不应使用临时表作为源，因为它无法识别选择的列。请改用表变量或 CTE。
You should not use temp tables as the source as it will not recognize the columns for the select. use table variables or CTEs instead.