Oracle DB에 OLE DB 명령 변환 구성하기

Oracle DB에 OLE DB 명령 변환 구성하기

 

한대성

MS SQL Server MVP

에이디컨설팅 | SQLLeader.com 운영자

  

 

 SQL 쿼리에서 커서(Cursor)를 이용한 데이터 처리 방법과 같이, 입력된 데이터를 개별 건 단위로 처리하기 위한 기능으로 SSIS에서 OLE DB 명령이라는 개체가 있습니다. 예를 들어, 원본에서 읽어온 다음, 변환을 거친 1,000개의 데이터를 건 단위로 UDDATE 또는 INSERT, DELETE를 수행하도록 해야 할 경우에 이용할 수 있는 변환입니다.

 

  

   (OLE DB 명령에 대한 상세 사항은 다음 링크의 강좌를 참고하시기 바랍니다.)

 

OLE DB 명령은 다음 형식과 같은 매개변수가 포함된 쿼리를 입력한 다음, 입력 데이터의 열과 쿼리의 입력 매개변수를 매핑하게 됩니다.

UPDATE TARGETTABLE SET CODE = 'AA' WHERE CODE = ?

 

   

 

 

하지만, 오라클 연결 관리자(ex : OraOLEDB.Oracle.1)와 같이 연결 관리자에서 테이블의 매개 변수 정보를 반환하지 않는 경우도 있습니다.

 

위와 같이 매개 변수가 포함된 쿼리를 입력하면, 매개 변수에 해당하는 열의 정보를 얻을 수 없기 때문에 다음과 같은 에러 메시지를 출력합니다.

 

Error at Data Flow Task [OLE DB Command [447]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80040E51  Description: "공급자는 매개 변수 정보를 유도할 수 없으며 SetParameterInfo는 호출되지 않았습니다.".

 

이와 같은 경우에는 OLE DB 명령을 이용한 작업을 직접 수행할 수는 없으며, 대신 다음과 같은 스크립트 구성 요소(변환)을 이용하여 처리하거나, SQL Server에서 연결된 서버(Linked Server)를 구성한 후 데이터를 입력하는 저장 프로시저를 별도로 구현하여 이용하는 방법을 사용해야 합니다.

 

 

 

 

스크립트 구성 요소(변환)을 이용하여 처리하는 방법

 

OLE DB 명령 대신 스크립트 구성 요소(변환)을 추가한 후, 다음과 같은 스크립트를 이용합니다.

본 예에서는 AAA라는 테이블에 Seq라는 Integer열과 Col이라는 Char(10)의 열에 데이터를 입력하는 쿼리를 수행하는 경우입니다. OleDBCommand 개체와 OledbParameter 개체를 이용해서 매개 변수의 정보를 지정하고, 입력하는 방식으로 처리합니다.

   

 

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Data.OleDb     Public Class ScriptMain     Inherits UserComponent       Dim oledbConn As OleDbConnection     Dim oledbCmd As OleDbCommand     Dim oledbParam As oledbParameter       Public Overrides Sub AcquireConnections(ByVal Transaction As Object)           Dim connectionString As String         oledbConn = New OleDbConnection("Provider=OraOLEDB.Oracle.1;Password=password;Persist Security Info=True;User ID=system;Data Source=orcl")         oledbConn.Open()       End Sub       Public Overrides Sub PreExecute()           oledbCmd = New OleDbCommand("INSERT INTO AAA(seq, col) VALUES(?, ?)", oledbConn)         oledbParam = New OleDbParameter("@seq", OleDbType.Integer)         oledbCmd.Parameters.Add(oledbParam)         oledbParam = New OleDbParameter("@col", OleDbType.Char, 10)         oledbCmd.Parameters.Add(oledbParam)       End Sub         Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)         With oledbCmd             .Parameters("@seq").Value = Row.seq             .Parameters("@col").Value = Row.col             .ExecuteNonQuery()         End With     End Sub       Public Overrides Sub ReleaseConnections()         oledbConn.Close()     End Sub   End Class

 

 

 

 

Linked Server를 이용하는 방법

 

Management Studio의 서버 개체 -> 연결된 서버 또는 쿼리 분석기를 이용해서 Oracle 또는 다른 DBMS와의 연결을 설정합니다.

 

예)

EXEC sp_addlinkedserver 'OracleLinked', 'Oracle','MSDAORA' GO EXEC sp_addlinkedsrvlogin 'OracleLinked', 'FALSE',NULL, 'system', 'passwd' GO

 

 

 

다음과 같이 연결된 서버에 데이터를 입력하는 저장 프로시저를 생성합니다.

CREATE PROC INSERT_ORACLE @SEQ int, @Col Char(10) AS         INSERT OracleLinked..SYSTEM.AAA         VALUES(@Seq, @Col) GO

 

 

그런 다음, SSIS 패키지의 OLE DB 명령 개체에서 이 저장 프로시저가 생성된 OLE DB 연결로 변경하고 SqlCommand에 이 저장 프로시저를 입력합니다.

 

열 매핑 탭에서 각 매개 변수에 맞는 입력을 지정하면 됩니다.

 

 

 

 

  

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.