SQL 실행 작업에서 결과 값을 받아야 할 경우가 있습니다.
오늘 하려는 이야기는 결과가 단일 행일 경우에만 해당되는 이야기입니다.
일반적인 방법으로는 SQL 실행 작업 편집기에서 결과 집합을 변경하고
결과 집합에서 결과 이름과 변수 이름을 매핑 시켜서 받아오는 방법이 있습니다.
다른 방법으로는 매개 변수 매핑에서 매개 변수의 방향을 OutPut으로 변경해서 받아오는 방법이 있습니다.
그래서 여러 가지 경우에서 나타나는 두 방법의 차이점을 알아보고자 테스트를 해 볼 생각입니다.
그래서 아래와 같은 패키지를 만들어서 비교를 해볼 생각입니다.
테스트 할 종류는 아래와 같습니다.
|
|
자료형
|
값
|
종류
|
|
1
|
String
|
있음
|
결과 집합
|
|
2
|
String
|
없음
|
결과 집합
|
|
3
|
String
|
있음
|
매개 변수 매핑
|
|
4
|
String
|
없음
|
매개 변수 매핑
|
|
5
|
Int32
|
있음
|
결과 집합
|
|
6
|
Int32
|
없음
|
결과 집합
|
|
7
|
Int32
|
있음
|
매개 변수 매핑
|
|
8
|
Int32
|
없음
|
매개 변수 매핑
|
- 변수의 준비
SSIS 새 패키지를 준비합니다.
변수를 8개 생성합니다. 이름은 그냥 변수 1, 2, 3…으로 하겠습니다.
|
이름
|
데이터 형식
|
값
|
|
변수1
|
String
|
일
|
|
변수2
|
String
|
이
|
|
변수3
|
String
|
삼
|
|
변수4
|
String
|
사
|
|
변수5
|
Int32
|
5
|
|
변수6
|
Int32
|
6
|
|
변수7
|
Int32
|
7
|
|
변수8
|
Int32
|
8
|
- 연결 관리자의 준비
하단의 연결 관리자에서 새 OLE DB 연결을 생성합니다. AdventureWorks에서 테스트 하겠습니다.
AdventureWorks가 없다면 문자열과 숫자 형을 Select해 올 수 있는 아무 DB와 연결해도 상관없습니다.
연결 관리자 이름을 SourceDB로 변경하겠습니다.
- 1) 결과 집합, String, 값 있음 (SQL 실행 작업)
1. SQL 실행 작업을 추가합니다. 이름은 1) 결과 집합, String, 값 있음이라고 변경하겠습니다.
2. Connection 속성은 SourceDB로 연결 해주고 SQLStatement 속성에는 아래의 쿼리를 입력합니다.
|
Select Top 1 Name From Production.Product
|
3. ResultSet 속성을 단일 행으로 변경하겠습니다.
4. 결과 집합 메뉴에서 결과 이름을 Name, 변수 이름은 변수1로 설정 해줍니다.
- 2) 결과 집합, String, 값 없음 (SQL 실행 작업)
1. SQL 실행 작업을 추가합니다. 이름은 2) 결과 집합, String, 값 없음이라고 변경하겠습니다.
2. Connection 속성은 SourceDB로 연결 해주고 SQLStatement 속성에는 아래의 쿼리를 입력합니다.
|
Select Top 1 Name From Production.Product Where 1 = 0
|
3. ResultSet 속성을 단일 행으로 변경하겠습니다.
4. 결과 집합 메뉴에서 결과 이름을 Name, 변수 이름은 변수2로 설정 해줍니다.
(위의 1)번 SQL 실행 작업과 쿼리와 변수만 다르고 설정은 다 같으므로 캡쳐는 생략하겠습니다.)
- 3) 매개 변수, String, 값 있음 (SQL 실행 작업)
1. SQL 실행 작업을 추가합니다. 이름은 3) 매개 변수, String, 값 있음이라고 변경하겠습니다.
2. Connection 속성은 SourceDB로 연결 해주고 SQLStatement 속성에는 아래의 쿼리를 입력합니다.
|
Select Top 1 ? = Name From Production.Product
|
3. 매개 변수 매핑 메뉴에서 변수 이름은 변수3으로 방향은 OutPut, 데이터 형식은 VARCHAR,
매개 변수 이름은 0으로 설정해줍니다.
- 4) 매개 변수, String, 값 없음 (SQL 실행 작업)
1. SQL 실행 작업을 추가합니다. 이름은 4) 매개 변수, String, 값 없음이라고 변경하겠습니다.
2. Connection 속성은 SourceDB로 연결 해주고 SQLStatement 속성에는 아래의 쿼리를 입력합니다.
|
Select Top 1 ? = Name From Production.Product Where 1 = 0
|
3. 매개 변수 매핑 메뉴에서 변수 이름은 변수4로 방향은 OutPut, 데이터 형식은 VARCHAR,
매개 변수 이름은 0으로 설정해줍니다.
(위의 3)번 SQL 실행 작업과 쿼리와 변수만 다르고 설정은 다 같으므로 캡쳐는 생략하겠습니다.)
- 5) 결과 집합, Int32, 값 있음 (SQL 실행 작업)
1. SQL 실행 작업을 추가합니다. 이름은 5) 결과 집합, Int32, 값 있음이라고 변경하겠습니다.
2. Connection 속성은 SourceDB로 연결 해주고 SQLStatement 속성에는 아래의 쿼리를 입력합니다.
|
Select Top 1 ProductID From Production.Product Order By ProductID
|
3. ResultSet 속성을 단일 행으로 변경하겠습니다.
4. 결과 집합 메뉴에서 결과 이름을 ProductID, 변수 이름은 변수5로 설정 해줍니다.
(위의 1)번 SQL 실행 작업과 쿼리와 변수만 다르고 설정은 다 같으므로 캡쳐는 생략하겠습니다.)
- 6) 결과 집합, Int32, 값 없음 (SQL 실행 작업)
1. SQL 실행 작업을 추가합니다. 이름은 6) 결과 집합, Int32, 값 없음이라고 변경하겠습니다.
2. Connection 속성은 SourceDB로 연결 해주고 SQLStatement 속성에는 아래의 쿼리를 입력합니다.
|
Select Top 1 ProductID From Production.Product Where 1 = 0
|
3. ResultSet 속성을 단일 행으로 변경하겠습니다.
4. 결과 집합 메뉴에서 결과 이름을 ProductID, 변수 이름은 변수6으로 설정 해줍니다.
(위의 1)번 SQL 실행 작업과 쿼리와 변수만 다르고 설정은 다 같으므로 캡쳐는 생략하겠습니다.)
- 7) 매개 변수, Int32, 값 있음 (SQL 실행 작업)
1. SQL 실행 작업을 추가합니다. 이름은 7) 매개 변수, Int32, 값 있음이라고 변경하겠습니다.
2. Connection 속성은 SourceDB로 연결 해주고 SQLStatement 속성에는 아래의 쿼리를 입력합니다.
|
Select Top 1 ? = ProductID From Production.Product Order By ProductID
|
3. 매개 변수 매핑 메뉴에서 변수 이름은 변수7로 방향은 OutPut, 데이터 형식은 VARCHAR,
매개 변수 이름은 0으로 설정해줍니다.
(위의 3)번 SQL 실행 작업과 쿼리와 변수만 다르고 설정은 다 같으므로 캡쳐는 생략하겠습니다.)
- 8) 매개 변수, Int32, 값 없음 (SQL 실행 작업)
1. SQL 실행 작업을 추가합니다. 이름은 8) 매개 변수, Int32, 값 없음이라고 변경하겠습니다.
2. Connection 속성은 SourceDB로 연결 해주고 SQLStatement 속성에는 아래의 쿼리를 입력합니다.
|
Select Top 1 ? = ProductID From Production.Product Where 1 = 0
|
3. 매개 변수 매핑 메뉴에서 변수 이름은 변수8로 방향은 OutPut, 데이터 형식은 VARCHAR,
매개 변수 이름은 0으로 설정해줍니다.
(위의 3)번 SQL 실행 작업과 쿼리와 변수만 다르고 설정은 다 같으므로 캡쳐는 생략하겠습니다.)
- 기타 설정
1. SQL 실행 작업을 1번부터 8번까지 모두 연결해줍니다.
2. 그리고 마지막 8번에서 중단점 편집을 하겠습니다. OnPostExecute 이벤트를 받는 경우 중단을 사용하겠습니다.
3. 2), 6)번 연결선에 작업을 한가지 해야 하지만 일단 실행을 해보겠습니다. F5 키를 눌러서 디버깅 해보겠습니다.
…
에러가 발생했습니다.
실행 결과 탭을 클릭해서 오류 메시지를 확인해보겠습니다.
”단일 행 결과 집합이 지정되었지만 행이 반환되지 않았습니다.” 라는 오류 메시지를 확인할 수 있습니다.
4. 그럼, 테스트의 원활한 진행을 위해서 2), 3) 번 사이의 제약 조건을 변경하도록 하겠습니다.
디버깅을 중지하고 제어흐름 디자인 창으로 돌아옵니다.
2), 3) 번 사이의 작업 선을 더블 클릭해서 편집기를 엽니다. 제약 조건 성공을 완료로 변경하겠습니다.
다시 실행해보겠습니다.
겍! 예상됐던 바이지만 또 에러가 발생했습니다.
진행률 탭을 확인해보면 6)번에서도 2)번과 동일한 에러가 발생한 것을 확인할 수가 있습니다.
5. 위 4번의 작업을 6), 7) 번 사이의 작업 선에도 합니다. (선행 제약 조건을 편집합니다.)
다시 실행해보겠습니다.
6. 메뉴에서 디버그 > 창 > 지역을 클릭해서 지역 변수 디버깅 창을 확인해보겠습니다.
Variables 트리를 열면 변수1~8까지 확인을 할 수 있습니다.
7. 분석
데이터가 있을 경우에는 데이터 형식에 관계없이 올바른 값을 가져오는 것을 확인할 수 있습니다.
2), 6)번은 오류로 인한 실패가 발생했기 때문에 변수2, 변수6의 값은 변수의 초기값에서 변하지 않았다고 생각할 수 있습니다.
2), 6)번과 동일한 작업을 했지만 결과 집합 대신 매개 변수로 출력 값을 받은 4), 8)번은 오류가 발생하지 않았습니다.
대신 String형 변수엔 빈 문자열이 Int32형 변수엔 이상한 값이 들어갔습니다.
이후에 별도의 SQL 실행 작업을 추가해서 테스트해봤더니 저 값은 계속 바뀌더군요.
DBNULL 값을 처리하게 되는 당시의 메모리 주소와 관계가 있지 않을까 조심스레 예상해봅니다.
8. 결론.
결과 집합을 이용하여 값을 가져올 때에는 실행하는 쿼리 내에서 Null을 처리하도록
쿼리를 작성하거나 패키지에서 선행 제약 조건을 편집해주는 등의 처리가 필요할 것입니다.
반면, 매개 변수를 이용해서 값을 가져올 때에는 값이 Null이라면
문자열은 빈 문자열이 출력된다는 것을 기억하시고
숫자 형일 때에는 예상할 수 없는 값이 나올 수 있으므로 주의하여 사용하는 것이 좋을 것 같습니다.
대신 Count(*)처럼 출력 결과가 Not Null이 보장된 쿼리에서는 사용할 수 있겠습니다.
매개 변수를 이용해서 값을 받아오면 문자열 컬럼의 경우 쿼리에서
Null 처리를 하지 않고 편리하게 가져올 수 있는 장점이 있습니다.
결과 집합과 매개 변수를 이용한 출력 값 사용은
각각, 장, 단점이 있으므로 상황에 맞게 사용하는 지혜가 필요할 것 같습니다.
끝으로 어설픈 응용 팁을 적자면,
매개 변수를 이용해서 단일 행을 받아오려면
Select Top 1 ? = col1, ? = col2 from Table
처럼 매개 변수를 여러 개 사용하시면 가능합니다. ^^
ImportExcel2.dtsx
stuanswer.xls
20070201_ForeachContainer.zip
