어제... 자주 가는 사이트 중의 하나인 SQLLeader.com에 SSIS에 대해서 문의를 했었다.
나와 비슷하게 느끼는 사람이 생각보다 있는 듯 했다.
그 중 읽어볼 만한 좋은 글을 퍼왔다. (혹시 site 폐쇠되면 ... ㅠㅠ)
link :
http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intSeq=2951
-- 본인의 질문
안녕하세요.
SQL Leader.com 에서 눈팅하는 회원입니다.
질문이 아니라 업무 성향이라고 할까 처리 방법이라고 말해야
할까.. 좀 단어선택이 어렵네요.
뭐.. 하여간 그런 것들에 대한 이야기를 해보고 싶어서 글을 올립니다.
토론이라면 토론이고...
그냥 다른 분들은 처리를 어떤 방식으로 주로 하시는지 궁금하고요..
이런 류의 것이 대부분 그렇듯 상황에 따라서 적절한 방법이 있을 뿐,
절대적인 정답이 존재하기 어려운 것이기도 하고... (아.. 개인적으로 그렇게 생각한다는 것이지 제 말이 꼭 맞다는 건
아닙니다.)
SQL Leader.com에서 기술자료 올라오는 걸 읽는 것이 참 좋아서 자주 들리게 되는데...
SSIS 부분의
기술자료가 압도적으로 많은 것을 보게 됩니다.
저의 경우는 SSIS를 가능한한 사용하지 않고 OPENQUERY나 혹은 linked
server를 사용해서 어떻게 해서든 Stored procedure를 사용해서 그 안에서 문제를 해결하려고 합니다. 제가 코드 보기가 더 쉽다는
이유이기도 하고... 아직 SSIS를 사용해야만 하는 상황을 만나지 못해서 일 수 도 있습니다.
다 읽어본 것은 아니지만,
SSIS를 사용하지 않고 위에 제가 언급한 방법으로 충분히 해결 가능한 것들이라고 느껴지는 예가 대다수 인듯 합니다.
하지만, 기술자료가
이렇게나 많이 올라오는 것을 보면 SSIS가 무척이나 유용한 것 같은데...
SSIS의 매력을 알고 싶습니다.
p.s.
사실 이런건 토론게시판 같은데 올리고 싶은데... 그런 게시판이 여기는 없네요...
----------------------------------------------------------------------------
-- 한대성님의 글
어찌보면 SSIS의 기능을 하나 더 익히는 것보다 더 중요한 부분일 수 있습니다.
나름대로 사이트에 게시물을 하나하나 추가하곤
하는데, 전체적인 부분이 아닌, SSIS를 가지고 작업을 하면서 익힌 새로운 방법이나 지식들을 단편적으로 올린 것이기에 더욱 더 막연하게 보일
수도 있겠네요.
개인적인 경험을 바탕으로 설명드리겠습니다. 제가 했었던 업무나, 프로젝트 등입니다.
처음에 저는 옥션에서 DataWarehouse를 개발하고 관리하는 DBA로 SSIS의 이전 버전인 DTS를 접했습니다.
지금도 많은
자료에서 ETL(Extract, Transformation, Loading) 툴이라 하면 Data Warehouse에서 데이터를 추출하는 데
이용한 툴로 소개해 놓은 곳이 많지요.
운영 데이터, 여러 형태의 데이터 서버들에서 데이터를 읽어들인 후, 정재하고 DW Database에
데이터를 적재하는 부분에 저장 프로시저 대신 DTS 패키지들을 이용하였습니다. 그 다음엔 OLAP(Analysis Service) Cube를
Processing하고 Web Report를 Generating하고 특정 사용자들에게 추출한 보고서를 메일로 보내는 등 여러 작업들을
수행하였습니다.
데이터만 뽑는다면, 특히 동일 박스(서버)에서 데이터를 뽑아서 다른 DB에 적재하는 역할만 한다면 굳이 DTS, SSIS를
쓸 필요는 없겠지요.
제가 했었던 부분은 여러 서버들에 있는 데이터를 모으는 것이었고, OPENQUERY, Linked Server로
하기에는 부하 및 문제가 많았지요.
가장 간단한 문제로, SQL Server 2000에서 Linked Server를 이용할 때 NOLOCK
못 쓴다는 것. 그리고 Oracle 데이터를 Linked Server로 읽어올 때 간혹 부하가 걸리는 문제 등.
이러한 부분에 대해서는
DTS나 SSIS가 월등히 우수합니다.
그런 다음 OLAP Cube를 Processing하는 작업을 수행해야 하는데, 고맙게도 DTS 내에
이러한 기능이 있어서 이를 이용하였습니다.
그러고선 Report를 Generating하는 프로그램을 VB로 만들었는데, 이것 또한 실행토록
할려니깐 DTS의 프로그램 실행 작업 개체를 이용하니깐 편하더라구요.
또한 메일 보내는 기능도 있어서 이를 이용하게 되었습니다.
패키지가 100개가 넘고 200개가 넘으니깐 관리를 해야 겠더라구요. 1번 패키지가 끝나고 2번 패키지 수행, 2번 패키지 끝나고 3,
4번 패키지 수행 형태로.
이것 또한 패키지의 자식 패키지를 호출하는 기능을 사용하니깐 편하더라구요.
이리저리 해서 한 5년 동안 SQL 2000의 DTS를 아주 재미있게 접하고 사용했었습니다.
이후에 회사를 나와서 지금의 전 직장인 에이디컨설팅에서 여러 프로젝트를 하는데 많은 도움이 되었습니다.
그 중에서 몇 개
말씀드리자면.
금융권에서 데이터를 처리하는 배치 작업을 프로그램과 저장 프로시저로 처리하는 데, 작업의 특성 상 텍스트 파일로 만들로
(데이터를 만다고 하지요.) 이를 보내고 또 결과를 받아서 이를 반영하는 작업이 많았습니다.
처리 로직도 상당히 복잡하긴 해서 저장
프로시저 행 수가 2000~3000행을 넘는 것이 대부분이었구요. 작업이 돌면 몇 시간씩 돌고 에러가 나면 어디서 나는지 확인하기
어려웠습니다.
이러한 작업을 SSIS로 바꾸면서 이벤트 처리기를 이용해서 각 단계별 수행 상태를 기록하도록 하여 모니터링을 구현하였고,
순차적인 저장 프로시저에 비해 시각적인 면에서 우수하며, 병렬로 작업을 처리할 수 있는 SSIS 패키지가 유용하였습니다.
파일을 생성하고
검증하고 에러가 나는 경우에 이에 대한 처리를 하고 관리자에게 통보하고, 생성한 파일을 FTP로 전송하는 등 모든 필요한 기능을 SSIS로
구현할 수 있었지요.
다른 경우는 제조부분에서의 작업인데, 여러 서버 간에 데이터 동기화 및 처리 작업을 수행하는데, Linked Server로 처리하는 것에는
여러 문제가 있었습니다. 이 경우에도 SSIS를 이용해서 패키지를 만들고 Looping 기능을 이용해서 여러 서버에 적용하도록 구현해서 서버의
대수가 늘어나더라도 별 작업없이 쉽게 처리할 수 있도록 구현되었지요.
이 외에도 여러 서버의 성능 정보 파일을 읽어온 후, DB에 적재하고 이를 처리하고, Raw 파일을 압축해서 다른 곳에 보관하도록 하는
기능 등에도 이용했었구요.
가장 최근에는 외부에서 특정 폴더에 데이터 파일을 떨궈(^^) 주면, SSIS 패키지가 이를 감시하고
있다가(WMI 감시자를 이용해서) 생성되면 이를 여러 곳에 있는 DB에 반영합니다. 반영하다가 에러가 나는 데이터는 별도의 폴더로 이동시키고
담당자에게 메일 보냅니다.
다른 여러 기능들도 있는데, 대략 여기까지만 정리하고..
그럼 SSIS가 항상 좋으냐.. 10개 고객사를 가는 경우 3~4군데 정도는 SSIS를 쓰지말고 그냥 BCP나 Bulk Insert,
Linked Server 또는 단순히 저장 프로시저를 이용하라고 말씀드리고 그렇게 변경합니다.
SSIS의 장점을 꼽으로면 저 개인적으로는
SQL Query 기능 + Programing 기능을 최우선으로 생각합니다.
SQL 2005 부터는 CLR 기능을 이용해서 프로그램 기능을
쿼리에서 쉽게 사용할 수 있긴 하지만, 기본적으로 Ad-Hoc 쿼리 또는 저장 프로시저는 DB의 영역에서 처리를 해야 합니다.
간단히 예를
들면, 테이블에 있는 데이터를 이용해서 B라는 파일 서버에 파일로 생성하고 싶습니다. 이를 저장 프로시저로 구현한다면, BCP 명령을 이용해서
파일을 생성하고 이를 복사해야 합니다. 파일 복사는 xp_cmdshell 명령을 이용해서 해도 되겠지만, SQL 2005에서는 기본값으로
비활성화이지요. 갑갑해 집니다.
만약 이를 프로그램(VB.net이나 C#등)으로 만든다면 후딱 만들겠지요.
SSIS에서는 쿼리 뿐만
아니라 스크립트 작업 등도 구현할 수 있기에 구현 가능한 범위가 넓어졌다고 생각합니다.
또 다른 장점은 하나의 패키지에서 할 수 있는 작업이 다양하다라는 것입니다.
예를 들어, 웹 로그 데이터를 처리하는 작업을
생각해봅니다.
a. 웹 서버에서 로그 파일이 생성되었는지를 확인하고 이를 DB에서 읽을 수 있는 위치로 복사합니다.
b. 복사한
파일을 DB 테이블에 넣습니다. 넣다가 에러도 날 것이고 필터링도 해야 할 것이고.
c. 데이터를 정재합니다. 거를 것 거르고, 넣을 것
넣고.
d. 처리한 파일은 삭제하거나 압축해서 다른 곳으로 보냅니다.
e. 데이터 처리가 끝나면 이를 집계해서 보고서를 생성하거나
결과물을 메일로 보냅니다.
이러한 기능을 구현해야 한다고 할 때, 쿼리가 할 수 있는 부분은 간단히 b, c 정도이고, a, d, e
단계를 구현하기 위해서는 별도의 프로그램이나 쉘 스크립트 등을 만들어 구현해야 합니다.
SSIS로 이를 구현한다면
a. File
시스템 작업으로 구현
b. 데이터 흐름 작업을 이용해서 정재, 변환을 거쳐 대상 테이블에 적재
c. SQL 실행 작업을 이용해서
데이터 처리
d. 프로세스 실행 작업이나 파일 시스템 작업으로 처리
e. 메일 보내기 작업으로 구현
SSIS가 SQL Server에 포함된 기능이라고 해서 DB의 기능에만 국한해서 생각할 필요는 없습니다.
간단히 한 마디만 덧붙이자면..
우리가 쓰는 프로그램들을 VB6.0 으로도 만들 수 있고 C++로도 만들 수 있으며 C#, Java
등으로도 다 구현할 수 있듯이 SSIS로만 되는 것은 하나도 없습니다.
단지 SSIS를 이용하면 몸이 더 편할 수 있다라는
것이지요.
여러 업무들이 있을 것입니다. SSIS의 여러 기능들을 보고선 이를 구현하다가 편하다고 생각되거나 누가 편하다고 떠들면
고려해보면 되는 것이지요.
써 놓고 보니 아무 영양가도 없는 글인 것 같아 다시 읽어보기 민망하네요. 그냥 마치렵니다...^^
----------------------------------------------------------------------------
