Data Science Lab

SSIS - UNION ALL(데이터 합치기)

UNION ALL 기능에 대해서 알아 보겠습니다. 

데이터를 조작하다 보면 여러곳의 데이터를 한 곳으로 합쳐야 할때가 있습니다.

이럴때 SSIS의 UNION ALL 기능을 이용하면 빠르고 효과적인 사용이 가능 합니다.

SQL의 쿼리 경우에는 테이블에 있는 데이터를 대상으로 SQL Server에서 연산작업을 합니다.

하지만 SSIS를 이용하면 텍스트, 엑셀 또는 타 DBMS에서도 수행할 수 있습니다.

여러개의 엑셀이나 텍스트 파일을 임시 저장단계 없이 UNION ALL로 변환하여 합칠 수 있습니다.

예를 들어 Address_99(99건 데이터), Address_199(100건 데이터), Address_299(100건 데이터) 이렇게 3개의 테이블이 있습니다.

만약 이들을 하나의 데이터로 합쳐서 보고 싶다면 SQL쿼리를 이용합니다.

이렇게 쿼리의 UNION ALL 방법을 SSIS로 만들어 보도록 하겠습니다.

1. 우선 BIDS를 실행하여 UNION ALL이라는 프로젝트를 생성 합니다.

2.   태스크 흐름을 추가 합니다

3.데이터 흐름 탭에서 [UNION ALL] 작업을 원본과 대상 으로 연결 합니다.
 

4. UNION ALL 작업에서 편집을 선택합니다.
 

5. UNION ALL 편집기에서는 출력 매핑을 지정 할 수 있습니다.

이때 [무시]를 선택하게 되면 해당열은 NULL로 출력 됩니다.

각 입력의 데이터 유형은 반드시 동일 해야 합니다.

 

6. SSIS 패키지를 실행하여 정상적으로 동작하는지 확인 합니다.

처리결과를 보면 총 299건이 UNION ALL 하여 대상 테이블에 입력된 것을 확인 할 수 있습니다.

 

7. 실제 SSMS에서 데이터가 잘 들 어왔는지 확인 합니다.

총 299건의 데이터가 입력 된 것을 확인 할 수 있습니다.

 

강성욱 / http://sqlmvp.kr

SSIS - 조건부 분할 (조건에 따라 데이터를 출력하자)

조건부 분할은  원본의 데이터를 읽어서 조건에 따라 서로 다른 출력 경로(다른 여러 테이블)로

데이터를 구분하여 내보냅니다.

실습을 통해서 알아 봅니다.

이번 실습은 AdventureWorks 의 Person.Address 데이터를

0~99 ,100~ 199, 200~299 구간을 세개의 테이블로 나누어 저장 합니다.

1.BIDS를 실행하여 프로젝트를 생성 합니다.

2. 데이터 흐름 태스크 작업을 드래그& 드랍하여 끌어다 놓습니다.

 

3. OLE DB 원본데이터에서 AdventureWorks의 Person.Address를 선택 합니다.

 

4. 도구 상자에서 조건부 분할을 끌어다 놓고 편집을 선택 합니다.
 

5. 편집기에서

조건은 AddressID 값이 0~99, 100~199, 200~299 의 조건을 부여 합니다.
 

6. 편집기에서 모든 조건 입력이 끝났으면 출력선을 OLE DB 대상에 끌어다 놓습니다.

그러면 아래의 그림처럼 입/출력 선택 창이 나타납니다.

해당하는 조건을 선택 합니다.

(0~99, 100~199, 200~299  모두 동일 방법으로 끌어다 놓습니다.)

 

7. 출력 연결이 완료 되었으면 실행을 하여 SSIS가 정상적으로 실행되는지 확인 합니다.

 

8. 실제 테이블에 데이터가 잘 들어왔는지 확인 합니다. 조건에 해당하는 데이터만 구분되어 저장된 것을 확인 할 수 있습니다.

 

강성욱 / http://sqlmvp.kr

멀티캐스트 – 하나의 데이터를 여러곳으로 복사하기

오늘은 SSIS에서 멀티캐스트 기능에 대해서 알아 보겠습니다. 

우리가 데이터를 수집하는데 있어서 동일한 데이터를 여러 곳의 위치로 복사해야 하는 이슈가 있습니다.

이때 쿼리를 이용하거나 SQL2000 에서는 DTS를 이용해야 합니다.

이는 모든 연결이 1:1 밖에 지원되지 않아 10번의 데이터를 복사하여야 할 경우에는 10번의 실행작업이 필요 합니다.

하지만 SSIS의 멀티캐스트를 이용하면 원본에서 읽어온 데이터를 메모리에서 복사하여

여러 경로로 전달하기 때문에1번의 작업으로 여러곳의 데이터를 한번에 복사 할 수 있습니다.

실습 내용은  AdventureWokrs의 Sales.SalesPerson의 데이터를 Test 의 dbo.MultiCast_1 ~ 5 번까지 복사 합니다.

1.우선 Test DB를 생성하여 AdventureWokrs의 Sales.SalesPerson과 동일한 테이블을 생성합 니다.

2. BIDS를 실행하여 새로운 프로젝트를 생성 합니다.
 

3. 프로젝트는 Integration Services 프로젝트 를 선택 합니다.
 

4., BIDS 창에서 프로젝트를 생성하고 나면 하단의 연결관리자에서 연결관리자를 등록 합니다.
 

5. 이번 실습에서는 AdventureWorks 와 Test  연결관리자가 필요 합니다.

(연결 관리자 등록후 식별을 위해 이름을 DB명과 동일하도록 변경)

1) Test  연결 관리자
 

2) AdventureWorks 연결 관리자
 

6. 도구 상자 에서 [데이터 흐름 태스크]를 [제어흐름 탭]  으로 끌어다 놓습니다.

그리고 이름을 [멀티캐스트] 라고 변경 합니다.
 

7. [데이터 흐름]  탭에서 [OLE DB 원본]과 [멀티캐스트]를 끌어다 놓고 연결 합니다.

8. [OLE DB 원본] 에서 마우스 오른쪽을 클릭하여 [편집] - 연결 관리자를 다음과 같이 설정 합니다.

9. [OLEDB 대상]을 데이터 흐름으로 끌어놓고 멀티캐스트와 연결 합니다.

 10. [OLE DB 대상]에서 마우스 오른쪽을 클릭하여 [편집]을 선택하여 아래와 같이 설정 합니다.

(MultiCast1 ~ MultiCast5 까지 반복)

 

11. 모든 설정이 마무리 되었으면 실행을 클릭하여 데이터가 잘 복사되는지 확인 합니다.

(실습에서는 총 17개의 행이 복사 되었습니다.)

만약 에러가 발생한다면 이벤트 처리기에서 에러내용을 확인하여 수정 합니다.
 

12. 실제 테이블에도 데이터가 잘 들어왔는지 확인 합니다.
 

 이렇게 하여 1번의 원본 테이블 읽고 5번의 복사작업을 완료 하였습니다.

강성욱 / http://sqlmvp.kr

SSIS를 이용하여 데이터 가져오기

단순하게 데이터를 가져오는 방법과 변수를 이용하여 테이블을 가져오는 방법 과변수를 이용하여 특정 테이블 가져오기 입니다.

테이블 – 테이블 데이터 복사하기.

1. 우선 테스트 테이블을 생성 합니다.

원본테이블 : tbl_20110630

대상테이블 : tbl_20110701

원본 테이블에 데이터를 입력 합니다.

2. SSBID 를 실행합니다..

3. 새프로젝트에서 [Integration Service 프로젝트]를 선택 합니다.

4. 메뉴바에서 [SSIS] – [변수]를 선택합니다.

5. [제어흐름] 탭을 선택하고 [도구상자]에서 [데이터 흐름 태스크]를 마우스로 끌어다 놓습니다. [속성]에서 [Name] 을 [Log수집] 이라고 수정합니다.

6. [데이터흐름]탭으로 이동하여 [OLE DB원본] 과 [OLE DB 대상] 을 끌어 놓습니다.

7. [OLE DB원본] 에서 마우스 오른쪽 [편집] 을 선택 하여 [데이터액세스 모드]는 [테이블 또는 뷰] 로 선택하고 [테이블 또는 뷰이름]에서는 [dbo].[tbl_20110630]을 선택 합니다.

8. [OLE DB대상] 에서 마우스 오른쪽 클릭 [편집]을 선택하여 [데이터 액세스 모드]는 [테이블 또는 뷰 – 빠른 로드] 를 선택 합니다. [테이블 또는 뷰 이름] 에서는 [dbo].[tbl_20110701]을 선택합니다.

9. 녹색 선을 연결하고실행을 선택 합니다. 원본테이블에서 대상테이블로 데이터가 복사된 것을 확인 할 수 있습니다.

변수를 이용하여 특정 테이블 복사하기

1. 앞의 실습까지 따라 하였으면 다시 [제어흐름] 탭으로 이동합니다. 그리고 [도구 상자]에서 [스크립트 태스크]를 마우스로 끌어다 놓습니다. [편집]을 선택합니다.

2. 사용할 변수명을 적고 변수선택 버튼을 클릭하면 어떤 형식의 변수를 사용할건지 선택합니다.

3. 변수선택이 끝났으면 [스크립트 편집] 버튼을 클릭합니다.

4. 스크립트 편집을 클릭하면 새로운 SSIS창이 나타납니다. 여기서 프로그램을 입력합니다.

아래의 그림처럼 따라서 입력합니다. 그리고 저장. 창을 닫습니다.

5. [제어흐름] 탭에서 [OLE DB원본] 에서 [편집]을 선택 합니다. [데이터 액세스 모드] 에서 [테이블 이름 또는 뷰 이름 변수]를 선택 합니다. 그리고 변수이름을 지정합니다

6. 이미 대상 테이블에 데이터가 있다면 테이터를 삭제한 후SSIS 패키지를 실행하여 데이터가 정상적으로 입력되었는지 확인 합니다.

위의 방법은 일자별또는 테이블 이름이 바뀌는 데이터를 가져올 때 특정 변수를 입력하여 해당 테이블을 가져올 때 매우 유용할 듯 합니다. 필자는 실제로 날짜로 테이블명이 생성되는 테이블테 가져올 때 유용하게 사용하고 있습니다.

위의 방법 둘다 로그를 쌓지 않고 가져오기 때문에 매우 빠른 속도로 데이터를 가져오는 것을 확인 할 수 있습니다.

많이 활용 하시길 바랍니다.

강성욱 / http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp