Data Science Lab

데이터 흐름 경로

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [데이터 흐름 경로]에 대해서 알아 보도록 하겠습니다.

[데이터 흐름 경로]는 데이터 흐름 영역 내에서 변환, 또는 원본과 변환, 변환과 대상간의 연결을 시켜주는 경로 입니다. 데이터 흐름 경로는 특별한 설정은 없이 데이터 뷰어를 통하여 데이터가 변환되어가는 과정에서 열 유형이나 열 길이와 같은 메타 데이터를 확인 할 수 있습니다. 데이터 뷰어는 개발 또는 디버깅 단계에서 처리되는 데이터를 쉽게 확인 할 수 있는 기능만 제공 합니다.

데이터 흐름 경로에는 정상적인 변환 결과를 출력하는 경로(녹색)와 오류 정보를 출력하는 경로(적색)가 있습니다. 오류 출력을 추가한 경우 원본 또는 변환의 오류 구성 설정에서 오류가 발생할 열에 대해 행 리디렉션을 설정해야 합니다.

[BIDS]를 실행하여 [데이터 흐름]탭에 다음과 같이 데이터 흐름 작업을 생성 합니다.

데이트 흐름선에서 마우스 오른쪽을 클릭하여 [데이터 뷰어]를 선택 합니다.

[데이터 흐름 경로 편집기]가 나타납니다.

[일반] 탭에서는 PathAnnotation 항목에서 경로의 설명을 표시할 방법을 설정 합니다.

[메타데이터] 탭에서는 현재의 데이터 흐름 경로를 지나는 데이터의 유형이나 자릿수 등과 같은 메타 데이터를 확인 할 수 있습니다.

[데이터 뷰어] 탭에서는 데이터 흐름 경로를 지나는 데이터를 확인 할 수 있는 뷰어를 설정 할 수 있습니다. [데이터 뷰어]는 패키지를 개발하거나 디버깅 하는 단계에서만 사용되며 DTEXEC, DTEXECUI또는 SQL Server 에이전트 등을 이용하는 실제 운영 환경에서 수행될 때에는 작동하지 않습니다.

하나의 데이터 흐름 경로에는 여러 개의 데이터 뷰어를 설정 할 수 있습니다.

표 – 표의 형태로 전체 열을 출력하도록 설정, 선택한 열만 출력하도록 설정 가능.

히스토그램 – Xval 열에 대한 히스토그램을 설정

산점도(x,y) – XVal, YVal열에 대해 산점도를 설정

세로 막대형 차트 - YVal열에 대해 차트를 설정

데이터 뷰어를 통해 한 번에 출력되는 데이터의 양은 데이터 흐름 작업 영역의 속성 중 다음의 속성에 따라 달라 집니다.

DefaultBufferMaxRows - 설정된 값으로 나누어 출력

DefaultBufferSize – 버퍼 사이즈 만큼 데이터 출력

예를들어 [DefaultBufferMaxRows : 10000], [DefaultBufferSize : 10485760] 이라면 출력 행이 10000개씩 나누어 출력 됩니다. 또는 데이터 한 행의 크기가 커서 10000행 이전에 버서 사이즈 넘으면 버퍼 사이즈 만큼 출력 됩니다.

데이터 뷰어 상단에 있는 [▶] 버튼을 클릭하면 다음 처리 버퍼의 내용이 출력 됩니다.

[분리(D)] 버튼을 클릭하면 데이터 확인 작업을 종료하고 데이터 처리를 계속 진행 하게 됩니다.

[데이터 복사©]를 클릭하면 현재 데이터 뷰어 창에 나타나 데이터 또는 그래프에서 이용되는 데이터를 클립보드로 복사 합니다.

SSIS는 변환 작업 간의 데이터 유형을 엄격히 일치시키도록 관리 합니다. 데이터 흐름경로를 이용하여 변환과 변환, 변환과 대상 사이의 열 정보와 같은 메타 데이터를 쉽게 확인할 수 있습니다.

검사점 – 패키지 오류 위치를 확인하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com)

이번 시간에는 [검사점]에 대해서 알아보도록 하겠습니다.

[검사점]작업은 패키지가 실행 중 실패했을 때 오류가 발생한 작업 개체에 대한 정보와 정상적으로 처리된 작업들의 정보가 별도의 파일에 기록 됩니다. 이 기록파일에는 수행시점에서 적용된 변수의 값도 포함 됩니다.

검사점이 존재하는 경우에는 패키지가 다시 수행되어도 이미 수행한 작업들에 대해서는 다시 수행하지 않으며 오류가 발생한 작업부터 수행 합니다.

패키지의 모든 작업이 정상적으로 진행된 경우에는 검사점 파일이 생성되지 않습니다. 따라서 관리자는 작업 실패가 발생되었는지를 확인하기 위한 방법으로 검사섬 파일의 생성 여부를 확인하면 됩니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성합니다.

[제어 흐름] 탭에서 다음과 같이 [SQL 실행 태스크]를 끌어다 놓습니다.

[SQL 실행 태스크]에서 마우스 오른쪽을 클릭하여 [Connection]를 선택하고 [SQLStatement]에 쿼리를 입력 합니다. (실습에서는 "SELECT 1" 입력)

오류를 발생 시킬 작업에서 다음과 같이 임의로 오류를 발생 시키는 SQL 명령문을 입력 합니다.

(실습에서는 3번째 작업에서 실패 하도록 "SELECT 0/0" 입력)

검사점은 [제어 흐름]영역에서 설정 합니다. 검사점에서 관리하는 최소 작업 단위는 개별 작업(작업 호스트)단위 입니다. 트랜잭션이 설정된 컨테이너인 경우 해당 컨테이너가 최소 작업 개체가 됩니다.

• CheckPointFileName – 검사점 파일을 지정 합니다.
• CheckPointUsage – 검사점 사용 여부를 설정 합니다.
  • Never – 검사점을 사용하지 않습니다.
  • Always – 검사점 파일을 항상 사용합니다. 오류가 발생하지 않더라도 검사점 파일이 있어야 합니다.
  • IfExists – 검사점 파일이 있는 경우 해당 파일을 사용 합니다.

검사점 기능을 구현하기 위해서는 제어 흐름에 있는 각 작업의 [FailPakageOnFailure]속성이 [True]로 설정 되어 있어야 합니다.

검사점 설정이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지를 실행하면 세번째 작업에서(SELECT 0/0) 오류가 난 것을 확인 할 수 있습니다.

이 때 검사점에서 지정한 폴더를 확인하여 보면 검사점 파일(CheckPoint.chk)이 생성된 것을 확인 할 수 있습니다.

검사점 파일을 메모장을 이용하여 열어보면 다음과 같이 오류 작업과 패키지의 속성, 사용된 변수 등을 확인 할 수 있습니다.

이제 다시 3번째 작업을 정상적으로 실행 될 수 있도록 SQL 명령문을 수정 합니다.

그리고 다시 패키지를 실행합니다.

오류가 난 작업부터 패키지가 실행되는 것을 확인 할 수 있습니다.

검사점을 이용하면 오류가 난 위치를 정확하게 파악할 뿐만 아니라 오류가 난 작업에 대해서 재실행 할 때에도 관리자의 특별한 설정 없이 자동으로 검사점으로 확인하여 실행되므로 중복 실행을 방지 할 수 있습니다.

패키지 구성

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [패키지 구성]에 대해서 알아 보도록 하겠습니다.

SQL Server 2005 SSIS 부터는 패키지의 속성값을 설정할 수 있는 구성(Configurations)이라는 기능을 제공 합니다. 패키지에 포함되어 있는 컨테이너나 작업, 연결 등에 대한 속성 뿐만 아니라 패키지의 격리 수준이나 검사점 파일 등과 같은 패키지의 전반적인 속성에 대해서도 값을 설정할 수 있습니다.

동일한 패키지 작업을 여러 서버에서 수행하야 하거나 다수의 패키지에서 사용하는 연결 속성 등을 일괄 관리하고자 할 때 구성을 이용할 수 있습니다. 또한 부모 패키지에서 자식 패키지로 값을 지정해 주는 기능을 구현할 때에도 이용할 수 있습니다.

시스템 레지스트리의 항목을 읽어오거나 시스템 변수의 값을 사용해야 하는 경우에도 이용할 수 있습니다.

패키지 구성에서 설정된 값들은 실제 실행이 되는 시점에 적용 됩니다.(런타임 적용)

구성이 설정되었다고 하더라도 해당 구성 파일 또는 테이블 정보가 없는 경우에도 패키지는 정상적으로 실행 됩니다. 이러한 경우에는 구성 정보를 이용하여 속성값을 변경하는 대신 기존의 속성값을 사용하게 됩니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

BIDS 상단의 메뉴에서 [SSIS(S)] – [패키지 구성©]를 선택 합니다.

[패키지 구성 도우미] 창이 나타나면 추가를 선택 합니다.

다음과 같은 [패키지 마법사]가 실행 됩니다.

• XML 구성 파일 – XML 파일 형태로 구성 설정 값이 저장 됩니다. 하나의 XML 파일에 여러 개의 구성 설정 값이 저장될 수 있습니다. XML구성 파일은 상단의 제목 부분에는 구성 파일 자체에 대한 정보를 포함 하는 부분이며 파일을 만든 시간, 패키지 명, 패키지 ID등과 같은 값 등이 저장 됩니다. 하단의 내용은 구성으로 저장된 속성과 속성값이 저장되는 부분 입니다.

• 환경 변수 – 시스템의 환경 변수에 등록된 값을 패키지에서 이용하도록 설정 합니다. 시스템의 환경 변수 설정 부분에서 변수를 추가한 후 이를 SSIS의 패키지에서 사용하도록 지정할 수 있습니다. 시스템에서 설정된 환경 변수들은 패키지의 작업 폴더의 경로나 다른 속성의 값으로 이용하도록 기본적으로 제공되는 환경 변수를 지정할 수 있습니다.

• 레지스트리 항목 – 구성에서 사용할 항목의 값을 레지스트리에 저장한 후 이를 사용할 수 있습니다. 또한 환경 변수와 마찬가지로 시스템에 저장되어 있는 레지스트리 항목을 SSIS패키지에서 사용할 수 있습니다. 이 때 읽어오거나 지정할 수 있는 레지스트리는 HKEY_CURRENT_USER하위에 있는 키 값 이어야 합니다.

• 부모 패키지 변수 – 부모 패키지에서 자식 패키지를 호출 할 때 자식 패키지에 있는 변수의 값 또는 개체의 속성 값을 지정할 수 있습니다. 자식 패키지에서는 값을 가져올 부모 패키지의 변수 이름을 지정해 줍니다. 부모 패키지 변수가 설정된 자식 패키지를 부모 패키지에서 호출 할 때 특별한 설정 없이도 부모 패키지에 해당 변수가 있을 때 이 변수에 저장된 값이 자식 패키지로 전달되어 실행 됩니다.

• SQL Server – SQL Server에 테이블 형태로 구성 정보를 저장하여 이용할 수 있습니다.

프로세스 실행 태스크

안녕하세요 강성욱 입니다. (www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [프로세스 실행 태스크]에 대해서 알아 보도록 하겠습니다.

[프로세스 실행 작업]은 데이터 처리 프로세스 내에서 윈도우 어플리케이션 또는 콘솔 어플리케이션을 호출하는 작업 개체 입니다. 프로그램에 따라서 매개변수가 필요하거나 실행결과 정보를 변수에 저장할 수도 있습니다.

이번 실습에서는 [프로세스 실행 작업] 개체와 [7zip] 어플리케이션을 이용하여 특정 파일을 압축하는 방법을 알아 보도록 하겠습니다.

(실습을 위해서 반드시 7Zip을 사용할 필요는 없습니다.)

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [프로세스 실행 태스크]를 드래그 앤 드롭으로 끌어다 놓습니다.

[프로세스 실행 태스크]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[프로세스 실행 태스크 편집기] 창이 나타납니다.

[일반]탭에서는 태스크의 이름을 변경 할 수 있습니다.

[프로세스] 탭에서는 다양한 설정을 할 수 있습니다.

실습에서는 7zip을 이용한 파일 압축 이기에 위의 그림과 같이 해당 경로 및 매개변수를 지정 합니다.

[Executable]

[Arguments]

[각 항목 설명]

• RequireFullname – 프로그램을 실행할 때 전체 경로가 필요한지 설정, TURE일대 해당 경로에 파일이 없으면 실패를 반환. 또한 OS PATH(notepad.exe, mspaint.exe)는 경로를 지정하지 않아도 수행 가능.

• Ececutable – 실행 할 프로그램.
• Arguments – 프로그램 실행시 필요한 입력 변수 지정.
• WorkingDirectory – 프로그램이 실행될 작업 폴더를 지정. 특정 폴더에서 해당 프로그램이 실행하도록 할 경우 이 속성 값을 지정.
• StandardInputVariable – 프로그램에 입력 변수로 전달할 값이 저장된 SSIS 변수를 지정. Arguments에 직접 쓰는 대신 SSIS의 문자형 변수에 입력 변수로 사용할 값을 저장한 후 이 변수를 지정합니다.
• StandardOutputVariable – 프로그램이 수행된 후 반환되는 결과값을 저장할 변수를 지정합니다.
• StandardErrorVariable – 프로그램 수행 중 발생된 오류 정보를 변수에 저장하도록 설정 합니다.
• FailTaskIfReturnCodeIsNotSuccessValue – 프로그램이 종료된 후 출력되는 결과값이 SuccessValue에서 정한 값과 다를 경우 실패로 처리할지 설정. 일반적인 종료시 0이 출력됩니다.
• SuccessValue – 성공으로 판단할 값을 지정. 디폴트 0 으로 설정 되어 있습니다.
• TimeOut – 프로그램이 시행 될 수 있는 시간(초)를 지정 합니다. 0 으로 지정하면 시간 제한 없이 프로그램이 완료 되거나 오류가 발생 할 때 까지 계속 수행하게 됩니다.
• TerminateProcessAfterTimeOut – TimeOut 속성에 지정한 제한 시간이 지난 경우 프로그램을 강제 종료 할지 설정 합니다. TimeOut이 0 아닌 경우에만 사용 가능 합니다.
• WindowsStyle – 프로그램이 실행될 때의 창의 모습을 지정 합니다.

[식] 탭에서는 Arguments에 사용할 값을 지정 할 수 있습니다.

변수에는 단 하나의 식만 정의할 수 있지만 작업 개체나 컨텐이너, 연결 등과 같이 여러 속성을 가지는 개체에서는 여러 개의 식을 정의하여 사용할 수도 있습니다.

변수 관련 참고 링크

http://blog.naver.com/jevida/140160932965

[식 작성기]에서 식을 작성할 때에는 시스템 변수, 사용자 변수를 사용할 수 있으며 각종 함수 및 연사자 등을 이용할 수있습니다.

(이번 실습에서는 [식] 탭의 설정은 따로 하지 않습니다.)

[프로세스 실행 태스크]에서 설정한 폴더의 위치에 1.TXT파일을 생성 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지를 실행하면 해당 경로 폴더에 Result.Zip라는 이름으로 압축 파일이 생성된 것을 확인 할 수 있습니다.

아래 그림은 큰 용량의 파일(4.5G)를 압축하는 그림입니다.

압축 프로그램이 실행되는 것을 확인 할 수 있습니다.

[프로세스 실행 태스크]를 이용하면 일일 통계나 기타 정보를 BCP 작업을 이용하여 텍스트 파일로 저장하여 이를 압축하여 다른 서버 및 메일로 전송 태스크과 함께 유용하게 사용 할 듯 합니다.

변수 – 다양한 사용자 변수를 만들어 보자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [변수]에 대해서 알아 보도록 하겠습니다.

지금까지 [데이터 흐름]에 대한 강좌를 하면서 중간 중간에 변수 사용에 대한 실습을 진행하였습니다. 이번 시간에는 변수에 대해서 좀더 자세히 알아보고 각 항목에 대한 특성을 알아 보도로 합니다.

SSIS에는 크게 두 가지 유형의 변수가 있습니다.

• 시스템 변수 – 패키지 생성일자, 패키지 GUID, Version GUID, Machine Name 등 패키지 수행 환경에 대한 정보가 저장되는 변수.

• 사용자 변수 – 패키지 내에서 작업을 수행할 때 사용되는 변수로 사용자가 필요에 따라 추가하며 값을 변경 할 수 있음

SSIS의 변수 범위는 컨테이너 개체를 기준으로 범위가 결정 됩니다. 작업 개체 역시 하나의 작업 호스트 컨테이너이기 때문에 변수의 범위로 정의 될 수 있습니다.

사용자 변수를 추가하는 방법을 알아 보도로 하겠습니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[변수] 탭을 실행하는 방법은 두 가지가 있습니다.

[보기] – [다른 창] –[변수]

또는 빈 공간에서 마우스 오른쪽을 클릭하여 [변수]를 선택 합니다.

[변수]탭을 활성화 시키면 다음 그림과 같이 [변수]탭이 나타나고 변수를 생성 할 수 있는 메뉴가 나타 납니다.

변수를 추가한다

변수를 삭제 한다

시스템 변수 목록 보기

현재 패키지에 정의된 모든 사용자 변수 보기

변수 열 선택

변수의 범위는 변수를 추가할 때 지정할 수 있는 것이 아니며 변수 추가 할 때 선택되어 있는 컨테이너가 자동으로 변수 범위를 설정 합니다.

아래 그림은 각 컨테이너 별로 변수를 생성한 화면 입니다.

컨테이너에 따라 범위가 자동으로 지정되는 것을 확인 할 수 있습니다.

만약 상위 범위의 변수를 추가하고 싶으면 상위 영역으로 이동하여 변수를 추가 합니다.

[제어 흐름 영역], [데이터 흐름 영역], [이벤트 처리기 작업 영역] 에서의 시스템 변수는 조금 차이가 있습니다.

[패키지 수준의 시스템 변수]

[컨테이너의 시스템 변수]

[작업 수준의 시스템 변수]

[이벤트 처리기의 시스템 변수]

스크립트 구성 요소 – [데이터 대상] 사용

안녕하세요 강성욱 입니다. (www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [스크립트 구성 요소] 사용에 대해서 알아 보도록 하겠습니다.

3부 – 데이터 대상으로 사용하기!

1부 – [스크립트 구성 요소] –[데이터 원본] 관련 링크

http://blog.naver.com/jevida/140160648324

2부 – [스크립트 구성 요소]-[데이터 변환] 관련 링크

http://blog.naver.com/jevida/140160719291

이번 [데이터 대상]은 입력 데이터 중에서 [OrderQty] 의 [합계]를 [메시지 박스]로 출력하여 봅니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[스크립트 구성 요소 3] 이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [스크립트 구성 요소]를 끌어다 놓습니다. 이때 팝업으로 [스크립트 구성 요소 유형 선택]창이 나타 납니다.

이번 실습에서는 [대상]으로 선택 합니다.

[OLE DB 원본]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Sales.SalesOrderDetail 테이블 사용)

[제어 흐름] 탭으로 이동하여 빈 공간에서 마우스 오른쪽을 클릭하여 [변수]를 선택 합니다.

[변수] 탭이 나타나면 변수명(SumQty)을 설정하고 범위를 [Package]로 지정한 다음 데이터 형식을 [Int32]로 지정 합니다.

[데이터 흐름] 탭으로 이동하여 [스크립트 구성 요소]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[입력 열] 탭에서 [OrderQty]를 선택 합니다.

[입/출력] 탭에서 입력 이름을 [In_Qty]로 변경 합니다. (한글 사용의 경우 글자 깨짐으로 인하여 인식 불능의 문제가 있습니다.)

[스크립트] 탭에서는 [ReadWriteVariavles]에서 패키지 차원의 변수로 지정한 [User::SumQty]를 선택 합니다. 그리고 [스크립트] 버튼을 이용하요 스크립트 편집기를 실행 합니다.

스크립트 편집기에서 다음과 같이 코드를 입력 합니다.

[제어 흐름]탭으로 이동하여 [스크립트 태스크] 작업을 드래그 앤 드롭으로 끌어다 놓습니다.

[스크립트 태스크]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[스크립트] 탭에서 [ReadWriteVariables]에서 [User::SumQty] 변수를 선택 합니다. 그리고 [스크립트 편집]을 실행합니다.

다음과 같이 메시지 박스를 출력하는 코드를 프로그래밍 합니다.

편집이 완료되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지를 실행하면 메시지 박스로 [OrderQty]의 총 합계가 출력되는 것을 확인 할 수 있습니다.

SSMS를 실행하여 실제 Qty의 합과 같은지 확인 합니다.

SSMS에서 실행한 합과 메시지 박스에 출력된 합이 같음을 알 수 있습니다.

스크립트 구성 요소 – [데이터 변환] 사용

안녕하세요 강성욱 입니다. (www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이버 시간에는 [스크립트 구성 요소] 사용에 대해서 알아 보도록 하겠습니다.

2부 – 데이터 변환으로 사용하기!

[데이터 변환]은 입력 데이터에 대하여 복잡한 연산을 수행하여 출력 합니다.

[스크립트 구성 요소] –[데이터 원본] 사용 관련 링크

http://blog.naver.com/jevida/140160648324

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[스크립트 구성요소 2] 이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름] 탭에서 그림과 같이 [스크립트 구성 요소]를 끌어다 놓습니다. 이때 팝업으로 [스크립트 구성 요소 유형 선택]창이 나타 납니다.

이번 실습에서는 [변환]으로 선택 합니다.

[레코드 집합 대상]을 드래그 앤 드롭으로 끌어다 놓은 후 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Sales.SalesOrderDetail 테이블 사용)

[스크립트 구성 요소]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[스크립트 변환 편집기] – [입력 열] 탭에서 아래 그림처럼 입력 열을 선택 합니다.

[입/출력] 탭에서는 [열 추가]버튼을 이용해서 [출력 열]을 생성 합니다.

[스크립트]탭에서 [스크립트 편집] 버튼을 클릭 합니다.

새로운 VS 창이 나타나면 데이터 변환 코드를 프로그래밍 합니다.

이번 실습에서는 Qty(수량)과 가격(UnitPrice)값을 곱한 Total_Price 값을 계산해 보도록 합니다.

프로그래밍이 완료 되었으면 VS 창을 닫고 [데이터 흐름 태스크]의 빈 공간에서 마우스 오른쪽을 클릭하여 [변수]를 선택 합니다.

그리고 아래 그림처럼 변수를 생성합니다.

변수의 데이터 형식은 Object로 설정 합니다.

[레코드 집합 대상]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[레코드 집합 대상 편집기] 창이 나타 납니다.

[구성 요소 속성] 탭에서 [사용자 지정 속성]의 [VariableName] 값을 앞에서 생성하였던 변수[User::TempResult]로 선택 합니다.

[입력 열] 탭에서 필요한 입력 열을 선택 합니다.

데이처가 처리되는 과정을 확인하기 위해 중간 경로에 [데이터 뷰어]를 추가 합니다.

[스크립트 구성 요소]와 [레코드 집합 대상]간의 녹색 경로에 마우스 오른쪽을 클릭하여 [데이터 뷰어]를 선택 합니다.

[데이터 흐름 경로 편집기]창이 나타나면 [데이터 뷰어]탭에서 [표]로 선택 합니다.

[구성] 버튼을 이용하여 [데이터 뷰어]에 나타낼 컬럼을 선택 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

[데이터 뷰어]에 [스크립트 구성 요소]에서 변환한 Total_Price값을 확인 할 수 있습니다.

스크립트 구성 요소 – [데이터 원본] 사용

안녕하세요 강성욱 입니다. (www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [스크립트 구성 요소]에 대해서 알아 보도록 하겠습니다.

1탄 - 데이터 원본으로 이용하기!

[스크립트 구성 요소]는 다양한 기능을 수행 할 수 있는 개체 입니다. [제어 흐름]에도 [스크립트 작업]이 있지만 [스트립트 구성 요소]는 원본 데이터를 변경하는 데이터 흐름과 관련된 전문적인 작업을 수행하는 개체 입니다

(1) 스크립트 구성 요소가 [데이터 원본]으로 사용 할 수 있는 경우.

• 입력 데이터가 플랫파일 연결이나 OLE DB 연결 등과 같이 간단한 설정을 할 수 없는 경우.
• 기본적으로 제공하는 데이터 원본 유형 이외의 형태 – SQL 2005 SSIS 이후에는 ODBC연결을 지원하지 않아 스크립트 구성요소를 이용하여 다양한 프로그래밍으로 연결이 가능합니다.
• 임의의 테스트 데이터를 발생 시키는 경우

(2) 스크립트 구성 요소가 [데이터 변환]으로 사용 할 수 있는 경우

• 입력 데이터에 대해 복잡한 변환 수행
• 입력 데이터를 이용한 연산 수행 – 입력 데이터에 대한 복잡한 산술연산 작업이 가능 합니다.
• 입력 데이터에 열 추가 – 각 행 번호 또는 계산된 결과 열을 추가 할 수 있습니다.
• 입력 데이터를 이용한 유효성 판단 – 예를들어 입력데이터가 NULL인경우 판단하지 않고 다음 행으로 넘어가도록 설정 할 수 있습니다.

(3) 스크립트 구성 요소가 [데이터 대상]으로 사용 할 수 있는 경우.

• 기본적으로 제공하는 데이터 대상 유형 이외의 형태 - ODBC연결과 같이 기존의 SSIS에서 제공하지 않는 데이터 대상 유형을 구현 할 수 있습니다.
• 처리되는 데이터를 이용하여 다양한 작업을 수행 – 처리된 데이터를 입력 받은 후 이를 이용하여 SQL 쿼리 파일을 만들거나 웹 보고서를 생성하는 등 결과 데이터를 이용한 다양한 작업이 가능 합니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이트 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[스크립트 구성 요소] 이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름] 탭에서 그림과 같이 [스크립트 구성 요소]를 끌어다 놓습니다. 이때 팝업으로 [스크립트 구성 요소 유형 선택]창이 나타 납니다.

이번 실습에서는 [원본]으로 선택 합니다.

[레코드 집합 대상]을 드래그앤 드롭으로 끌어다 놓은 후 데이터 흐름선을 연결 합니다.

[스크립트 구성 요소] 에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[스크립트 변환 편집기]가 실행 됩니다. 편집기에는 3개의 탭을 확인 할 수 있습니다.

[입/출력] 탭에서는 입,출력 속성을 설정 할 수 있습니다.

입/출력 이름을 [OutResult]로 변경합니다. (한글 사용시 VSA에서 깨짐 현상 발생.) 트리를 확장하여 [출력 열]에서 [열 추가] 버튼을 이용하여 출력열을 추가 합니다.

이번 실습에서는 [1-1000 사이의 난수 발생 결과]를 출력합니다. 따라서 Num 출력 열에 난수가 나타납니다.

[스크립트] 탭에서는 해당 스크립트의 [언어] 및 스크립트 편집을 할 수 있습니다.

저는 VS C# 2008 을 사용하기에 해당 언어를 선택하고 [스크립트 편집] 버튼을 클릭 합니다.

[스크립트 편집]을 실행하면 새로운 VS 창이 나타 납니다. 여기에서 스크립트를 해당 언어에 맞게 프로그래밍 하면 됩니다.

이번 실습에서는 1-1000 사이의 난수 발생을 출력하기에 해당 코드를 아래 그림처럼 입력 합니다.

[스크립트] 편집이 완료 되었으면 해당 창을 닫고 [데이터 흐름] 탭으로 이동 합니다.

그리고 빈 화면에서 마우스 오른쪽을 클릭하여 [변수]를 실행 합니다.

[변수]탭이 나타나면 아래 그림과 같이 변수를 생성하고 [Num]이라는 변수명을 지정 합니다.

데이터 형식은 [Object]를 선택 합니다. 이는 데이터 대상인 [레코드 집합 대상]에서 이용할 변수 입니다.

[레코드 집합 대상]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[고급 레코드 집합 데상 편집기]창이 나타 납니다.

[구성 요소 속성] 탭에서 [사용자 지정 속성]의 [VariableName] 값을 앞에서 생성하였던 사용자 변수 [User::Num]로 선택 합니다.

[입력 열] 탭에서는 해당 변수에 출력할 열을 선택 합니다.

[입/출력 속성] 탭에서는 여러가지 속성을 확인 및 설정 할 수 있습니다. [입력 열]탭에서 나열되는 열은 별도로 선택할 필요는 없습니다.

데이터가 처리되는 과정을 확인하기 위해 중간 경로에 [데이터 뷰어]를 추가 합니다.

[스크립트 구성 요소]와 [레코드 집합 대상]간의 녹색 경로에서 마우스 오른쪽을 클릭하여 [데이터 뷰어]를 선택 합니다.

아래 그림과 같이 [데이터 흐름 경로]편집기 창이 나타납니다.

[데이터 뷰어] 탭에서 어떤 형태로 나타낼 것인지 선택 합니다. 이번 실습에서는 [표]형태로 나타 냅니다.

편집기 선택이 완료되면 다음과 같은 화면이 나타납니다.

[데이터 뷰어] 생성이 완료되면 흐름선 옆에 작은 아이콘 모양이 나타난 것을 확인 할 수 있습니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

[데이터 뷰어]에 [스크립트 구성 요소]에서 발생한 난수를 출력한 결과를 확인 할 수 있습니다.

피봇 해제 변환 – 피봇된 데이터를 테이블 형태로 변환

안녕하세요 강성욱 입니다. (www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [피봇 해제 변환]에 대해서 알아 보도록 하겠습니다.

[피봇 해제 변환]은 피봇 변환과는 반대로 피봇 형태를 일반 테이블 형태로 출력하는 변환 작업 입니다.

[피봇 변환] 관련 링크

http://blog.naver.com/jevida/140160394207

[SSMS]를 실행하여 오늘 실습에 사용할 데이터를 생성 합니다.

지난 [피봇 변환]에서 성별에 대하여 피봇한 내용을 다시 테이블 형태로 출력하도록 합니다.

[BIDS]를 실행 하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[피벗 해제 변환] 이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름] 탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [피벗 해제]작업을 추가하여 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

( 본 실습에서는 위에서 생성한 예제 데이터를 이용 합니다.)

[피벗 해제]작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

아래 그림과 같이 [피벗 해제 변환 편집기] 창이 나타 납니다.

사용 가능한 입력열을 선택하고 이때 피벗에 사용할 열과 해제 열을 선택 합니다.

• 대상 열(1) - 값으로 출력될 열의 이름을 지정 합니다.
• 피벗키 값(2) – 피벗에서 열 속성의 열 이름을 지정 합니다.
• 피벗 키 값 이름(3) – 피벗키 값(2)의 값들을 나타낼 열의 이름을 지정 합니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 이름 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 클릭하면 피봇해제 된 형태의 테이블 컬럼이 생성 됩니다.

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패지키를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 정상적으로 피봇 해제가 완료 되었는지 확인 합니다.

데이터를 확인하면 기존 성별 컬럼이 행으로 바뀌고 Cash컬럼이 생성되어 값이 입력된 것을 확인 할 수 있습니다.

이 작업은 피봇된 형태의 데이터를 언피봇하여 데이터를 정렬할 때 매우 유용하게 사용 될 듯 합니다. 통계 작업에서 피봇을 많이 사용하지만 집계된 데이터를 다시 언피봇 해야할 때 사용 하여 봅니다.

피봇 변환 – 데이터 행렬 변환

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [피벗 변환]에 대해서 알아 보도록 하겠습니다.

SSIS에서 [피벗 변환]은 테이블 데이터 뿐만 아니라 다양한 데이터 형태의 입력 데이터데 대해서도 피벗 변환을 수행 할 수 있습니다.

[SSMS]를 실행하여 오늘 실습에 사용할 데이터를 생성 합니다.

오늘 실습은 성별에 대하여 피봇을 진행 하도록 합니다.

[BIDS를 실행]하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[피벗 변환] 이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [피벗]작업을 추가하여 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면[OLE DB연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 위에서 생성한 예제 데이터를 이용합니다.)

[피벗] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다,

아래 그림과 같이 [고급 피벗 편집기]창이 나타납니다.

[구성 요소 속성]탭에서는 피벗의 이름 및 LocalID를 지정할 수 있습니다. 또한 유효성 체크 유무를 변경 할 수 있습니다.

[입력열] 탭에서는 피벗에 사용할 입력 열을 선택 합니다.

[입/출력] 속성 탭에서는 입력 받은 데이터를 피벗 데이터로 출력할 것인지 설정 합니다.

이때 주의 사항이 아래 그림처럼 입력 열에 대한 [LeneageID]값이 매핑 값이 됩니다.(중요함)

또한 [PivotUsage]값에 따라 행 및 열, 값 속성으로 분류 됩니다.

실습에서는

ID : 0

Gender : 2

Age : 1

Cash : 3

으로 PivotUsage를 지정 하였습니다.

[출력 열]에서는 [열 추가] 버튼을 이용하여 [출력 열]을 생성 합니다.

여기서 주의할 점이 출력 열에서 나타낼 데이터의 입력열을 지정해 주어야 하는데 이때 사용하는 것이 위에서 설명한 [LeneageID]값 입니다. [SourceColumn]값에 입력 열의 [LeneageID]을 입력하면 [DataType] 타입 또한 입력열의 데이터 타입으로 자동으로 변경 됩니다.

오늘 실습에서는 성별에 대한 피봇을 진행 합니다. 여기서도 주의할 점이 값에 대한(PivoUsage : 3) [LeneageID]를 입력해야 합니다. 그리고 필터 조건으로 [PivotKeyValue] 항목에 (PivotUsage : 2) 성별에 대한 값을 입력해 주어야 합니다.

• ComprasionFlags – 그룹핑 작업을 수행 할 때 문자열에 대한 비교 처리 방법, 대소문자 구분이나 문자 너비, 기호 무시등의 문자열 비교 속성을 설정할 수 있습니다.
• PivotKeyValue – SQL 쿼리에서 [CASE WHEN 컬럼명 = '조건값' THEN…] 형태에서 '조건 값'에 해당하는 값을 지정합니다.
• SourceColumn – 연산을 수행할 값의 [LeneageID]값 입니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 피봇 데이터 형태의 컬럼이 생성 됩니다.

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 데이터를 확인 합니다.

데이터를 확인하면 성별에 따라 Cash 값을 피봇된 것을 확인 할 수 있습니다.

이 작업은 SSIS를 이용하여 여러곳에서 데이터를 가져올 때 피벗 작업을 진행하여 데이터를 적재할 때 매우 유용할 듯 합니다. (통계 쿼리 대신 사용할까 고민 중입니다.)

조회 변환 – 참조 테이블을 이용하여 변환하기

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [조회 변환]에 대해서 알아 보도록 하겠습니다.

[조회 변환]은 입력 데이터에 대해 코드 테이블 또는 디멘전 테이블과 같은 참조 테이블을 조회할 때 사용하는 변환 입니다.

조회 변환은 조인 연산(INNER JOIN 또는 OUTER JOIN)과 유사 합니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[조회 변환]이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [조회]작업을 추가하여 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Person.Address 테이블 사용)

[조회]작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

편집기 창에는 다양한 설정을 지정 할 수 있습니다.

[일반]탭에서는 캐시 및 연결 모드를 선택 할 수 있습니다.

[캐시 모드]에서 [전체 캐시]를 선택하면 참조 테이블 전체를 메모리에 캐싱합니다. 이는 작업전 모든 데이터를 메모리로 로딩하는 작업이 수행 됩니다. [캐시 없음]을 선택하면 캐싱 작업을 하지 않습니다.

[연결 유형]에서는 OLE DB연결 관리자와 캐시 연결 관리자를 선택 할 수 있습니다.

[연결]탭에서는 OLE DB연결을 이용하여 조회 테이블 또는 조회에 이용할 쿼리를 지정합니다.

테이블을 지정할 수 있지만 [SQL 쿼리 결과 사용]을 선택하면 직접 쿼리문을 사용하여 성능상 유리 합니다.

(본 실습에서는 테이블을 선택 하도록 합니다. Adventureworks 의 [Person.StateProvince] 테이블 사용)

[열]탭에서는 입력열과 참조열을 매핑합니다. 마우스 오른쪽을 클릭하여 [매핑 편집] 메뉴를 실행 합니다.

[Stateprovince]로 매핑을 연결하고 사용가능한 조회열에서 [Name]을 선택 합니다.

조회작업에서는 AddressLine2로 출력을 지정합니다.

[고급] 탭에서는 사용자가 쿼리를 수정 할 수 있습니다. [매개변수] 버튼을 이용하여 매개 변수를 설정 할 수 있습니다.

[오루 출력] 탭에서는 오류 내역을 지정합니다.

[조회]작업에서 [대상 데이터] 작업으로 흐름선을 연결 합니다.

흐름선을 연결하면 아래 그림과 같이 [입/출력 선택] 창이 나타납니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 출력 테이블이 생성 됩니다.

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 데이터를 확인 합니다.

데이터를 확인해 보면 StateProvince코드가 Name에서 Washington을 나타냄을 확인 할 수 있습니다.

[대상 테이블]을 보면 AddressLine2컬럼에 Name 내용이 변환되어 도시 이름이 나타난 것을 확인 할 수 있습니.

유사 항목 그룹화 – 입력 데이터 중 유사한 항목을 그룹핑하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [유사 항목 그룹화]에 대해서 알아 보도록 하겠습니다.

[유사 항목 조회]는 입력 데이터에 대해 참조 테이블의 데이터와 비교하여 유사하다고 판단되는 항목을 출력하는 작업인 반면 [유사 항목 그룹화]는 입력 데이터들 중 서로 유사하다고 판단되는 항목들로 그룹화 시키는 작업 입니다.

[유사 항목 조회] 관련 링크

http://blog.naver.com/jevida/140159740992

유사성 임계값에 따라 분류되는 그룹의 수는 달라 집니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[유사 항목 그룹화]이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [유사 항목 그룹화]작업을 추가하여 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Person.Address 테이블 사용)

[유사 항목 그룹화]작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

아래 그림과 같이 [유사 항목 그룹화 변환 편집기]창이 나타납니다.

[연결 관리자] 탭에서 [OLE DB 연결 관리자] 항목에서 유사 항목 그룹화 작업을 수행 할 때 필요한 임지 저장 테이블의 연결을 지정 합니다. 이때 생성되는 임시 테이블은 입력 데이터를 토큰화 하여 저장하는 인덱스 입니다. 변환 과정에서 자동으로 임시테이블을 생성하고 변환작업을 하므로 운영 DB대신 별도의 임시DB를 사용하는 것이 좋습니다.

[열] 탭에서는 유사 항목 그룹화를 수행할 열을 지정하고 세부 설정을 지정할 수 있습니다.

세부 설정은 [유사 항목 조회]의 설정과 비슷 합니다.

[숫자]는 입력 데이터에서 숫자에 대한 처리 방식을 지정 합니다.

• Neither – 앞부분 및 뒷부분의 숫자 모두 분류작업에 특별한 의미가 없음.
• Leading – 입력 데이터의 앞부분에 나타나는 숫자만 의미가 있음.
• Trailing – 입력 데이터의 뒷부분에 나타나는 숫자만 의미가 있음.
• LeadingAndTrailing – 입력 데이터의 앞부분 및 뒷부분의 숫자 모두 의미가 있음.

[비교 플래그]는 문자열 데이터를 비교할 때 옵션을 설정 합니다.

[고급]탭에서는 임계값 및 토큰값을 설정 할 수 있습니다.

임계값이 높을수록 그룹화 되는 조건이 엄격해 지며 낮을수록 그룹화 되는 수가 작아 집니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [입력 키열 이름] 과 [출력 키열 이름], [SCORE], 클린젱과 유성 컬럼이 추가 됩니다.

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 데이터를 확인 합니다.

데이터를 확인해 보면 [_Key_in]은 순차적으로 부여되는 입력 키 값이며 [_key_out]의 숫자가 [_key_in]의 값과 그룹핑 되었다는 것을 의미 합니다.

[InputData]의 값은 입력된 데이터를 나타내며 [InputData_Clean]은 그룹회 되어 분류되는 값을 나타 냅니다.

[_Simiarity_InputData]는 InputData열에 대한 유사성을 나타냅니다.

유사 항목 조회 – 데이터 클렌징 하기

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [유사 항목 조회]에 대해서 알아 보도록 하겠습니다.

[유사 항목 조회] SQL Server 2012의 DQS 서비스의 일부 기능 처럼 표준화 되지 않은 데이터에 대해 기준 데이터와 가장 유사한 값을 조회하고 조회된 데이터와의 유사도, 신뢰도를 판단해 주는 작업을 합니다.

[유사 항목 조회 변환]은 [용어 추출]과 비슷합니다. [용어 추출]은 [참조 테이블]의 비교 항목과 일치하는 경우에만 출력하지만 [유사 항목 조회] 변환은 정확히 일치 하지 않더라도 유사하다고 판단될 경우 데이터를 출력 합니다.

[용어 추출] 관련 링크

http://blog.naver.com/jevida/140159227758

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[유사 항목 조회]이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [유사 항목 조회]작업을 추가하여 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Person.Address 테이블 사용)

[유사 항목 조회]작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

아래 그림과 같이 [유사 항목 조회 변환 편집기]가 실행 됩니다.

[참조 테이블]에 데이터 원본을 지정하고 [참조 테이블]을 지정 합니다.

[유사 조회 변환]에서 사용할 참조 테이블을 생성합니다. SSMS를 실행하여 아래 쿼리를 이용하여참조 테이블을 생성합니다.

다시 편집기 창으로 돌아와서 [참조 테이블]탭에서 앞서 만들었던 참조 테이블 [MasterData]를 선택 합니다.

• 참조 테이블 – 조회 대상이 되는 메타 테이블.
• 새 인덱스 저장 – 참조 테이블이 존재하는 데이터베이스에 지정한 이름의 인덱스가 생성됨.
• 저장된 인덱스 유지 관리 – 참조 테이블에 인덱스의 정보를 관리할 트리거가 생성됨. 입력 데이터가 자주 변경되며 유사 항목 조회 작업을 반복해서 수행할 경우 이 옵션을 이용하여 인덱스 테이블을 효과적으로 관리 가능.
• 기존 인덱스 사용 – 이미 생성되어 있는 인덱스 사용

[열] 탭에서는 입력 데이터와 조회 데이터간의 매핑을 설정 합니다.

빈 화면에서 마우스 오른쪽을 클릭하여 [매핑 편집]을 실행 합니다.

아래 그림과 같이 [관계 만들기] 창이 나타 납니다.

[입력 열]과 [조회 열(참조 테이블 열)]을 선택합니다.

[매핑 유형]에는 두 가지가 유형이 있습니다.

• Fuzzy –유사 조회를 수행 하는 것.
• Exact – 정확히 일치하는 조회를 수행하도록 하는 것. 모든 비교열이 Exact일 때에는 조회 변환과 동일하게 수행 됩니다.

[비교 플래그] 에서는 문자표 변환 또는 집계 변환 등에서와 같이 문자열 비교 작업에 대한 속성을 설정 합니다.

• 문자표 변환 참조 링크

http://blog.naver.com/jevida/140158797722

• 집계 변환 참조 링크

http://blog.naver.com/jevida/140158945633

[최소 유사성]에는 해당 열에 대한 매핑 작업 수행 시 매핑을 수행할 최소의 유사성을 지정 합니다. 예를들어 유사성이 0.80으로 설정한다면 유사성이 0.80 이상인 경우에만 매핑이 이루어 집니다.

[유사성 출력 별칭]은 유사성을 출력할 열의 이름을 설정 합니다.

[고급] 탭에서는 일치 하는 최대 항목 수 임계값 등을 상세히 설정 할 수 있다.

• 조회당 출력에서 일치하는 최대 항목 수 – 입력 데이터에 대해 유사 조회 연산을 수행 한 후 출력할 수 있는 최대 항목 수를 지정 합니다. 이 값을 1로 설정할 경우 유사한 항목으로 판단되는 데이터들 중에서 가장 유사성이 높은 항목 1개만 출력 됩니다.
• 유사성 임계값 – 변환 전체의 유사성에 대한 임계값을 설정 합니다. [열 탭]에서의 [최소 유사성]과 비슷한 기능이지만 [전체 열의 평균값]을 이용하여 판단 하는 점이 다릅니다.
• 토큰 구분 기호 – 입력 데이터를 비교 가능한 개별 토큰으로 구분 할 때 사용되는 구분 기호를 지정하는 부분 입니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [참조 열]에 대한 컬럼과 [유사성], [임계값], [유사 데이터] 컬럼이 생성됨을 확인 할 수 있습니다.

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 데이터를 확인 합니다.

데이터를 확인해 보면 유사성, 임계값 등을 확인 할 수 있습니다.

[유사 항목 조회] 작업은 우편 주소나 정제되지 않은 데이터 집계 또는 정제시 매우 유용하게 사용될 듯 합니다.

SQL Server 2012에는 DQS기능이 새롭게 추가 되었습니다.

행 개수 – 작업 행의 개수를 출력하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [행 개수]에 대해서 알아 보도록 하겠습니다.

행 개수 작업은 SSIS작업시 단순히 데이터 흐름 경로 상에서 통과하는(=처리되는) 행 수를 사용자가 지정한 변수에 저장하는 역할을 수행 합니다.

따라서 아무런 변화 작업이 없기에 입력된 데이터와 출력된 데이터는 동일 합니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름]탭에서 [데이터 흐름 태스크]를 드래그앤 드롭으로 추가 합니다.

[행개수]이름을 지정하여 태스크를 생성합니다.

그리고 [스크립트 태스크]를 드래그앤 드롭으로 추가하여 흐름선을 연결 합니다.

[제어 흐름]탭에서 마우스 오른쪽을 클릭하여 [변수]를 선택합니다. 아래 그림처럼 변수 탭이 나타나면 행 수를 저장할 변수를 선언 합니다. (일반적으로 데이터 흐름 작업 내에서 행수를 변수에 저장한 후 제어 흐름 영역이나 다른 데이터 흐름에서 사용하게 되므로 변수 영역을 작업 영역 상위로 지정합니다.)

[Row_Cnt]라는 변수명을 지정하고 데이터 타입은 [Int32]를 지정 합니다.

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [행 개수]작업을 추가하여 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks 의 Person.Address 테이블 사용)

[행 개수]작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

아래 그림과 같이 [고급 행 개수 편집기]가 실행 됩니다.

[사용자 지정 속성]에서 처음에 만들었던 사용자 변수를 선택 합니다.

[입력 열] 탭에서는 특별히 지정하지 않아도 됩니다.

[입/출력 속성] 탭 또한 특별한 작업없이 기본을 사용하도록 합니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다.(이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [원본 데이터]와 동일한 컬럼이 생성됨을 확인 할 수 있습니다.

(행개수 작업은 데이터를 변환하지 않는 작업이므로 입력 데이터와 출력데이터 동일)

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 [제어 흐름]탭으로 이동 합니다.

[스크립트 태스크] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[스크립트 태스크 편집기]창에서 [ReadOnlyVariables]를 선택하면 아래 그림과 같이 변수를 선택 할 수 있는 창이 나타납니다.

해당 창에서 [User::Row_Cnt]라는 사용자 변수를 선택 합니다.

[스크립트 편집] 버튼을 클릭하여 스크립트를 편집 합니다.

[스크립트 편집]을 실행하면 스크립트를 편집 할 수 있는 새로운 창(비주얼 스튜디오)이 나타 납니다.

아래 그림처럼 메시지 박스를 출력하기 위한 소스코드와 변수를 입력 합니다.

편집이 완료 되었으면 창을 닫고 패키지를 실행 합니다.

패키지를 실행하면 메시지 창으로 실행된 행 수가 출력 됩니다.

실제 [데이터 흐름]탭에서 나타나는 정보 또한 메시지 박스에 출력된 행수와 동일 한 것을 확인 할 수 있습니다.

늘 그러듯!

녹색 : 정살 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 출력된 행수 만큼 데이터가 잘 입력 되었는지 확인 합니다.

보통 대용량 작업이나 1:1 조인등 처리량에 대해서 확인해야할 때가 있습니다. 이때 이 프로세스를 사용한다면 데이터 처리의 오류 유무 또는 데이터 누락등에 대해서도 확인하는데 매우 유용 할듯 합니다.

행 샘플링 / 비율 샘플링 – 데이터를 샘플링 하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [행 샘플링], [비율 샘플링]에 대해서 알아 보도록 하겠습니다.

[행 샘플링] 변환은 입력 데이터에 지정한 행수 만큼 샘플 대상을 선정하는 작업 입니다.

[비율 샘플링] 변환은 입력 데이터에 대해 지정한 비율 만큼 대상을 선정하는 작업 입니다.

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름]탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[샘플링 변환]이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [OLE DB 원본]와 [대상 데이터1(OLE DB 대상)], [대상 데이터2(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [멀티 캐스트]와 [행 샘플링], [비유 샘플링] 작업을 추가하여 데이터 흐름선을 연결 합니다.

멀태캐스트에 관한 내용은 아래 링크 참고

http://blog.naver.com/jevida/140147086468

[OLE DB 원본]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리 보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Address 테이블 사용)

[행 샘플링]작업에 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

아래 그림과 같이 [행 샘플링 변환 편집기]가 실행 됩니다.

편집기에서는 샘플링 행 수를 지정할 수 있습니다.

행 수 : 샘플링 의 수 (10 – 10개 추출, 1000 – 1000개 행 추출)

샘플 출력 이름 : 샘플된 데이터를 출력할 경로의 이름. 여기에서는 샘플된 데이터의 행이 출력됨

선택하지 않은 출력 이름 : 샘플되지 않은 데이터를 출력할 경로의 이름. 여기에서는 샘플 되지 않은 데이터의 행이 출력됨.

[열] 탭으로 이동하여 [입력 열]을 선택 합니다. 실습에서는 [AddressLine1] 을 사용합니다.

편집이 완료 되었으면 데이터 흐름선을 [대상 데이터1]에 연결 합니다. 그러면 다음과 같은 선택창이 나타납니다.

[출력]에서 [선택된 행에서 샘플링 출력]을 선택 합니다.(샘플된 행을 가져온다)

[대상 데이터1] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [행 샘플링]에서 출력될 결과물의 테이블과 컬럼이 생성됨을 확인 할 수 있습니다.

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

[비율 샘플링] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

아래 그림과 같이 [비율 샘플링 변환 편집기]가 실행 됩니다.

편집기를 살펴보면 이전의 [행 샘플링 변환 편집기]와 유사한 창이 나타납니다.

여기서 다른 점은 [행 샘플링]은 실제 행수를 지정하였다면 여기에서는 전체 행에 대한 비율을 설정 합니다.

행의 백분율 : 샘플링 할 비율 (전체 행수 대비 비율)

임의 초기 값 사용 : 이 옵션을 체크하지 않으면 샘플링 작업이 수행될 때마다 매번 다른 샘플이 추출됩니다. 이 옵션을 체크하고 초기값(Seed)을 지정하면 동일 데이터에 대해서는 Seed값에 따라 샘플링되는 값은 항상 동일 합니다.

(실습에서는 행 백분율 : 10%, 임의 초기값 사용 안 함)

편집이 완료 되었으면 데이터 흐름선을 [대상 데이터2]에 연결 합니다. 그러면 다음과 같은 선택창이 나타납니다.

[출력]에서 [선택된 행에서 샘플링 출력]을 선택 합니다.(샘플된 행을 가져온다)

[대상 데이터2] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다.(이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [비율 샘플링]에서 출력될 결과물의 테이블과 컬럼이 생성됨을 확인 할 수 있습니다.

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업을 보면 전체 19614의 입력행이 있습니다.

[행 샘플링]에서는 10건의 행을 설정 하였기에 출력 행수에 10개 행이 나타남을 확인 할 수 있습니다.

[비율 샘플링]에서는 전체 행의 10% 를 설정하였기에 1948개의 행이 출력됨을 확인 할 수 있습니다. 이때 비율 샘플링은 약간의 오차가 발생 할 수 있습니다.

패키지 작업이 완료 되었으면 SSMS를 실행하여 샘플링 작업이 잘 되었는지 확인 합니다.

[행 샘플링](대상 데이터1)에서는 샘플된 10건의 데이터를 확인 할 수 있습니다.

[비율 샘플링](대상 데이터2)에서는 샘플된 1948개의 데이를 확인 할 수 있습니다.

행 샘플링 및 비율 샘플링 작업은 전체 데이터 중 특정 수 만큼 샘플 대상을 추출하거나 대량 로그에서 표본 조사시 유용할 듯 합니다.

용어 조회 – 참조 데이터를 이용하여 빈도를 확인하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [용어 조회]에 대해서 알아 보도록 하겠습니다.

[용어 조회 변환]은 입력 데이터에서 미리 정의된 참조 데이터의 용어가 얼마나 나타나는지를 조회하는 작업 입니다.

이 변환 역시 [용어 추출]과 유사하게 영문 데이터에 대해서만 정상적으로 수행되며 입력 데이터는 유니코드 문자형(DT_WSTR), 유니코드 텍스트(DT_NTEXT)만 지원 됩니다.

관련 링크

http://blog.naver.com/jevida/140159227758

[BIDS]를 실행하여 [Integration Services 프로젝트]를 생성합니다.

[제어 흐름]탭에서 [데이터 흐름 태스트]를 드래그 앤 드롭으로 추가 합니다.

[용어 조회]이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 대상)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그리고 그 사이 [용어 조회] 작업을 추가하여 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리 보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Address 테이블 사용)

[용어 조회]작업에 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다

아래 그림과 같이 [용어 조회 변환 편집기]가 실행 됩니다.

편집기를 살펴 보면 [참조 테이블]탭에서는 변환에 사용할 참조 테이블을 지정 할 수 있습니다.

참조 데이터는 OLE DB 연결만 가능하며 SQL Server, Access테이블만 조회 가능합니다. 미리 정의된 용어 목록을 별도의 테이블에 저장 시킨 후 변환이 시작될 때 이를 메모리에 캐싱 후 조회 작업을 수행 합니다.

SSMS를 실행하여 참조 데이터를 사용할 테이블을 생성하고 데이터를 입력 합니다.

본 실습에서는 참조 데이터는 [Adventureworks] 데이터베이스의 [Person.Address] 테이블을 조회하여 몇몇 단어를 추출하여 [TBL_MasterData] 테이블을 생성하였습니다.

[참조 테이블] 탭에서 [OLE DB 연결 관리자]에서 참조 테이블이 있는 연결을 선택하고 [참조 테이블 이름] 에서 참조에 사용할 테이블을 선택 합니다. (TBL_MasterData)

[용어 조회] 탭에서 조회에 사용할 입력 열을 선택합니다. 그러면 하단의 속성창에 [통과열]에는 사용자가 선택한 [입력 열]이 나타나며 [출력 열 별칭]에는 사용자가 설정한 별칭으로 출력 됩니다.

속성을 지정하고 나면 빈 곳에서 마우스 오른쪽 클릭을 사용하여 [매핑 편집]을 실행 합니다.

[관계 만들기]창이 나타나면 [입력 열]과 [조회 열]을 매핑 합니다.

매핑이 완료되면 아래 그림처럼 관계선이 나타 납니다.

[고급]탭에서는 [옵션]으로 대/소문자 구분을 사용할 것인지 선택 할 수 있습니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [용어 조회]에서 출력될 결과물의 테이블과 컬럼이 생성됨을 확인 할 수 있습니다.

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 [용어 조회]작업이 잘 되었는지 확인 합니다.

Term : 용어

Frequency : 빈도

AddressLine1 : 입력에 사용된 행

빈도 계산은 입력 데이터 행 단위로 수행 됩니다.

예를 들어 한 입력 한 행에 "a Way Way"라는 단어가 있고 'Way'라는 참조를 조회한다면

Frequency에는 2로 표시 된다.

용어 추출 – 특정 용어를 추출 하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [용어 추출]에 대해서 알아 보도록 하겠습니다.

용어 추출은 입력데이터에 대해 단어를 추출하여 사전 형식의 데이터 결과를 만들어 주는 작업 입니다. 입력데이터에서 명사 또는 명사구의 형태로 추출되며 텍스트 입력 데이터에 대해 임계값, 최대 용어 길이등의 옵션을 설정하여 추출이 가능 합니다.

용어 추출 변환의 특징.

• 관계사와 대명사는 추출하지 않는다.
• 기본적으로 대/소문자를 구분하지 않는다.
• 단수/복수를 동일하게 추출한다.
• 변환은 전용 메모리 공간에 저장된 후 변환을 수행한다.
• 변환은 내부의 자체 알고리즘과 통계 모델을 사용한다.

[BIDS]를 실행하여 [Ingetration Service 프로젝트]를 생성 합니다.

[제이 흐름]탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[용어 변환]이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그리고 그 사이 [용어 추출] 작업을 추가하여 데이터 흐름선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE D 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리 보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 AdventureWorks의 Address 테이블 사용)

[용어 추출] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

아래 그림과 같이 [용어 추출 변환 편집기]가 실행 됩니다.

편집기를 살펴보면 [용어 추출] 탭에서는 입력 열을 지정할 수 있으며 출력 열 이름을 지정 할 수 있습니다.

[AddressID]를 선택 하여 봅니다. 오류가 발생 합니다. 현재 AddressID의 데이터 타입은 INT이기 때문에 지원할 수 없다고 나타납니다.

용어 추출 작업은 한글 데이터는 지원하지 않습니다. 영어만 가능 합니다.

입력 데이터는 유니코드 문자열(DW_WSTR)또는 유니코드 텍스트(DT_NTEXT)만 가능 합니다.

[제외] 탭에서는 추출하는 용어 중 제외할 용어가 있을 때 이 탭에서 설정 합니다.

제외할 데이터를 테이블에 저장한 후 이를 참조하도록 할 수 있습니다.

[고급]탭에서는 여러가지 속성을 정의 할 수 있습니다.

[용어 유형]

• 명사 – 단일 명사만 추출
• 명사구 – 명사구 형태의 데이터만 추출
• 명사 및 명사구 – 명사와 명사구 모두 추출

[점수 유형]

• 빈도 – 점수 유형을 빈도로 설정
• TFIDF – 점수 유형을 TF(용어 빈도)와 IDF(역문서 빈도)의 곱으로 설정합니다.

[매개변수]

• 빈도 임계값 – 단어 또는 구로 추출되기 위한 최소의 임계치.(예를들어 임계값이 3인경우 해당 용어가 3번이상 나와야 출력에 포함)
• 최대 용어 길이 – 추출되는 용어의 최대 길이를 설정. 이 값은 명사구에만 영향.

[옵션]

• 대/소문자 구분 용어 추출 사용 – 용어 추출 시 대/소문자 구분 여부 설정. 이 때 Bike는 bike로 분류가 되며 Bike와 BIKE는 다른 용어로 분류된다.

이번 실습에서는 [AddressLine1]을 선택 합니다. 해당 입력의 데이터 타입은 NVARCHAR 타입니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [용어 추출]에 추가하였던 컬럼이 자동으로 생성됨을 확인 할 수 있습니다.

[매핑]탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 이용하여 실제로 [용어 추출]이 잘 되었는지 확인 합니다.

용어 추출은 텍스트 형태의 기사나 E메일 등의 데이터에서 주요이슈 사항을 추출하여 분류, 분석하는 작업에 사용하면 유용할 듯 합니다.

감사 - 출력 열에 여러가지 정보를 나타내자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 감사에 대해서 알아 보도록 하겠습니다.

감사는 패키지가 실행 될 때의 환경이나 상황에 대한 정보를 데이터 흐름에 추가하여 여러가지 정보를 확인 할 수 있는 개체 입니다.

입력 데이터에 여러가지 정보를 추가하여 출력할 수 있습니다.

감사 변환은 하나의 입력과 하나의 출력을 가지며 오류 출력은 존재하지 않습니다.

BIDS를 실행하여 [Integration Service 프로젝트]를 생성 합니다.

[제어 흐름]탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[감사]이름을 지정하여태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 [OLE DB 원본]과 [OLE DB 대상]을 끌어다 놓습니다. 그리고 그 사이 [감사]작업을 추가하여 데이터 흐름선을 연결 합니다.

[OLE DB 원본]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 미리 보기를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Address 테이블 사용)

[감사] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

아래 그림과 같이 [감사 변환 편집기]가 실행 됩니다.

편집기를 살펴 보면 [출력열] 이름을 지정할 수 있으며 이때 어느 감사 정보를 출력할 것인지 [감사유형]을 정의 할 수 있습니다.

감사 유형의 종류

• 실행 인스턴스 GUID : 패키지가 수행할 때의 GUID값이며, 이 값은 매번 수행될 때마다 달리진다.
• 패키지ID : 수행되는 패키지의 ID정보, 패지키 ID는 제어 흐름 영역에서 속성창의 ID부분에서 확인 가능 하다.

  

• 패키지 이름 : 수행되는 패키지 이름
• 버전ID : 패키지의 버전 정보. 패키지의 버전은 패키지 내의 수정 작업이 저장되면 변경된다.
• 실행 시작 시간 : 패키지가 수행되기 시작한 시간.
• 컴퓨터 이름 : 수행되는 컴퓨터의 이름 정보
• 사용자 이름 : 패키지를 수행하는 사용자 이름
• 작업 이름 : 감사 변환이 포함되어 있는 태스크(데이터 흐름 작업의 이름) 이름.
• 작업 ID : 감사 변환이 포함되어 있는 데이터 흐름의 ID정보. 이 정보는 제어 흐름 영역에서 데이터 흐름 작업을 클릭한 후 속성창의 ID부분에서 확인 가능하다.

  

이번 실습에서는 [컴퓨터 이름]을 선택하도록 합니다.

[출력 열 이름]에 [ServerName]라는 출력 이름을 지정하고 [감사 유형]에서는 [컴퓨터 이름]을 선택 합니다.

[OLE DB 대상] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다.(이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.

[새로 만들기]를 선택 하면 [변환 작업]에 추가하였던 컬럼이 자동으로 생성됨을 확인 할 수 있습니다.

[매핑]탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 이용하여 실제로 데이터에 [감사]작업이 잘 이루어 졌는지 확인 합니다.

대상 데이터에 [ServerName]컬럼에 컴퓨터 이름이 [감사]작업을 통하여 입력 된 것을 확인 할 수 있습니다.

감사 변환은 데이터가 입력될 때의 상황에 대한 정보를 결과 데이터에 포함하여 사용할 수 있는 기능으로 잘못된 데이터 처리나 버전관리등을 할 때 유용하게 사용하면 좋을 듯 합니다.

집계 - 카운트, 평균, 그룹, 최소, 최대 다양한 집계를 하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간은 [집계]에 대해서 알아 보도록 하겠습니다.

집계는 입력열에 대하여 SUM이나 MAX, MIN, COUNT 등과 같은 집계연산을 수행하여 결과를 반환 합니다. SQL 쿼리에서 GROUP BY 구문과 비슷하여 집계를 수행할 열과 연산을 지정 합니다.

집계 연산 종류

GROUP BY : 집계 작업을 수행하기 위한 그룹 열.

SUM : 합계

AVERAGE : 평균 값

COUNT : 전체 로우 수

DISTINCT COUNT : 유니크 한 로우 수

MINIMUM : 최소값 반환 (SQL 쿼리와는 달리 문자형에 대해서는 수행 할 수 없음.)

MAXIMUM : 최대값 반환 (SQL 쿼리와는 달리 문자형에 대해서는 수행 할 수 없음.)

BIDS를 실행하여 [Integration Service 프로젝트]를 생성 합니다.

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[집계 변환]이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 원본 데이터(OLE DB원본)와 대상 데이터(OLE DB 대상)를 끌어다 놓습니다. 그리고 그 사이 [집계] 작업을 추가하여 데이터 흐름 선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 미리 보기를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks 의 Address 테이블 사용)

[집계] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

아래 그림과 같이 [집계 변환 편집기]가 실행 됩니다.

편집기를 살펴 보면 GROUP 연산에 대한 고급 속성과 편집기 고급 설정 탭이 있습니다.

[집계]탭에서 [고급] 버튼을 확장하면 GROUP BY에 대한 세부 속성을 설정 할 수 있습니다.

[편집기]-[고급]탭에서 또한 위의 고급 속성과 비슷하게 키 배율, 키 수, 고유 키 수 등을 지정할 수 있습니다. 하지만 여기에서의 속성은 변환 작업 전체에 대해 설정이 적용 됩니다.

집계 탭의 고급 설정 – 각 집계 작업에 대한 설정

집계 탭 하단의 설정 – 출력에 포함된 각 열에 대한 설정

고급 탭의 설정 – 집계 작업 전체에 대한 설정.

고유 수 배율 : DISTINCT COUNT연산일 때 이 속성을 사용한다. 고유 수의 정도에 따라 메모리를 미리 확보 함으로써 연산 수행 성능을 향상 시킬 수 있다.

    Unspecified : 고유 수 배율 속성 사용하지 않음(디폴트)

    LOW : 약 500000개 정도의 고유 수 정도

    MEDIUM : 약 5000000개 정도의 고유 수 정도

    HIGH : 약 25000000개 정도의 고유 수 정도.

고유 키 수 : 정확한 고유 수 값을 아는 경우 설정 한다. 이를 미리 지정함으로써 메모리를 미리 확보할 수 있다. 고유 수 배울과 고유 키 수를 모두 지정하면 고유 키 수의 속성이 적용 된다.

자동 확장 비율 : 집계 작업을 위한 메모리 사용에 대한 고급 설정으로 집계 작업 수행 중 추가로 메모리가 필요 할 때 늘릴 비율을 지정하는 것. (기본값 25%)

실습에서는 AddressID의 전체 로우수를 집계하도록 하겠습니다.

MINIMUM : 최소값 반환 (SQL 쿼리와는 달리 문자형에 대해서는 수행 할 수 없음.)

MAXIMUM : 최대값 반환 (SQL 쿼리와는 달리 문자형에 대해서는 수행 할 수 없음.)

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기를 선택하면 [변환 작업]에 추가하였던 컬럼이 자동으로 생성됨을 확인 할 수 있습니다.

[매핑]탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

집계 작업이므로 전체 로우수에 대한 카운트만 출력됨을 확인 할 수 있습니다.

패키지 작업이 완료 되었으면 SSMS를 실행하여 실제로 데이터가 정상적으로 집계 되었는지 확인 합니다.

대상 데이터와 원본 데이터를 집계한 결과 동일 함을 확인 할 수 있습니다.

집계 작업 또한 여러가지 작업이 병행가능 하므로 여러 원본에서 데이터 집계 시 매우 유용할 듯 합니다.

개체 전송 태스크 - 개체 속성까지 복사하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간은 [개체 전송 태스크]에 대해서 알아 보도록 하겠습니다.

SSIS를 이용하여 데이터를 가져오거나 SSMS에서 가져오기/내보내기에서 테이블을 복사하여 오는 경우 테이블의 속성이 복사되지 않아 1:1복사 테이블을 만들 때 또는 동일한 환경을 구축할 때 불편한 경우가 있습니다.

[개체 전송 태스크]를 이용하여 개체 속성까지 모두 복사하여 사용할 수 있습니다.

실제로 사용시 스키마 뿐만 아니라 테이블의 파일 그룹까지 모든 속성이 복사됨을 확인 할 수 있습니다.

BIDS를 실행하여 [Integration Service 프로젝트]를 생성 합니다.

[제어 흐름]탭에서 [데이터 흐름 태스크]를 드래그앤 드롭으로 추가 합니다.

[SQL Server 개체 전송 태스크]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

[개체 전송 태스크 편집기]가 나타나면 [개체]탭에서 [연결] 부분에서 원본소스와 대상소스를 선택 합니다.

(실습에서는 AdventureWorks 데이터베이스를 사용하였습니다.)

[개체 전송 태스크 편집기]에는 다양한 옵션을 설정 할 수 있습니다. 자세한 내용은 온라인 도움말 등을 참고하길 바랍니다. 기본적인 설정부터 고급설정까지 다양합니다.

[대상 복사 옵션]에서 [CopyAllObjects] 속성이 TURE이면 원본 소스의 모든 개체를 복사 함을 뜻합니다. 여기서는 특정 테이블만 할 것이므로 False를 선택 합니다.

[ObjectToCopy]를 확장하여 [TableList]를 선택 합니다.

[테이블 선택]창이 나타나면 [Address]테이블을 선택합니다.

편집이 완료 되었으면 태스크를 실행 합니다. 태스크를 실행하면 아래의 그림과 같이 실패가 나타납니다.(사용자 환경에 따라 성공이 나타날 수도 있습니다.)

[진행률] 탭에서 오류 내용을 확인 합니다. Person 이라는 스키마 소유자가 존재하지 않아 오류가 발생함을 알 수 있습니다. 처음 도입부에 설명하였듯이 개체 복사 시 스키마 속성까지 복사하므로 존재하지 않은 스키마로 인한 오류를 확인 할 수 있습니다.

[테이블 선택]창에서 스키마가 dbo 인 테이블을 선택합니다.

편집이 완료 되었으면 태스크를 실행 합니다.

이번에는 정상적으로 실행 되었습니다,

개체 복사가 잘 되었는지 SSMS를 실행하여 테이블 속성 및 데이터를 조회하여 봅니다.

이번 실습에서는 스키마 복사만 선택하였고 테이블안의 데이터 복사는 실행하지 않았습니다.

(개체 복사시 데이터 복사 가능)

테이블 속성이 원본의 속성과 동일함을 확인 할 수 있습니다.

이 작업은 백업 복원을 하기 힘든 (로그성 데이터)경우 원본의 환경을 유지하면서 데이터를 복사 및 이동 하는 작업에 매우 유용할 듯 합니다.

문자표 - 문자 데이터를 변환 하자

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [문자표]에 대해서 알아 보도록 하겠습니다.

문자표는 대문자를 소문자로 변환하거나 전자 문자를 반자 문자로 변환하는 등 문자 데이터에 대해 변환작업을 수행 할 때 사용합니다.

대상 속성에 따라 기존 열을 변경하거나 새로운 열을 추가하여 사용 할 수 있습니다.

연산 작업의 종류

• 소문자 : 입력 열의 데이터를 소문자로 변경
• 대문자 : 입력 열의 데이터를 대문자로 변경
• 바이트 반전 : 입력 열의 바이트 순서를 반대로 연결
• 히라가나 : 일본어인 경우 히라가나로 변경
• 가타카나 : 일본어인 경우 가타카나로 변경
• 반자 : 전자 문자를 반자로 변경
• 전자 : 반자 문자를 전자로 변경
• 대소문자 구분 기능 : 대/소문자의 구분기능 적용
• 중국어 간체 : 중국어인 경우 간체로 변경
• 중국어 번체 : 중국어인 경우 번체로 변경

BIDS를 실행하여 [Integration Service 프로젝트]를 생성 합니다.

[제어 흐름]탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[문자표]이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 원본 데이터(OLE DB 원본)와 대상 데이터(OLE DB 대상)을 끌어다 놓습니다. 그리고 그 사이에 [데이터 변환] 작업을 추가하여 데이터 흐름 선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 미리 보기를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Address 테이블 사용)

[문자표] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

아래 그림과 같이 [문자표 변환 편집기]가 실행 됩니다.

편집기에서 변환할 열을 선택 합니다.

하단의 박스에서 [대상] 속성에서 기존 열을 변환할지 새로운 열을 추가할지 선택 합니다.

연산 작업을 선택하여 변환 할 속성을 선택 합니다. 이때 중복으로 선택 가능 합니다.

(실습에서는 대문자 변환을 선택 합니다.)

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [변환 작업]에 추가하였던 컬럼이 자동으로 생성됨을 확인 할 수 있습니다.

[매핑]탭으로 이동하여 해당 데이터의 원본과 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 실제로 데이터가 정상적으로 변환되었는지 확인 합니다.

정상적으로 소문자의 데이터가 대문자로 변환된 것을 확인 할 수 있습니다.

기존의 SSIS기능인 [파생 열] 및 [데이터 변환] 등의 기능과 유사하지만 문자 데이터에 대하여 여러 가지 변환 작업이 필요할 때 활용 할 수 있을 듯 합니다.

CASE 1. 다중 속성 변환 작업.

변환 작업의 속성을 두 개 이상 지정한 경우 입니다.

그림 처럼 속성을 다중으로 선택하여 실행 합니다.

바이트 순서가 반대로 바뀌어 영어가 한문으로 나타나는 결과입니다. (영어가 한문으로 번역된 데이터가 아님에 주의하여야 합니다.)

CASE 2. 상반된 옵션 에러.

연산 작업을 여러개 동시에 할 수 있지만 상반된 옵션은 설정 할 수가 없습니다.

예를들면 대분자/소문자, 히라가나/가타가나 등 상반된 옵션을 선택하였을 경우 에러가 발생 합니다.

파생 열 - 다양한 함수를 사용하여 새로운 파생열을 만들자

안녕하세요 강성욱 입니다. (www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [파생 열]에 대해서 알아 보도록 하겠습니다.

파생 열은 SQL에서 계산된 열과 같이 기존의 열을 이용하여 새로운 열을 변환하는 기능 입니다. 예를들어 A, B컬럼이 있을 때 A+B를 정의하는 새로운 열 C를 만드는 기능입니다.

파생 열 변환은 데이터 변환과 거의 유사하지만 색로운 열을 추가하는 대신 기존을 대체 할 수도 있으며 여러가지 함수를 활용 함으로써 열 외에도 SSIS의 사용자 변수나 시스템 변수를 포함시킬 수도 있습니다.

길이, 전체 자릿수, 소수 자릿수, 코드페이지 등과 같은 속성은 데이터 변환 부분과 동일 합니다.

관련 링크

데이터 변환 : http://blog.naver.com/jevida/140158650970

데이터 변환 - 컬럼 속성을 변경하자

안녕하세요 강성욱 입니다. (www.sqltag.org, www.sqler.com, blog.naver.com/jevida)

이번 시간에는 데이터 변환에 대해서 알아 보도록 하겠습니다.

데이터 변환은 특정열에 대해여 데이터 형식, 길이, 자릿수, 코드 페이지 등의 속성을 변경하는 작업 입니다.

BIDS를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭 으로 추가 합니다.

[데이터 변환] 이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 원본데이터(OLE DB 원본)와 대상 데이터(OLD DB 대상)을 끌어다 놓습니다. 그리고 그 사이에 [데이터 변환] 작업을 추가하여 데이터 흐름 선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 미리 보기를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 Adventureworks의 Address 테이블 사용)

[데이터 변환] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[데이터 변환 편집기]가 나타나면 아래 그림과 같이 데이터 변환에 사용할 컬럼을 선택 합니다. 그러면 편집기 하단에서 선택된 컬럼의 대상 이름 및 출력 속성을 지정 할 수 있습니다.

현재는 기존의 데이터 타입 및 길이를 나타내고 있습니다.

출력 별칭 및 데이터 형식, 길이 등을 수정 합니다.

[AdressID] : 부호없는 4바이트 정수 -> 문자열 50

[City] : 유니코드 문자열 30 -> 유니코드 문자열 200

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [데이터 변환]작업에 추가하였던 컬럼이 자동으로 생성됨을 확인 할 수 있습니다.

[매핑] 탭으로 이동하여 해당 데이터의 원본과 대상이 적절히 매핑 되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

패키지 작업이 완료 되었으면 SSMS를 실행하여 실제로 데이터가 정상적으로 변환 되었는지 확인 합니다.

테이블 속성을 확인하여 기존 데이터의 속성과 변환된 컬럼의 속성을 비교 하여 잘 변환되었는지

확인 합니다.

그리고 정상적으로 데이터가 입력된 것을 확인 할 수 있습니다.

이 작업은 대용량 ETL 작업시 집계유형에 따라 컬럼의 속성을 변경하는 데이터를 가져올 때 유용할 듯 합니다.

열 복사 - 원본 열을 복사 하자 

안녕하세요 강성욱 입니다. (www.sqltag.org, www.sqler.com, blog.naver.com/jevida)

이번 시간에는 [열 복사]에 대해서 알아 보도록 하겠습니다.

열 복사는 멀티캐스트와 비슷하나 멀티캐스트의 경우에는 데이터 전체에 대한 복사작업이며 열 복사 변환은 입력데이터 중 특정 열을 동일한 형태로 복사를 하는 변환 입니다.

관련링크

멀티 캐스트 : http://blog.naver.com/jevida/140147086468

BIDS를 실행하여 [Integration Services 프로젝트]를 생성 합니다.

[제어 흐름]탭에서 [데이터 흐름 태스크]를 드래그앤 드롭 으로 추가 합니다. [열 복사] 이름을 지정하여 태스크를 생성 합니다.

[데이터 흐름]탭에서 그림과 같이 원본데이터(OLE DB 원본) 와 대상 데이터(OLE DB 대상)을 끌어다 놓습니다.

그리고 그 사이에 [열 복사] 작업을 추가하여 데이터 흐름 선을 연결 합니다.

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

그림과 같이 편집기 창이 나타나면 [OLE DB 연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 미리 보기를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

[열 복사] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[열 복사] 편집기가 나타나면 아래 그림과 같이 열복사에 사용 할 컬럼을 선택 합니다. 그러면 편집기 하단에 복사되는 열이 나타나며 새로운 이름을 지정 할 수 있습니다.

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 편집을 선택 합니다.

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 만들어져 있다면 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 [열 복사] 작업에서 추가하였던 복사 컬럼이 자동으로 나타나는 것을 확인 할 수 있습니다.

[매핑]탭으로 가서 해당 데이터의 원본과 대상이 적절히 매핑되었는지 확인 합니다.

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹새 : 정상 실행

빨간색 : 오류 발생

[진행률] 탭에서는 현재 작업의 진행 상태 등의 정보를 출력합니다.

패키지 작업이 완료 되었으면 SSMS를 실행하여 실제로 데이터가 정상적으로 복사 되었는지 확인 합니다. 새로운 [복사 열]에 데이터가 [원본 열]과 동일하게 입력된 것을 확인 할 수 있습니다.

원본 데이터를 읽어 온 후 여러 작업을 할 때 원본의 복사본을 유지할 때 사용하면 좋을 듯 합니다.

SSIS - 열 내보내기

이번 시간에는 열 내보내기 라는 주제를 가지고 포스팅을 합니다.

이번 장을 학습하기 이해서는 반드시 [열 가져오기]를 먼저 실습 하신 후 진행 하시길 바랍니다.

(예제 데이터를 지난 학습에 사용한 데이터를 이용.)

관련 링크

열 가져오기  : http://blog.naver.com/jevida/140150105798

열 내보내기는 열 가져오기와 반대로 특정 열의 값을 파일로 출력합니다.

열을 내보낼 수 있는 유형은 텍스트 스트림 (DT_TEXT), 유니코드 텍스트 스트림(DT_NTEXT), 이미지(DT_IMAGE)만 가능 합니다.

(지난번 열 가져오기 할때 사용하였던 데이터 유형 입니다.)

그럼 열 가져오기 실습이 완료 되었다는 가정하에 열 내보내기 실습을 하도록 하겠습니다.

1. SSMS 를 실행하여 지난 시간에 실습한 TBL_B에 file_Path 라는 컬럼을 추가 하여 해당 파일이 저장될 경로와 파일명을 입력 합니다.

이때 해당경로의 폴더에 쓰기 권한이 필요 합니다!!! 

(권한의 편리성을 위하여  Everyone 계정에 Write 권한 추가)

2. BIDS를 실행하여 프로젝트를 생성하여 [열 내보내기] 제어흐름을 생성 합니다.

 

3. [데이터 흐름] 탭에서 TBL_B 원본을 선택 합니다.

 

4. [데이터 흐름] 탭에서 [열 내보내기] 작업을 추가 합니다.

그리고 [편집]을 선택 합니다.

 

5. [열 내보내기 변환 편집기] 에서 추출열과 파일 경로 열 을 선택 합니다.

* 추출열 : 추출할 데이터가 있는 열. 텍스트 유형 또는 이미지 유형이 자동으로 나열 됩니다.

* 파일 경로 열 : 추출열에서 추출한 파일이 저장될 경로가 입력된 열.

* 추가 허용 : 대상 파일이 이미 있을경우 추가로 쓸것인지 설정.

* 강제 자름 : 데이터를 쓰기 전에 기존의 파일 내용을 삭제하고 새로 쓸것인지를 설정.

* 바이트 슨서 표기: 바이트 순서 표시(BOM : Byte Order Mark)를 파일에 쓸것인지 설정.

 데이터 형식이 유니코드 텍스트 스트림(DT_NTEXT)이며 기존 파일에 추가되지 않는 경우에만 BOM사용 가능.

 

6. [고급 편집기 표시] 를 선택 합니다.

 

7. [고급 열 내보내기 편집기] 창에서 [입력 열] 탭을 선택 합니다.

입력열과 출력 별칭을 설정 합니다.

 

8. 실행 합니다.

늘 그렇듯!!

녹색 :  정상 실행

빨간색 :  오류 발생.

 

9. file_path 경로 폴더로 가서 실제 파일이 생성되었는지 확인 합니다.

학습하기 : 5번의 설정에서 환경을 바꾸어 테스트 하면서 어떤 변화가 있는지 살펴 봅니다.  

강성욱 / http://sqlmvp.kr

SSIS - 열 가져오기 - BLOB(Binary Large Object) 데이터를 저장하자

오늘은 열 가져오기 입니다. 

열 가져오기 및 열 내보내기 (다음 포스팅 예정)는 BLOB (Binary Large Object)형 데이터를 대상로 수행 되는 작업 입니다.

보통 이미지나 큰 텍스트 파일들을 보관할 때에는 데이터베이스에 주소만 기록하고 실제의 파일은 다른 물리적인 위치에 경유하는 경우가 많습니다. 

하지만 SQL Server에서도 Image, Text, varbinary 등 데이터를 저장할 수 있는 타입을 제공 합니다.

물론 이미지를 모두 데이터베이스에 저장한다면 데이터의 크기가 커지는 문제 및 패킷 사이즈의 문제가 발생하지만

데이터베이스 내에 저장되기 때문에 보안수준이 데이터베이스 수준과 동일하게 유지되고 관리의 편의성이 증대 되는 장점이 있습니다.

(사용처에 대한 고민은 사용자가...알아서.....)

실습을 통해서 열 가져오기를 만들어 보겠습니다

1. 우선 테스트에 필요한 이미지 파일을 생성 합니다.

저는 D:\Test 라는 폴더에 

1.png (저 이렇게 생겼어요 ㅋ 잘생겼나요?)

 2.png

이렇게 그림 파일을 생성 하였습니다.

파일 경로 기억 하세요 ^^

2. 테스트용 테이블을 생성 합니다.

TBL_A : 원본 테이블

TBL_B : 출력 테이블

원본 테이블 (TBL_A)에 데이터를 입력 합니다. 이때 파일 경로를 실제 이미지 파일이 있는 경로로 입력 합니다.

(파일 없으면 에러 납니다. 에러 처리에 관한 포스팅은 다음 기회에~!)

3. BIDS를 실행 합니다.

프로젝트를 생성합니다.

4. [데이터 흐름 태스크]를 만들고 [데이터 흐름] 탭에서 아래와 같이 작업을 끌어다 놓습니다.

그리고 [열 가져오기]작업에서 [편집] 선택 합니다.

5. 편집기 창에서 [입력 열] 탭을 선택 합니다.

입력 열에서 이미지 파일의 컬럼을 선택 합니다.

6. 만약 지원되지 않는 열이 선택 된다면 오류가 발생 합니다.

7. [입/출력 속성] 탭으로 이동 합니다.

[열 가져오기 출력]을 확장하여 [출력 열]을 확장 합니다.

[열 추가(C)] 버튼을 선택하여 출력열을 생성하고 이름을 지정 합니다.

이때 이름은 기존의 컬럼 명과 구분 할 수 있는 것으로 합니다.

(뒤에서 매핑시 혼돈의 우려가 있기 때문에)

8. [출력 열] - [Convert_Image] 속성에서 데이터 타입을 변경 합니다.

[유니코드 텍스트 스트림]을 선택 합니다.

9. [Convert_Image]의 속성에서  ID 값(55) 을 기억 합니다.

10. [열 가져오기 입력] 을 확장하여 [입력 열] 에서 기존의 이미지 경로 컬럼을 선택합니다.

그리고 속성 창에서 [FileDataColumnID]를 Convert_Image 의 ID값을 입력하여 매핑 합니다.

11. 출력 선을 모두 연결 합니다.

TBL_B에서 [편집]을 선택 합니다.

12. TBL_B의 [편집기]창에서 입력열과 출력열에 대한 매핑을 합니다.

기존의 add_file이 아닌 변환된 값을 저장하여야 하므로 입력열 - 출력열 의 매핑을 변경 합니다.

13. 패키지를 실해 합니다.

늘 그러듯!

녹색 :  정상 실행

빨간색 : 오류 발생

14. 데이터를 확인 합니다.

기존 TBL_A에는 파일의 경로만 가지고 있습니다.

TBL_B에는 해당 경로의 그림파일에 대한 바이너리 값이 저장된 것을 확인 할 수 있습니다.

열 가져오기를 활용하면 외부의 다른 툴 없이도 그림파일을 간단히 DB로 저장할 수 있을 듯 합니다.

강성욱 / http://sqlmvp.kr

SSIS - 병합 조인( 두 개의 입력된 데이터를 조인 하자)

오늘의 병합 조인을 하기 위해서는 반드시 이전 시간의 병합 에 대해서 꼭 학습 하여야 합니다.

병합 포스팅 : http://blog.naver.com/jevida/140149753108

병합 조인이란 무엇인가?

병합 조인은 병합과 마찬가지로 정렬되어 입력 된 2개의 데이터를 조인 하는 것 입니다.

단 수행 할수 있는 조인으로는 아래의 종류가 있습니다.

내부 조인 (INNER JOIN)

왼쪽 우선 조인 (LEFT OUTER JOIN)

완전 조인 (FULL OUTER JOIN)

병합 조인은 두 개의 입력에 대해서만 조인 할 수 있는데 이는 SQL 쿼리문을 작성하여 실행하는 것 보다 불편 할 수도 있습니다.

하지만 이기종 DBMS 및 테이블 데이터가 아닌 경우에도 임시 테이블을 사용하지 않고 직접 조인 연산을 수행 할 수 있는 장점이 있습니다,.

병합 조인은 병합과 마찬가지로 입력 데이터가 정렬되어 있어야 합니다.

실습으로 알아 보도록 하겠습니다.

1. 실습 데이터를 생성 합니다.

Adventure Works 데이터베이스의 Person.Address 테이블의 데이터를 사용 합니다.

아래의 그림에 있는 쿼리 처럼 실습용 데이터를 추출 합니다.

2. BIDS를 실행하여 SSIS 패키지를 생성 합니다.

아래의 그림 처럼 SSIS 작업에 [병합 조인] 등록 합니다.

* 병합 아이콘과 유사하게 생겼으니 유의 할 것.

 

3.  출력선을 병합 조인으로 끌어다 놓으면 [입/출력] 선택 창이 나타납니다.

이때 병합의 기준을 [병합 조인 왼쪽 입력] 으로 할 것인지 [병합 조인 오른쪽 입력]을 할 것인지 선택 합니다.

실습에서는 기본 왼쪽으로 하도록 하겠습니다.

 

4. [병합 조인] 작업에서 [편집]을 선택 합니다.

만약 [편집] 버튼을 선택 했을 때 경고 창이 나타난다면 입력 데이터 (TBL_A, TBL_B)

 정렬이 되지 않았을 수도 있으니 반드시 정렬을 확인 합니다.

정렬 방법 : http://blog.naver.com/jevida/140149753108

5. [병합 조인 변화 편집기] 창에서 [조인 유형]을 선택 합니다.

실습에서는 [완전 외부 조인] 으로 하도록 하겠습니다.

아래 그림에서 출력 별칭은 사용자 수정이 가능 합니다.

 

6. 출력 선을 모두 연결 하였으면 TBL_C 에서 편집을 선택합니다.

 

7. TBL_C 의 대상 편집기 창에서 속성을 설정 합니다.

[매핑] 탭에서 해당 컬럼의 매핑을 설정 합니다.

 

8. 패키지를 실행 합니다.

늘 그러듯~

녹색 : 정상 실행

빨간색 : 오류

각 테이블의 7건 데이터를 병합 조인하여 총 10건의 결과물이 출력 되었습니다.

 

9. SSMS에서 데이터가 잘 입력 되었는지 확인 합니다.

FULL OUTER JOIN 이 잘 되었나요?

SSIS의 병합 조인을 이용하여 테이블 형식이 아닌 데이터를  조인할 때 유용하게 활용 할 수 있을 듯 합니다.  

강성욱 / http://sqlmvp.kr

SSIS – 병합 (두개의 입력 데이터를 합치자)

SSIS - 병합  기능에 대해서 알아 보도록 하겠습니다.

병합은 UNION ALL 과 비슷하지만 조금 다른 기능 입니다.

차이점에 대해서 먼저 알아보고 실습을 하도록 하겠습니다.

실습을 통해서 병합의 기능과 UNION ALL 의 차이점을 확인 해보도록 하겠습니다.

1. 우선 실습에 사용할 데이터를 생성 합니다. 데이터는 AdventureWorks 데이터베이스의 Person.Address 테이블 입니다.

아래 그림처럼 Person.Address 테이블에서 AdressID, AdressLine1 컬럼만 

[Test_Address2], [Test_Address3] 테이블로 복사 합니다.

그리고 동일 형식의 타입으로 [Test_Address3] 테이블을 생성 합니다.

2.BIDS (Business Intelligence Developement Studio)를 실행 하여 프로젝트를 생성 합니다.

[데이터 흐름 태스크] 를 추가 합니다.

 

3. [데이터 흐름] 탭에서 아래 그림과 같이 작업을 추가 합니다.

4. [Test_Address] 원본을 병합으로 끌어 놓으면 그림과 같은 [입/출력] 선택창이 나타 납니다.

(병합은 오직 두 개의 데이터 입력만 가능합니다.)

Test_Address (병합 입력 1)

Test_Address (병합 입력 2)

 

5. [병합] 작업에서 편집을 선택하면 [패키지 유효성 검사 오류]가 발생 합니다.

왜 발생할까요?

위에서 설명하였듯이 병합은 반드시 정렬된 조건으로 입력이 되어야 합니다.

즉 현재 입력 데이터는 정렬된 데이터이다!  라는 것을 알려주어야 합니다.

 

6. 그래서 현재 입력 데이터는 정렬된 것이다!! 라는것을 알려주기 위한 두가지 방법이 있습니다.

(1) Sort 작업을 이용하여 정렬된 데이터를 입력한다. :  실제 테스트 테이블의 데이터는 정렬되어 있지만 DB는 알 수 없습니다.

그래서 정렬 작업을 중간에 삽입함으로써 정렬된 데이터 라는것을 알려 줍니다.

(2) 데이터 속성에서 정렬된 데이터라고 강제 지정 합니다.

7. 정렬을 이용한 방법 :  아래 그림처럼 정렬 작업을 중간에 추가하여 정렬된 데이터임을 지정 합니다.

(정렬에 관한 포스팅 : http://blog.naver.com/jevida/140148804730)

8. Test_Addrss2 에서 [고급 편집기(A)]를 선택 합니다.

 

9. 편집기에서 [입/출력 속성] 탭을 선택 합니다.

아래 그림처럼 IsSorted 항목을 True 로 선택 합니다. (해당 입력 속성은 정렬되어 있다 라고 강제 지정 한다는 뜻입니다.)

 

10. 그리고 [출력 열]을 확장하여 정렬키를 지정 합니다.

정렬키는 AddressID 에서 SortKeyPostion 을 1로 설정 합니다.

[SortKeyPostion 속성]

1: 오름차순 (ASC)

-1 : 내림차순 (DESC) 

 

11. 설정이 완료 되었으면 실행 합니다.

녹색 : 정상실행. 

 

빨간색 : 오류.

 

12. SSMS에서 데이터가 정상적으로 입력되었는지 확인 합니다.
데이터가 입력 된 것을 확인 할 수 있습니다.

그렇다면 UNION AL 과의 차이점을 무엇일까요?

13. UNION ALL 과의 차이점을 비교 하기 위해 UNION ALL 작업을 추가 합니다.

SSMS에서 Test_Address3 와 같은 동일 형식의 테이블 Test_Address4 를 생성 합니다.

긔록 아래 그림처럼 SSIS 작업을 추가 합니다.

(아래 흐름을 이해 하지 못하시는 독자들은 지금까지 포스팅을 다시 한번 복습 바랍니다.)

14. 입력된 데이터를 비교해 보도록 하겠습니다.

병합 과 UNIONN ALL 의 차이점을 찾으 셧나요?

(1) 병합 :  2개의 입력 데이터가 정렬되어 저장 됩니다.

(2) UNION ALL  :  선행 입력 뒤에 후행 입력이 덧붙여 지는 형식 입니다.

 

이렇게 해서 병합에 대해서 살펴 보았습니다.

 UNION ALL 과 약간의 차이점이 있지만 명백히 다른 기능인 것을 확인 할 수 있습니다.  

그렇다면

정렬 이전  UNION ALL 을 먼저 하고 정렬 작업을 하면  병합기능과 동일 하지 않을까?

라고 생각하시는 독자가 분명이 있으이라 생각 합니다...

(아무 생각없이 따라하기만 하신건 아니죠? ㅡ.ㅡ)

이 때에는 상황에 따라 잘 판단하여야 합니다.

100만건의 데이터 입력이 10개 있다고 가정 할때 

(1) UNION ALL을 해서 1000만건을 정렬을 할것인지?

(2) 100만건씩 병합을 해서 9번의 병합 작업을 하여 1000만건을 처리 할 것인지?

이때는 서버의 메모리가 많을경우 전자의 방식을. 메모리가 부족할 경우에는 후자를 추천 합니다.

하지만 항상 모든 작업은 비지니스 환경에 맞게 스스로 결정 해야합니다.

강성욱 / http://sqlmvp.kr

플랫파일 대상 - 데이터를 텍스트로 저장하자

플랫파일 대상 - 데이터를 텍스트로 저장하자

1. BIDS를 실행하여 프로젝트를 생성 합니다.

오늘의 프로젝트 이름은 [플랫파일 대상] 입니다.

도구 상자에 보면 작업 이름이 [플랫파일 대상] 으로 되어 있어요 ^.^

 

2. 늘 그렇듯 [태스크 흐름] 을 끌어다 놓습니다.

태스크 이름은 수정 하셔도 무방합니다~!!

3. 오늘의 원본 데이터 역시 샘플DB인 Adventure Works 입니다.

(실습할 때 정말 유용하게 잘 쓰이는 듯 합니다.)

Adventure Works 의 [Person.Adress]테이블을 원본 데이터로 사용합니다.

 

4.대상 작업은 [플랫 파일 대상]을 끌어다 놓습니다.

그리고 [편집]을 선택 합니다.

 

5. 플랫 파일 대상 편집기 에서 [새로 만들기(N)]를 선택 합니다.

어떤 기호로 구분할 것인지 선택 합니다.

 

6. 구분 기호를 지정 하였으면 [찾아보기(W)]를 선택하여 플랫파일의 저장 위치를 지정 합니다.

 

7. 필자의 경우에는 아래 그림에 해당하는 경로를 지정 하였습니다.

그리고 파일명을 입력 합니다.

 

8. 파일위치 및 이름이 사용자가 입력한 것과 맞는지 확인 합니다.

그리고 구분자 또한 선택 한 것과 동일한지 확인 합니다. 

(리스트 박스(?)에서 수정 가능 합니다.)

 

9. [매핑] 탭으로 이동하여 입력 열과 대상열의 매핑을 확인 합니다.

10. 모든 세팅이 완료 되었으면 실행을 합니다.

녹색이면 정상 실행!

빨간색이면... 오류 발생!! 

 

11. 플랫 파일의 저장 위치에 가서 텍스트파일이 생성되었는지 확인 합니다.

Person.Address.txt 라는 파일이 생성된 것을 확인 할 수 있습니다.

 

12. 텍스트파일을 실행해 보면 Adventure Works 의 Person.Address 의 데이터가 텍스트 파일로 저장된 것을 확인 할 수 있습니다.

 

DB to DB가 아닌 작업에 사용하면 매우 좋을 듯 합니다.

필자의 경우에는 해외의 지표 데이터를 BCP명령을 이용하여  TXT로 저장하여 FTP로 받아 보고 있는데

SSIS를 이용하면 TXT저장은 물론 FTP 전송까지 한번에 가능하여 매우 편리할 것으로 생각 됩니다.

강성욱 / http://sqlmvp.kr

SSIS - SORT (데이터를 정렬 하자)

오늘은 SSIS의 기능에 정렬(SORT)에 대해서 알아 보겠습니다.

정렬은 우리가 자주 쓰는 기능으로 쿼리문에서는 ORDER BY 을 의미 합니다.

SSIS에서 데이터를 끌어오거나 내보낼때 정렬 할 수가 있습니다.

예를들어 쿼리문의 경우를 살펴 보겠습니다.

예제 데이터베이스 : AdventureWorks

AdventureWorks 데이터베이스에서 Person.Address 테이블을 조회 하여 봅니다.

AddressID 로 정렬된 것을 확인 할 수 있습니다.

그렇다면 AddressLine1의 컬름으로 오름차순으로 하고 싶다. 어떻게 할까요?

아래의 쿼리 구문을 사용해야 합니다.

AddressLine1으로 정렬 된 것을 확인 하셨나요?
 

그렇다면 이제 SSIS로 정렬하여 데이터를 넣어 보겠습니다.

1. BIDS를 실행하여 새 프로젝트를 생성합니다. 그리고 [데이터 흐름 태스크]를 끌어다 놓습니다.
 

2. [데이터 흐름] 탭에서 원본(AdventureWorks.Person.Address)과 대상(Test.dbo.Tbl_Sort)을 끌어다 놓습니다.

그리고 도구 상자에서 [정렬] 작업을 끌어놓고 편집을 선택 합니다..
 

3. [정렬]의 편집기를 살펴보면 원본의 컬럼정보가 나타 납니다. 여기서 필요한 입력 열을 선택 합니다.

본 실습에서는 Test 데이터베이스에 TBL_Sort라는 테이블 명으로 AddressID, AddressLine1 컬럼만 생성하였습니다.

필요한 컬럼을 선택하고 정렬 방법 및 정렬 순서를 선택 합니다.

4. 작업이 완료 되었으면 SSIS 패키지를 실행 합니다.

(만약 빨간색으로 오류가 발생한다면 오류를 수정 후 다시 시작 합니다.)
 

5. SSMS에서 데이터가 정상적으로 정렬되어 입력 되었는지 확인 합니다.

AddressLine1 오름차순으로 정렬 된 것을 확인 할 수 있습니다.
 

정렬은 앞에서 실습한 멀티캐스트, UNION ALL, 조건부 분할 등과 조합하여 각종 데이터 수집 및 처리에 활용 할 수 있습니다.

강성욱 / http://sqlmvp.kr