기본 강좌 23 - OLE DB 명령 변환

OLEDB명령예제.dtsx

OLE DB 명령

 

OLE DB 명령은 입력되는 데이터의 각 행 별로 SQL 작업을 수행하는 변환 작업입니다. SQL에서의 커서(Cursor)와 유사한 방식입니다. 예를 들어, 우편번호 1000개가 입력 데이터인 경우, 각 우편번호에 대해 상세 주소를 별도의 테이블에 저장하도록 하는 프로시저를 수행할 수 있습니다. 각 행 별로 수행될 SQL 쿼리에는 ? 로 표시되는 입력 매개 변수가 포함되며, 열 매핑에서 이 ? 에 대한 입력 데이터의 매핑 작업을 설정합니다.

 

SQL 쿼리에서 커서를 이용하여 데이터를 처리해야 하는 경우와 같이, 데이터 처리 프로세스에서도 커서 방식의 처리가 필요한 경우가 있습니다. 이전 버전의 경우, SQL 실행 작업에서 SQL 커서를 만들어 사용하거나 ActiveX 스크립트 작업에서 ADO 커서를 이용하여 처리하였지만, SQL 2005 SSIS에서는 OLE DB 명령 변환을 이용한다면 보다 효과적이며 편리하게 사용할 수 있습니다. OLE DB 명령은 변환 작업에 포함되지만, 입력되는 열과 출력되는 열이 동일하며 열이 추가되거나 변경되지는 않습니다.

 

 

 

OLE DB 명령의 편집기는 다음과 같이 4개의 탭으로 구성되어 있습니다.

 

Ÿ           연결 관리자 – OLE DB 명령을 수행할 연결을 지정합니다. 이 연결은 연결 관리자의 OLE DB 연결을 사용합니다. 여기서 지정하는 연결은 OLE DB 명령 변환 작업에서 사용하는 연결을 지정하는 것이기 때문에, 반드시 입력되는 데이터와 동일한 연결일 필요는 없습니다. 또한, 원본에서 사용하는 OLE DB 연결을 그대로 사용할 수도 있습니다.

Ÿ           구성 요소 속성 – 각 행의 데이터로 수행할 SQL 명령을 설정합니다. SQL 명령 외에 Timeout 값, Code Page 등을 설정할 수 있으며, 변환 작업의 이름이나 설명을 변경할 수도 있습니다. SqlCommand에는 저장 프로시저 또는 Ad-Hoc 쿼리 형태로 설정하며, 쿼리에는 입력 매개 변수로 ? 를 지정합니다.
               예) exec sp_insertdata ?,?,?

Ÿ           열 매핑 – SqlCommand의 매개 변수에 대해 입력 데이터의 열을 매핑 시킵니다. 사용 가능한 입력 열 부분이 입력되는 데이터의 열이며, 사용 가능한 대상 열 부분이 매개 변수를 나타냅니다. 매개 변수는 Ad-Hoc 쿼리인 경우, 차례대로 Param_0, Param_1, Param_2, … 등과 같은 형태로 나타나며, 저장 프로시저인 경우 프로시저의 입력 변수 명이 나타납니다.

Ÿ           입/출력 속성 – 입력 열 및 출력 열에 대한 상세 속성을 조회하거나 데이터 유형을 변경하는 등의 변경 작업을 할 수 있습니다.

 

 

 

 

따라하기

 

AdventureWorks 데이터베이스에 있는 [Production].[Product] 테이블의 ProductID에 대해, [Prduction].[TransactionHistory] 테이블에서 해당 ProductID 개수 및 최근 OrderID를 구하여 별도의 테이블에 저장하는 예제를 구현해 봅니다.

 

1.        쿼리 분석기를 이용하여 다음과 같은 저장 프로시저 및 임시 테이블을 생성합니다.

USE AdventureWorks GO   IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestResult]') AND type in (N'U'))              DROP TABLE [dbo].[TestResult] GO CREATE TABLE [dbo].[TestResult] (              ProductID int NOT Null,              OrderCount int Null,              LastOrderID int Null ) GO   IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspTestProc]') AND type in (N'P', N'PC'))              DROP PROCEDURE [dbo].[uspTestProc] GO CREATE PROCEDURE [dbo].[uspTestProc]              @ProductID int AS              INSERT [dbo].[TestResult](ProductID, OrderCount, LastOrderID)              SELECT @ProductID, COUNT(*), MAX(ReferenceOrderID) FROM Production.TransactionHistory              WHERE ProductID = @ProductID GO

 

2.        빈 패키지 파일을 하나 추가한 후, 데이터 흐름 작업을 추가합니다.

3.        아래 부분에 있는 연결 관리자에서 AdventureWorks DB를 지정하는 OLE DB 연결을 생성합니다.

4.        데이터 흐름 영역에서 OLE DB 원본을 추가한 후, 3에서 추가한 연결을 데이터 원본 연결로 지정하고, [Production].[Product] 테이블을 선택합니다.

5.        도구 상자에서 OLE DB 명령 변환을 추가한 후, OLE DB 원본의 녹색 선을 연결합니다.

6.        OLE DB 명령을 더블 클릭하여 고급 OLE DB 명령 편집기를 연 후, 연결 관리자 탭에서 AdventureWorks에 대한 연결을 지정합니다.

7.        구성 요소 속성 탭에서 SqlCommand 속성에 다음과 같이 입력합니다.
         EXEC dbo.uspTestProc ?

8.        열 매핑 탭에서 사용 가능한 입력 열의 ProductID를 사용 가능한 대상 열의 @ProductID로 매핑 시킵니다. 만약, 실행시키는 쿼리가 저장 프로시저가 아닌 Ad-Hoc 쿼리인 경우, Param_0, Param_1,… 과 같은 형식으로 나타납니다.

9.        확인을 누른 후, 패키지를 수행합니다.