创建一个SSIS包-要将数据从Oracle复制到SQL Server [英] Create a SSIS Package - To Copy data from Oracle to SQL Server

问题描述

我想编写一个SSIS包，用于将数据从oracle复制到MS Sql服务器.而且条件是，通过在SQL中选择一个表，可以知道必须将哪些数据从oracle移到SQL.基本上，如果SQL Server的该表中没有该值，则必须从oracle中填充SQL Server中的Table1.Column1.我们在该表中还具有公共列值，以将记录与sql server和oracle映射.为此，我计划在SSIS程序包中进行此操作.

I want to write a SSIS Package for copying data from oracle to MS Sql server. and also the condition is, What are the data has to be moved from oracle to SQL will be known by selecting a table in SQL. Basically Table1.Column1 in SQL server has to be filled from oracle if the value is not there in that table in SQL Server. we have also have common column value in that table to map the record with sql server and oracle. so to do this i have planned to do it in SSIS Package.

任何人都可以详细说明如何为上述情况创建SSIS程序包吗?

Can any one explan me in detail how to create a SSIS Package for the above said scenario.

预先感谢您的帮助.

推荐答案

您应该使用Lookup连接SQL Server和Oracle的公共列(相同数据类型)，并从Oracle中没有匹配记录的值中检索值. SQL服务器.

You should use Lookup to join the common column( same data type) of SQL server and Oracle and retrieve the values from Oracle which does not have a matching record in SQL server.

设计

1.使用SSIS 2005

1.Using SSIS 2005

1. 拖动OleDB源并指向您的Oracle数据库并选择表.
2. 拖动查找并选择SQL Server连接并指向其表.
3. 在列"选项卡中，加入公共列，然后在右侧选择要检索的列.
4. 单击配置错误"输出，然后为连接"列选择重定向行.
5. 拖动Oledb命令并将其连接到查找的错误输出. 6，在Oledb Command中写一个插入语句

1. Drag a OleDB Source and point to your Oracle Database and select the table .
2. Drag a lookup and select the SQL Server connection and point to its table .
3. In columns tab join the common column and select the columns in the right hand side which you need to retrieve .
4. Click on Configure Error output and select redirect row for the join column .
5. Drag a Oledb Command and connect it to the error output from the lookup. 6.Write an insert statement in Oledb Command

2.如果您使用的是SSIS 2008，则无需在查找中配置错误输出.只需将不匹配的输出从查找拖动到SQL Server目标即可.

2.If you are using SSIS 2008 then no need to configure the Error Output in lookup .Just drag the no match output from Lookup to SQL Server destination.

这篇关于创建一个SSIS包-要将数据从Oracle复制到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！