패키지 실행 시 매개변수 동적으로 주기

Microsoft Premier Field Engineer

한대성

 

 

 Question
job agent에서 패키지(SSIS)를 호출하여 매일 실행시키는 작업을 추가하였습니다.
근데 SSIS에서 매개변수를 받는것이 있거든요.
그 매개변수는 매일 실행하는 월.일(20090817, 200908) 입니다.
이런 매개변수값을 agent등록시 어떻게 지정하여 넣어주어야 하나요?
값 넣는 것은
\package.Variables[User::YYYYYMMDD].Value : 임의의값
이란 것을 알고 있지만 임의의 값이 아닌 동적인 값을 넘기고 싶습니다.


1. SSIS 패키지 내에서 날짜에 대한 값 지정하기

일반적인 방법으로, 입력되는 날짜가 없는 경우에는 자동으로 오늘 날짜 및 이번 달을 지정하도록 만드는 것입니다.

a.     스크립트 작업을 패키지의 가장 처음에 실행될 수 있도록 추가합니다.

b.     스크립트 작업의 ReadWriteVariables 에 User::YYYYMMDD,User:YYYYMM 변수를 입력합니다.

 

c.     다음과 같은 스크립트를 입력합니다. (Main() 안에)

public void Main()

        {

 

            if (Dts.Variables["YYYYMMDD"].Value.ToString() == "")

            {

                Dts.Variables["YYYYMMDD"].Value = DateTime.Today.ToString("yyyyMMdd");

                Dts.Variables["YYYYMM"].Value = DateTime.Today.ToString("yyyyMM");

            }

            Dts.TaskResult = (int)ScriptResults.Success;

        }


 

d.     간단히 각 변수의 값을 출력하는 작업을 하나 추가하고 패키지를 테스트 하면 다음과 같이 오늘 날짜 및 이번 달이 변수 값으로 지정됩니다.

 

e.      SQL Agent에서는 오늘 날짜로 설정할 경우에는 \package.Variables[User::YYYYMMDD].Value의 속성에 빈 값을 넣으면 됩니다.

 

 

2. Shell Script를 이용하여 날짜 값 지정

만약 패키지를 수정할 수 없는 경우에는 다음과 같은 방식을 이용합니다.

a.     SQL Agent에서 실행할 패키지를 지정하고, YYYYMMDD, YYYYMM 변수에 임의의 값을 지정한 후, 명령줄 탭에 나오는 명령줄 부분을 복사합니다.

         

b.     메모장에 위의 명령을 붙여 넣은 다음, 아래와 같이 날짜 정보를 얻어오는 Shell Script를 추가한 후 확장자를 cmd로 저장합니다.

echo off

 

REM Define the TODAY variable

REM Define the THISMONTH variable

 

REM 운영체제가 한글 버전인 경우

for /F "tokens=1-3 delims=/ " %%a in ('date /t') do SET TODAY=%%a%%b%%c

for /F "tokens=1-3 delims=/ " %%a in ('date /t') do SET THISMONTH=%%a%%b

 

REM 운영체제가 영문 버전인 경우 아래의 REM 제거

REM for /F "tokens=1-3 delims=/ " %%a in ('date /t') do SET TODAY=%%c%%a%%b

REM for /F "tokens=1-3 delims=/ " %%a in ('date /t') do SET THISMONTH=%%c%%a%

 

"D:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /FILE "D:\Temp\SSISProject\SSISProject\Package.dtsx" /CHECKPOINTING OFF /SET "\package.Variables[User::YYYYMMDD].Value";%TODAY% /SET "\package.Variables[User::YYYYMM].Value";%THISMONTH% /REPORTING E


주의할 것은 날짜를 가져오는 부분이 OS의 국가별 언어 설정에서 지정한 날짜 방식에 따라 약간 다릅니다. 만약 운영체제가 영문일 경우, 영문 버전에 맞게 수정하세요.

그리고, 가급적 패키지를 실행시키는 dtexec.exe는 전체 경로를 명확히 지정해 주세요. SQL 2005가 같이 설치되어 있을 경우, 단순히 dtexec.exe로 지정하면 SQL 2005 버전이 실행될 수도 있습니다.

 

c. 패키지를 테이블에 저장하도록 약간 수정한 후 테스트 해 보면 다음과 같이 정확히 날짜가 입력됩니다.

 



 


 

d.     다시 SQL Agent 작업으로 돌아가서, 작업 유형을 운영 체제(CmdExec)로 변경한 후, 아래와 같이 명령 부분에 위에서 생성한 파일을 지정합니다.


          

-끝-

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

FOR 루프 컨테이너를 이용한 대기 작업 구현  (0) 2011.08.27
비표준화된 입력 데이터에 대한 처리  (0) 2011.08.27
병렬로 쿼리 실행하기  (0) 2011.08.27
문자열의 코드 변환하기  (0) 2011.08.27
패키지 외부에서 매개 변수 값 지정하기  (0) 2011.08.27

병렬로 쿼리 실행하기

 

 

Microsoft Premier Field Engineer

한대성 

 

 Question

같은 프로시져를 동시에 여러 프로세스로 실행하고 싶습니다.  아이디어가 안 떠 오르는데, SSIS 로 할 수 있는 방법 없을까요?

방법1 )
declare @i int
set @i = 1

while ( @i < 100 ) 
begin
        EXEC  프로시져명  @i
        set @i = @i + 1

end

방법1)  처럼 실행하면 프로시져를 while 로 돌리면 1개 처리되고 다음것이 순차적으로 처리가 되는데,
순차적인 방법이 아닌 병렬로    exec  프로시져명 1  , exec  프로시져명 2 ,  exec 프로시져명 3   ....   exec  프로시져명 100  를  한번에 실행하고 싶은데 방법이 없을까요 ? 


같은 프로시저를 동시에 실행시킨다는 것은 간단해 보이지만 꽤 까다로운 주제입니다.

SQL 쿼리 실행 자체가 동기성 작업(Synchronous)이라, 쿼리 분석기이든 다른 어플리케이션이든 쿼리를 실행시키고 결과가 나올 때 까지 기달려야 하는 형태입니다. 이에 반해 SQL Agent의 작업과 같은 형태는 비동기성 작업(Asynchronous)이라 할 수 있습니다. 시작만 시켜놓고 종료 여부에 상관없이 다른 작업들을 할 수 있는 형태입니다.

 

SSIS를 이용해서 동시에 여러 작업을 수행하도록 하는 것을 어떻게든 구현해 봅시다.

어떻게 패키지를 구성할 것이냐. 10개 동시에 수행하는 것으로 단순화 해서 설명드리겠습니다.
 

10개의 SQL 실행 작업을 만들어놓고선, 각 실행 작업들이 수행할 변수를 지정합니다. 변수도 물론 10개가 되겠지요. 파일이든 테이블이든간에 어디선가 쿼리 목록을 읽어온 다음(1), 차례대로 이 변수들에 대입시켜주고선 SQL 실행 작업들을 실행시키는 것이지요.(2)

 

1.     쿼리 목록을 읽어와서 변수에 대입하기.

본 예에서는 다음과 같은 형태의 쿼리가 저장된 파일에서 쿼리를 읽어오도록 구성하겠습니다.

 

SSIS에서 QueryObj이라는 이름의 Object형 변수를 하나 추가한 다음, 아래와 같이 데이터 흐름 작업에서 RecordSet 대상을 이용하여 이 변수에 결과값을 저장하는 것을 구성합니다. RecordSet 대상 편집기에서 저장할 변수를 지정한 후, 입력 열 탭에서 저장되는 열을 선택하셔야 합니다.

 


 

 

이제 SSIS 변수에 Query1, Query2, .. Query10 이라는 10개의 String 형 변수를 추가합니다.


 

데이터 흐름 작업 뒤에 스크립트 작업을 연결한 후, 아래와 같이 변수들을 지정합니다.


 

스크립트 편집기를 연 다음 상단의 프로젝트(P) à 참조 추가(R)을 선택한 후, System.Xml.dll 을 참조로 추가합니다.

 

다음과 같은 스크립트를 입력합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

 

 

Public Class ScriptMain

 

             Public Sub Main()

            

        Dim oLead As New Data.OleDb.OleDbDataAdapter

        Dim dt As New Data.DataTable

        Dim dr As System.Data.DataRow

        Dim seq As Integer = 0

 

 

        oLead.Fill(dt, Dts.Variables("QueryObj").Value)

 

        For Each dr In dt.Rows

 

            seq += 1

            Dts.Variables("Query" & seq.ToString).Value = dr.Item(0).ToString

        Next

        Dts.TaskResult = Dts.Results.Success

 

    End Sub

End Class

 

 

 

2.     SQL 실행 작업 구성하기

이제 시퀀스 컨테이너를 스크립트 작업 뒤에 연결한 후, 시퀀스 컨테이너 안에 10개의 SQL 실행 작업을 추가합니다.

 

각각의 SQL 실행작업을 열어 실행할 DB 연결을 지정하고, 각 작업들의 속성 중 Expressions을 선택하여 아래와 같이 SQLStatementSource 속성에 대해 Query1 부터 Query10까지 지정합니다.

 

각 작업의 속성 중 DelayValidation 속성도 True로 바꿔줍니다. (필수 사항은 아닙니다.)

그런 다음, 패키지의 빈 곳을 선택한 후, 속성의 MaxConcurrentExecutables 값을 10으로 변경해 줍니다. 동시에 수행할 수 있는 작업의 수를 10개로 하겠다라는 것입니다.

 

완성된 모습은 다음과 같습니다.

 

아래와 같은 테스트 프로시저를 이용하여 결과를 확인해 봅시다.

USE TEMPDB

GO

 

CREATE TABLE ResultValues

(

             seq int,

             insdate datetime default(getdate())

             )

GO

 

CREATE PROC TestProc(@seq int)

as

             INSERT ResultValues(seq) values(@seq)

       WAITFOR DELAY '00:00:01'

GO

 

 

10개의 SQL 실행 작업을 동시에 수행하라고는 하였지만, 위의 결과와 같이 약간의 차이가 존재하긴 합니다.

참고삼아 다음과 같이 SQL 쿼리를 이용하여 수행하였을 때의 결과를 비교해 봅시다.

 

만약, 반드시 동일한 시간에 동시에 수행해야 한다면?

SSIS 패키지에서 이를 제어하지 말고, 저장 프로시저를 약간 고쳐서 이를 해결해 봅시다.

 

ALTER PROC TestProc(@seq int, @WAITTIME DATETIME = NULL)

as

 

IF @WAITTIME IS NOT NULL

       WAITFOR TIME @WAITTIME

      

      

       INSERT ResultValues(seq) values(@seq)

       WAITFOR DELAY '00:00:01'

GO

 

그리고선, 실행 쿼리문도 다음과 같이 변경합니다.

EXEC dbo.TestProc 1,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 2,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 3,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 4,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 5,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 6,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 7,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 8,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 9,'2009-08-07 23:17:00.000'

EXEC dbo.TestProc 10,'2009-08-07 23:17:00.000'

 

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

비표준화된 입력 데이터에 대한 처리  (0) 2011.08.27
패키지 실행 시 매개변수 동적으로 주기  (0) 2011.08.27
문자열의 코드 변환하기  (0) 2011.08.27
패키지 외부에서 매개 변수 값 지정하기  (0) 2011.08.27
FTP 작업에서 여러 파일 보내기  (0) 2011.08.27

문자열의 코드 변환하기

 

Microsoft Premier Field Engineer

한대성

 

 Question
text 파일에 있는 데이터를 불러오는 과정에서 codepage와 관련하여 에러가 납니다.
원본 화일에 있는 데이터가 20127이라는 코드페이지로 되어 있는데요.
그래서 1251로 전환이 안되는 데이터가 있다고 에러가 납니다.
이런경우에는 어떻게 코드 페이지를 변환 해주어야 하는건가요?



일반적으로 열의 코드 페이지를 바꿀 때에는 다음과 같이 데이터 변환(Data Conversion)을 이용하면 됩니다.

 

 

하지만, 질문에서와 같이 일부 코드 간에는 이를 이용해서 처리할 수 없는 경우가 있습니다.

예) 원본 CodePage : 20127  변환 CodePage : 1252

 

이 경우에는 다음과 같이 스크립트 변환을 이용하여 처리할 수 있습니다.

 

다음과 같이 데이터 원본 뒤에 스크립트 변환을 추가합니다.


스크립트 변환의 입력 열 속성에서 변환하고자 하는 열을 선택합니다.

 

입/출력 탭에서 Output0 (또는 출력0 으로 나타남)을 확장한 다음, 변환되어 출력될 열을 추가합니다. 이 때 열의 유형을 원하는대로 설정합니다.


 

스크립트 탭에서 스크립트 편집기를 연 다음, 아래와 같은 코드를 입력합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

 

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim enc As New System.Text.ASCIIEncoding

        Dim Str1 As String, Str2 As String

        Str1 = enc.GetString(EncodeString(Row.AddressLine1.ToString, 1252))

        Str2 = enc.GetString(EncodeString(Row.AddressLine2.ToString, 1252))

 

        Row.ConvAddress1 = Str1

        Row.ConvAddress2 = Str2

 

    End Sub

 

    Function EncodeString(ByRef SourceData As String, ByRef CharSet As Integer) As Byte()

        Dim bSourceData As Byte() = System.Text.Encoding.Unicode.GetBytes(SourceData)

        Dim OutEncoding As System.Text.Encoding = System.Text.Encoding.GetEncoding(CharSet)

 

        Return System.Text.Encoding.Convert(System.Text.Encoding.Unicode, OutEncoding, bSourceData)

    End Function

 

 

End Class

 

 

 

 

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

패키지 실행 시 매개변수 동적으로 주기  (0) 2011.08.27
병렬로 쿼리 실행하기  (0) 2011.08.27
패키지 외부에서 매개 변수 값 지정하기  (0) 2011.08.27
FTP 작업에서 여러 파일 보내기  (0) 2011.08.27
패키지 자동으로 재 실행하도록 설정하기  (0) 2011.08.27

패키지 외부에서 매개 변수 값 지정하기

 

Microsoft Premier Field Engineer

한대성

 

 Question

안녕하세요.
SSIS를 사용해서 여럿 패키지를 사용해서 돌리고 있는데요..
특정월을 선택해서 돌리고 싶은데, 변수값 적용이 잘 안되네요, TT
변수값을 동적으로 바꿔서 데이터구간을 바꿔가면서 데이터를 끌고 갈 수 없을까요??
yyymm between ? and ?   때에 따라서 데이터를 끌고오는 구간을 바꾸고 싶거든요..
(SQL실행작업 or OLE DB 원본 항목)
조언부탁드립니다. 수고하세요~


다음과 같은 스크립트로 예제 데이터를 생성합니다.

USE TEMPDB

GO

 

IF EXISTS(SELECT * FROM sysobjects WHERE name='tempdata')

        DROP TABLE tempdata

GO

 

CREATE TABLE tempdata

(

        inputdate datetime not null,

        col1 int not null default(rand()*100),

        col2 varchar(20) default('aaaaaaaaa')

)

GO

 

DECLARE @I AS DATETIME

SET @I = '2009-01-01'

WHILE(@I<'2010-01-01')

BEGIN

        INSERT tempdata(inputdate) VALUES(@I)

       

        SET @I = @I + 1

END

GO

 

 

SSIS 패키지에서 두 개의 DateTime 변수를 정의합니다. 이 때 기본값도 같이 입력하도록 합니다.

 

제어 흐름 영역에 데이터 흐름 작업을 추가하고 OLE DB 원본을 추가한 후, 다음과 같이 원본 쿼리를 설정합니다.

SELECT InputDate, Col1, Col2

FROM tempdata

WHERE InputDate BETWEEN ? AND ?

 

 

매개 변수(P) 버튼을 클릭한 후, FrodDate, ToDate 변수를 지정합니다.

 

데이터를 처리할 대상을 임의로 설정합니다. 본 예제에서는 Flat File 대상을 이용하도록 하겠습니다.

 

패키지를 실행시켜 봅니다.

 



 

이제 이 패키지를 이용해서 특정 일자를 입력하여 실행할 수 있는 방법을 구현토록 하겠습니다.

이 때 필요한 것은 패키지 내부의 개체 속성을 나타내는 문자열 값을 만드는 것입니다. 쉽게 말하자면, 본 예제 패키지에서 FromDate라는 패키지 변수 개체의 Value라는 속성을 나타내는 문자열 값을 찾아야 한다는 것입니다. 제가 자주 사용하는 방법은 다음과 같습니다.

 

패키지의 상단 메뉴 중 SSIS(S) à 패키지 구성(C)를 클릭하여 패키지 구성 도우미를 엽니다.

패키지 구성 설정 옵션을 체크한 후, 추가(A)를 눌러 패키지 구성 마법사를 실행합니다.

구성 유형에서 아무 것이나 선택하고 구성 파일도 아무 이름을 입력합니다. (중요치 않기 때문)


 

다음을 눌러 내보낼 속성 선택 창에서 FromDate 변수의 Value 속성과 ToDate 변수의 Value 속성을 체크합니다.


 

다음을 눌러 나타나는 창에서 아래와 같이 속성 부분에서 생성된 문자열을 복사하고 취소를 눌러 설정 작업을 취소시킵니다.


 

\Package.Variables[사용자::FromDate].Properties[Value]

\Package.Variables[사용자::ToDate].Properties[Value]

 

패키지 구성 설정 체크 한 것을 해제하고 닫기를 눌러 창을 닫습니다.

 

이제 패키지를 실행할 때 이 속성을 지정하는 방법을 구현해 봅시다.

패키지 파일(.dtsx)을 더블 클릭하거나 SQL Agent 작업에서 실행할 패키지를 지정합니다.


 

Set Values 탭에 위에서 구한 속성값 문자열을 입력하고 날짜를 입력합니다.

 

이 상태에서 Execute를 실행시켜도 되고, Command Line 탭에서 생성된 Command line 문자열을 이용하여 패키지를 실행시켜도 됩니다.

 

 



 

-끝-
저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

병렬로 쿼리 실행하기  (0) 2011.08.27
문자열의 코드 변환하기  (0) 2011.08.27
FTP 작업에서 여러 파일 보내기  (0) 2011.08.27
패키지 자동으로 재 실행하도록 설정하기  (0) 2011.08.27
DTS 마이그레이션 관련사항  (0) 2011.08.27
FTP 작업에서 여러 파일 보내기

Microsoft Premier Field Engineer

한대성





 Question

FTP작업실행으로 A서버에서 B서버로 파일 보내기를 하려고 합니다
FTP작업 편집기에서 LOCALPATH에서 새연결을 해서 선택 하면  사용유형에 파일명을 적게 되어 있는데
이곳에는 단 하나의 파일만 선택 할수  있게 되어 있습니다



하나의 파일을 선택한뒤 실행 하면 B서버로 잘 보내집니다.
문제는 여러개의 파일 즉..폴더에 있는 모든 파일을 보내고 싶습니다.
파일 시스템 작업자로 하게 되면 원격지대상의 폴더에  다른서버의 폴더로 지정을 할수 가 없습니다.
어떻게 하면 될까요?


다음과 같은 방법으로 여러 파일들 또는 디렉터리 전체에 있는 파일들을 보낼 수 있습니다.


a. String형 변수를 추가한 후, 보내고자 하는 파일 명을 적습니다.
   예) D:\Temp\ 폴더 내에 있는 확장자가 txt인 모든 파일을 보내고자 할 때
        변수명 : FileNames
        값       :  D:\\Temp\\*.txt
   

b. 다음과 같이 FTP 작업 속성에서 IsLocalPathVariable 속성을 True로 바꾸고 위에서 설정한 변수를 지정합니다.
  




저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

문자열의 코드 변환하기  (0) 2011.08.27
패키지 외부에서 매개 변수 값 지정하기  (0) 2011.08.27
패키지 자동으로 재 실행하도록 설정하기  (0) 2011.08.27
DTS 마이그레이션 관련사항  (0) 2011.08.27
조회 변환 및 OLE DB 명령 변환을 이용한 데이터 처리  (0) 2011.08.27

패키지 자동으로 재 실행하도록 설정하기

 

한대성

Microsoft Premier Field Engineer

 

 

 

 Question

다름이 아니라 SSIS에서 패키지 실패나 TASK 실패시 자동 재실행 설정방법이 있는지요?
패키지 실행시 재실행 방법은 있는것 같은데 모르겠고, TASK 실패 시 가능한지 조차도 모르겠습니다.

사유는 저희가 해외 사이트에서 추출할 일이 많은데, 네트워크 문제로 자주 끊어짐 현상이 발생합니다. 중간에 특정 해외 사이트 TASK 가 끊어질 때 다시 재실행 하는 방법이 있으면 좋겠네요. ^

 

 

 

우선 다음과 같이 패키지 내의 모든 작업 개체에 대해 FailPackageOnFailure 속성을 True로 설정합니다.

 

 

 

그리고, 데이터 흐름의 빈 영역을 선택한 후, 아래와 같이 검사점(CheckPoint)을 설정합니다.

 

검사점 설정과 관련되어서는 다음 글을 참고하세요.

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=456

 

이제, 부모 패키지를 구성합니다.

 

다음과 같이 CheckFileExist라는 변수를 추가하고 기본 값을 Y로 설정합시다. 또한 RetryCnt 라는 Int32형 변수를 추가하고 초기값을 0으로 설정합시다.

 

 

다음과 같이 For 루프 컨테이너를 추가하고 속성의 EvalExpression 부분에

           @@CheckFileExist == “Y” && @RetryCnt <=10 라는 식을 입력합시다.

 

 

 

이제 For 루프 컨테이너 내에 패키지 실행 작업을 추가하고 실행시킬 패키지를 지정합니다.



패키지 실행 작업을 선택한 후, ForceExecutionResult를 Success로 설정합니다. 실패가 나더라도 무조건 이 작업 개체(패키지 실행 작업)를 성공으로 처리하고자 하는 것이지요.

 

 

이제 스크립트 파일을 추가한 후, 다음과 같이 패키지 실행 작업과 연결합니다.


 

스크립트 작업의 ReadWriteVariable 속성에 CheckFileExist와 RetryCnt라는 변수를 지정하고,


 

다음과 같은 스크립트를 입력합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

 

 

Public Class ScriptMain

 

             ' The execution engine calls this method when the task executes.

             ' To access the object model, use the Dts object. Connections, variables, events,

             ' and logging features are available as static members of the Dts class.

             ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

             '

             ' To open Code and Text Editor Help, press F1.

             ' To open Object Browser, press Ctrl+Alt+J.

 

             Public Sub Main()

                           '

                           ' Add your code here

        '

        If File.Exists("D:\PackageErrorCheck.log") Then

            Dts.Variables("CheckFileExist").Value = "Y"

            Dts.Variables("RetryCnt").Value = CInt(Dts.Variables("RetryCnt").Value) + 1

 

Else

                Dts.Variables("CheckFileExist").Value = "N"

        End If

                           Dts.TaskResult = Dts.Results.Success

             End Sub

 

End Class

 

즉, 위에서 설정한 Check 파일이 있을 때에는(=에러가 발생했을 때에는) CheckFileExist의 값을 “Y”로 설정하고, RetryCnt 값을 1 증가시킵니다.

 

임의로 자식 패키지의 스크립트 작업 2를 실패로 처리하고 패키지를 수행하면 다음과 같이 10번 반복되는 결과를 볼 수 있습니다.

 

다시 정상적으로 해당 작업을 성공으로 설정하면 한 번만 수행됩니다.


저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

패키지 외부에서 매개 변수 값 지정하기  (0) 2011.08.27
FTP 작업에서 여러 파일 보내기  (0) 2011.08.27
DTS 마이그레이션 관련사항  (0) 2011.08.27
조회 변환 및 OLE DB 명령 변환을 이용한 데이터 처리  (0) 2011.08.27
구성 기능을 이용하여 패키지 연결 정보 관리하기  (0) 2011.08.27

DTS 패키지 마이그레이션 관련 Q&A 입니다.


Question 1)

--기존 sql2000 msdb에서 DTS 관리

select * from dtspackages

 

-- sql2005 MSdb에서 DTS 관리

select * from sysdtspackages

 

이렇게 관리가 되어진다고 들었습니다.

 

Answer) 맞습니다.

그런데, SQL 2005에서는 정확히

msdb.dbo.sysdtspackages는 레거시 부분에 저장되는 DTS 패키지가 저장되고

msdb.dbo.sysdtspackages90에 SSIS 패키지가 저장됩니다. (SQL Server로 저장하는 경우에)

 

 

 

Question2) 레거시에서 DTS파일을 가져오기 하면 바로 하단의 리스트가 생기잖아요.

수정 및 삭제도 가능한데 sql2000과는 별개라고 듣기 했는데  거기서 마이그레이션해서 dts파일을 dtsx파일로(ssis) 변환 해야만 사용 가능한 상태로 되는거죠. 그 리스트 자체를 (DTS파일) 작업 걸어서 사용 가능한 건가요?

 

Answer) 마이그레이션 하면 기존의 SQL Server 2000과는 별개인 것은 맞습니다. 앞에서 말한 SQL Server 2005의 sysdtspackages 테이블로 옮겨집니다.

실행할 수는 있습니다. SQL Server 2000과 같이 바로 실행할 수는 없고, SSIS 패키지에서 DTS 패키지 실행을 선택하든지,

           dtsrun /S krdshan /E /N DTSPackageName

과 같은 운영체제 명령(cmd 창에서 실행시키는) 으로 실행시킬 수 있습니다. 보다 자세한 옵션은 dtsrun /? 해서 참고하세요.

 

 

Question 3) 그리고 바로 마이그레이션을 하면 파일형태로 어디에 남지는 않고 시스템 테이블 안에 sysdtspackages90 바로 들어가지고 변경 시 SQL BIDS에서만 불어와서 변경 가능한건가여?

 

Answer) Management Studio의 레거시 부분에서 마이그레이션 하면 SQL Server DB에 저장됩니다. 이를 확인하려면 Management Studio로 Integration Service를 선택해서 연결하면 저장된 패키지가 나타납니다. 또한, 이 패키지를 수정하려면 BIDS에서 패키지 가져오기 식으로 읽어오는 수 밖에는 없습니다.

대신, BIDS 내에서도 패키지를 마이그레이션 할 수 있습니다. 이 경우에는 패키지를 파일로 바로 가져오기 때문에 수정이 더 용이합니다.


 

Question 4) 제가 패키지를 실행시켜서 네트워크 드라이브에 넣도록 하고 싶은데요, SQL 작업에서 만들어서 DTSX 파일 지정해서 실행시키는데 실패하던데 혹시 이유 하시는 분 계신가요?

 

Answer) 다음 글을 참고하세요.

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=3087

 




Question 5) 혹시 SQL BIDS은 그럼 수정하고 만들고 시스템 db안에 들어가 있어야 관리가 되나요??

 

Answer) 어떤 관리를 말하시는지? 패키지 관리라 하면, 기본적으로 일반 어플리케이션과 같이 파일 형태로 존재합니다. 즉, Visual Studios 솔루션 및 프로젝트 내의 패키지 파일로 만들어집니다. 굳이 SQL Server에 저장할 필요는 없습니다. 물론 저장하게 되면 Management Studio에서 관리, 정확히는 목록 및 실행 여부 관리 정도 할 수 있습니다.

 




Question 6) 음 데이터를 거의 전송하고 받는 일이 많아서여 2005로 바꿀려고 하는데 많이 모르겠네여 ^^ 제가 질문하고도 맞는건지 잘 헤깔리듯.

 

Answer) 질문은 맞는 것 같습니다.

 

 


Question 7) 위의 관련 문서나 싸이트 아심 알려주심 감사합니다 ^^


Answer) SQLLeader.com을 강추합니다.

 

 

 

Question 8) 혹시  시스템 msdb 테이블 안에 무엇 들어가는지 msdb이나 마이소프트쪽 가면 나와있나어?

 

Answer) 네. MSDN 같은데 찾아보시면 잘 나와 있습니다.
           그런데 마이소프트는 아니고 마이크로소프트입니다.



도움이 되셨길~

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

FTP 작업에서 여러 파일 보내기  (0) 2011.08.27
패키지 자동으로 재 실행하도록 설정하기  (0) 2011.08.27
조회 변환 및 OLE DB 명령 변환을 이용한 데이터 처리  (0) 2011.08.27
구성 기능을 이용하여 패키지 연결 정보 관리하기  (0) 2011.08.27
서버 간 데이터 동기화 구현  (0) 2011.08.27

조회 변환 및 OLE DB 명령 변환을 이용한 데이터 처리

 

 

한대성

 

  


 Question

구성하고 픈 작업은.....
Table1  테이블에서 Code 값 한개 받아와서 그 값을 조건으로 해서 Table2에서 결과를 뽑아
Table3에 insert하는 패키지를 구성하고 싶은데요...
Code 값을 받아와서 넘겨주는게 잘 안되네요.




간단히 다음과 같은 상황을 만들어 보죠.

TABLEA 에는 코드성 테이블이 있습니다. (예 : A, B, C, …)

TABLEB 에는 각 코드에 해당하는 데이터들이 있습니다.

TABLEA 에서 코드 값 하나를 읽어서 B에서 이 코드 값의 건수를 계산한 후, TABLEC에 저장하는 패키지를 만들어 봅시다.

 

 

USE TEMPDB

GO

DROP TABLE TABLEA

DROP TABLE TABLEB

DROP TABLE TABLEC

GO

 

CREATE TABLE TABLEA

(

                  SEQ INT IDENTITY,

                  COLID VARCHAR(10)

)

GO

 

INSERT TABLEA(COLID)

SELECT 'A' UNION ALL

SELECT 'B' UNION ALL

SELECT 'C'

GO

 

CREATE TABLE TABLEB

(

                  COLID VARCHAR(10),

                  RANDDATA INT DEFAULT(RAND()*100)

)

GO

 

INSERT TABLEB(COLID)

SELECT 'A' UNION ALL

SELECT 'A' UNION ALL

SELECT 'A' UNION ALL

SELECT 'B' UNION ALL

SELECT 'B' UNION ALL

SELECT 'C'

GO

 

CREATE TABLE TABLEC

(

                  COLID VARCHAR(10),

                  CNT INT

)

GO

 

 

 

다음과 같이 SSIS 패키지를 구성합니다. 데이터 흐름에서 TABLEA의 값을 읽어오는 OLE DB 데이터 원본을 구성합니다.

 


 

TABLEA에서 읽어온 코드 값으로 TABLEB에서 건수를 읽어오는 작업을 구현하기 위해 조회를 이용하는 방법부터 먼저 설명하겠습니다.

조회(Lookup) 변환을 추가한 후, OLE DB 원본(TABLEA)과 연결하고 속성 부분에 다음과 같은 쿼리를 입력합니다.





 

열 탭에서 CNT 부분을 체크하고,


 

고급 탭에서 메모리 제한 사용을 선택한 후, SQL 문 수정을 체크하고 매개 변수(P) 버튼을 클릭해서 COLID가 매개 변수로 지정되도록 선택합니다. 이렇게 설정하는 이유는 미리 전 COLID 열에 대해 집계를 하는 것이 아니라, 입력되는 COLID에 대해서만 집계 연산을 수행토록 하기 위함입니다.

 



 

확인을 눌러 편집기를 닫은 다음, OLE DB 대상을 추가해서 연결하고, 다음과 같이 COLID,와 CNT 열을 매핑 시킵니다.


 

실행해 봅시다.


 

 

 

 

두 번째 방법입니다.

 

다음과 같이 입력 값에 대해 연산을 수행하는 저장 프로시저를 만듭시다.

CREATE PROC USP_CALCCNT

                  @COLID VARCHAR(10)

AS

                  INSERT TABLEC(COLID, CNT)

                  SELECT @COLID,

                                   (SELECT COUNT(*) AS CNT FROM TABLEB WHERE COLID = @COLID)

GO

 

 

OLE DB 명령 변환을 추가한 후, OLE DB 원본(TABLEA)과 연결합니다.


 

OLE DB 명령 편집기를 연 다음, 연결 관리자 탭에서 저장 프로시저가 있는 DB 연결을 설정하고, 구성 요소 속성 탭에서 다음과 같은 명령을 입력합니다.


 

열 매핑 탭에서 다음과 같이 설정한 후, 확인을 눌러 편집기를 닫습니다.


 

실행해 봅시다. TABLEC의 결과는 동일합니다.



 

첫 번째 방법(LOOKUP)을 사용했을 경우에는, 만약 TABLEB에 COLID가 없는 경우(예 COLID=D) 에러가 발생합니다. 이를 방지하기 위해 에러 처리 작업을 해 줘야 합니다. (참고 : LOOKUP이 안되서 발생하는 에러이며, SQL 2008에서는 에러가 발생하지 않도록 변경되었습니다.) 대신, TABLEA, TABLEB, TABLEC가 각기 서로 다른 DB에 있는 경우에는 유용하겠지요.

두 번째 방법(OLE DB 명령)을 사용했을 경우에는, 동일한 인스턴스 내에 있는 테이블들에 대해서는 성능이 괜찮지만, 서로 다른 인스턴스에 있는 테이블들인 경우에는 성능이 문제가 될 수 있습니다.

 

이와 같은 간단한 예를 참고해서 변형해 보시기 바랍니다.  @.@

 

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

패키지 자동으로 재 실행하도록 설정하기  (0) 2011.08.27
DTS 마이그레이션 관련사항  (0) 2011.08.27
구성 기능을 이용하여 패키지 연결 정보 관리하기  (0) 2011.08.27
서버 간 데이터 동기화 구현  (0) 2011.08.27
이전 행의 값 저장하기 (스크립트 변환 이용)  (0) 2011.08.27

구성 기능을 이용하여 패키지 연결 정보 관리하기

 

한대성

 

 


 Question
전에 질문드리고 답변 내용보고 나름 구성파일도 건드려보고 XML도 만들어보고 했는데
답이 잘 안나오네요 ㅡㅜ
연결관리자에 등록된 서버가 변경된경우 작업한 모든 패키지를 열어서 수정해야하는데
이걸 깔끔하게 한방에 처리하고 싶습니다. >.,<



 

 

여러 패키지의 연결 정보를 하나로 관리하는 방법에는 몇 가지 있습니다. 이 중에서 패키지 구성(Configuration) 기능을 이용한 방법을 설명 드리겠습니다.

 

패키지 구성 기능은 패키지 내에 있는 여러 개체들, 즉 작업 태스크, 변수, 연결 정보 등의 속성을 설정해 주는 기능입니다. 이를 이용하여 연결 관리자에 있는 연결 정보만을 저장하는 파일로 만들고 이를 다른 패키지에서 가져다 쓰는 형태로 설정할 수 있습니다.

 

OLE DB 연결이 포함된 간단한 패키지를 하나 만듭시다. 이 연결의 이름을 DBConnA라고 변경합시다. 본 예제에서는 DBConnA라는 연결은 DSHAN이라는 서버의 tempdb를 가리키도록 설정해 놨습니다.

 

패키지에 dbname 이라는 string 변수를 하나 추가하고, SQL 실행 작업을 하나 추가한 후

 

                  SELECT db_name() as DBName

 

이라는 쿼리를 사용하여 현재의 데이터베이스 명을 출력하도록 설정합니다.

 

결과 집합 탭에서 결과를 변수에 저장하도록 설정합니다.

 

스크립트 작업을 추가한 후, ReadOnlyVariables 속성에 dbname 변수를 추가하고, 스크립트 편집기에서 다음과 같은 간단한 스크립트를 입력하여 dbconn 변수의 값을 출력하도록 설정합니다.

 

                  MsgBox(Dts.Variables("dbname").Value.ToString)

 

 

이제 구성을 설정해 봅시다. 상단 메뉴에서 SSIS(S) à 패키지 구성(C)를 선택해서 패키지 구성 도우미를 열고, 패키지 구성 설정(E) 옵션을 체크해서 패키지가 구성 기능을 사용하도록 설정합니다.

 

추가를 눌러서 패키지 구성 마법사를 실행시킨 후, 다음과 같이 구성 유형을 XML 구성 파일로 설정하고 적절한 구성 파일, 즉 연결 정보가 저장될 파일을 설정합니다.

 

현재는 이 파일이 없기 때문에 특별한 메시지 없이 이 파일에 담길 내용을 선택하는 창으로 넘어갑니다. 여러 속성들 중에서 연결 관리자 부분에 있는 DBCOnnA 연결에 대한 ConnectionString 속성을 선택하겠습니다.

 

다음을 누른 후, 적절한 구성 이름을 정해주면 설정이 끝입니다.

 

자, 그럼 패키지를 실행해 볼까요?

 

에러가 날 것입니다~! (만약 에러가 발생하지 않는다면, 연결 관리자에 있는 DB 연결이 윈도우 인증을 하도록 설정한 경우일 것입니다.)

 

왜 에러가 발생할까요? 구성을 설정하기 전에는 잘 되던 놈이. ㅡ,.ㅡ

 

패키지가 구성을 사용하도록 설정했다면, 패키지가 실행될 때 구성이 저장된 파일이나 테이블 등에서 지정된 속성 값을 읽어옵니다. 본 예제에서도 구성 파일을 만들었기 때문에 DBConnA의 연결 정보를 구성 파일인 D:\ConnectionString.dtsConfig 라는 파일에서 읽어오겠지요.

 

그런데 뭐가 문제일까요? 이 구성 파일을 열어봅시다. XML 형태로 저장하도록 설정되었기 때문에 다음과 같은 형태일 것입니다.

 

주저리 긴 내용인데, 줄 맞춰서 보면 위와 같이 연결 정보가 포함되어 있습니다. 그런데? Password 속성이 빠졌네요. 보안 때문에 자동으로 Password 정보는 빠지게 됩니다. 그래서 에러가 발생된 것이지요.

그래서 패키지의 에러 메시지도 다음과 같이 나타난 것입니다.

 

[연결 관리자 "DBConnA"] 오류: SSIS 오류 코드 DTS_E_OLEDBERROR.  OLE DB 오류가 발생했습니다. 오류 코드: 0x80040E4D. OLE DB 레코드를 사용할 수 있습니다. 원본: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  설명: "사용자 'sa'이(가) 로그인하지 못했습니다.".

 

자, 그럼 이 연결 정보에 Password = xxxxxx 구문을 추가한 후에 다시 패키지를 실행해 봅시다. 성공적으로 수행될 것입니다.

 

여기까지가 구성 파일을 만들고 이용하는 부분입니다.

 

이제 이 구성 파일을 여러 다른 패키지에서 사용하도록 설정해 봅시다.

다른 패키지에서 이 구성 파일, 정확히는 연결 정보가 포함된 구성 파일을 공유해서 사용하려면 패키지들의 연결 정보 이름이 동일해야 합니다. (@.@) 간단히 말해 다른 패키지에서도 DBConnA라는 OLE DB 연결이 있어야 한다는 것이지요. 구성 파일에서 연결 정보를 내려 받아서 적용할 때 이름으로 찾아가기 때문에 반드시 이름을 일치시켜줘야 한다는 말입니다.

 

다른 패키지를 하나 추가한 후, 간단히 구성해 봅시다. 앞의 패키지와 동일한 모습으로 otherpackage.dtsx 라는 이름으로 패키지를 만들었습니다. 자, 이제 이 패키지에서도 기존에 있는 구성 파일을 이용하도록 설정하겠습니다. 상단 메뉴의 SSIS à 패키지 구성 메뉴를 선택한 후, 앞에서와 같이 XML 구성 파일을 사용하도록 설정합니다. 이 때, 구성 파일 이름은 이미 만들어진 구성 파일을 선택합니다.


다음을 누르면 아래와 같은 경고 메시지가 나타납니다.


이 때, 기존 파일 다시 사용(R)을 선택합니다. 덮어쓰기를 선택했다간 기존 내용을 다 날리게 됩니다.

 

이미 구성 파일에는 password 부분이 추가되어 있기 때문에 패키지를 돌리더라도 성공적으로 실행됩니다.

 

 

테스트를 해 봅시다. 간단히, 데이터베이스를 tempdb에서 AdventureWorks로 바꾸도록 하겠습니다. 구성 파일의 연결 문자열 중 Initial Catalog=tempdb 부분을 Initial Catalog=AdventureWorks로만 바꾸면 되겠지요.

이렇게 한 다음, 앞서 만든 패키지와 새로 만든 패키지를 각각 돌려봅시다.


 

둘 다 바뀐 것을 확인할 수 있습니다.

 

참고로, 만약 구성 파일을 만들었다면 이 파일의 속성을 읽기 전용으로 설정할 것을 권고합니다. 패키지 구성 설정을 하다 보면 실수로 파일 덮어쓰기를 할 경우를 대비해서이지요.(^^)

보안이 걱정된다면, 이 구성 파일에 대한 보안 설정에서 읽을 수 있는 사용 권한을 패키지를 실행시키는 사용자(SQL Agent 서비스 시작 계정)과 패키지를 만드는 사용자(현재 패키지를 만들 때 로그인한 사용자) 정도만으로 설정하면 되겠지요.

 

그럼 도움이 되셨기를~^^

 

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

DTS 마이그레이션 관련사항  (0) 2011.08.27
조회 변환 및 OLE DB 명령 변환을 이용한 데이터 처리  (0) 2011.08.27
서버 간 데이터 동기화 구현  (0) 2011.08.27
이전 행의 값 저장하기 (스크립트 변환 이용)  (0) 2011.08.27
텍스트 데이터를 이용한 값 업데이트 하기  (0) 2011.08.27
서버 간 데이터 동기화 구현

백도훈

에이디컨설팅 선임 컨설턴트



 Question

다른 서버에 있는 테이블을 동기화하려고 합니다.
보안과 안정성 그리고 편의성을 다 만족하는 테이블 동기화 방법이 뭐 없을까요?

 

사내 정책상 제약 사항이 많은 경우 각기 다른 서버의 테이블 동기화를 유지해야 한다면 어떤 방법이 있을까요?

여러 방법이 있겠지만, 말 그대로 통합(integration) 서비스인 SSIS를 이용해보는 것도 좋을 것 같습니다.

 

코드 테이블에 동기화 상태 값을 저장할 컬럼과 트리거를 추가하여 재미있는 패키지를 구성할 수 있을 것 같네요. ^^;;

컬럼 추가가 불가능하다면 코드 테이블의 PK를 키 값으로 하는 별도의 테이블을 생성해서 관리하는 방법도 있을 것입니다.

 

이렇게만 설명하면 막연하니 간단한 예를 들어보겠습니다.

----------------------------

-- 1. 가상 기본 환경

----------------------------

USE TEMPDB

 

CREATE TABLE CODETABLE (

                  SEQ INT IDENTITY

                  , COL1 VARCHAR(10) DEFAULT('AAA')

                  , COL2 VARCHAR(10) DEFAULT('BBB')

                  , COL3 VARCHAR(10) DEFAULT('CCC')

)

GO

 

INSERT CODETABLE DEFAULT VALUES

GO 10

 

SELECT * FROM CODETABLE
 

 



현재 CODETABLE 테이블의 상태가 이렇다고 가정하겠습니다.

 

이 테이블에 컬럼과 트리거를 추가해보겠습니다.

--------------------------------------------

-- 2. 컬럼 추가 및 트리거 생성

--------------------------------------------

ALTER TABLE CODETABLE ADD STATUS CHAR(1) DEFAULT 'N' NOT NULL

GO

 

CREATE TRIGGER TR_CODETABLE

ON CODETABLE

FOR UPDATE

AS

                  IF (NOT UPDATE(STATUS))

                                   UPDATE CODETABLE

                                   SET STATUS = 'N'

                                   FROM CODETABLE AS A

                                   INNER JOIN

                                   INSERTED AS B

                                   ON A.SEQ = B.SEQ

GO

 

SELECT * FROM CODETABLE

 

------------------------------------------------------------

-- 3. 테스트를 위해 상태값을 Y로 업데이트

------------------------------------------------------------

UPDATE CODETABLE SET STATUS = 'Y'

 

SELECT * FROM CODETABLE



 

 

STATUS외의 컬럼 값이 UPDATE되거나 INSERT되면 다음과 같이 됩니다.

---------------------------------------------

-- 4. UPDATE 와 INSERT 테스트

---------------------------------------------

UPDATE CODETABLE SET COL1 = 'ZZZ' WHERE SEQ = 5

INSERT CODETABLE DEFAULT VALUES

 

SELECT * FROM CODETABLE




상태 값이 Y가 아닌 값이 동기화 대상이 되겠습니다.

 

 

SSIS 패키지 구현

 

SSIS로 테이블 동기화를 구현해보겠습니다.

환경은 원본 테이블이 있는 데이터베이스 A,

동기화 할 테이블이 있는 데이터베이스 B, C가 있다고 가정하겠습니다.

 

동기화는 A à B à C의 순서도 진행한다고 가정할 때 STATUS 값의 변화는 다음과 같습니다.

 

STATUS 값

A DB에 코드 입력 또는 변경

N

B DB 동기화 시작

B

C DB 동기화 시작

C

C DB 동기화 완료

Y

 

상태 값을 계속 변경해주는 것은 동기화 하는 도중 값이 또 변경되더라도 누락되는 행이 없도록 하기 위함이고

서버 별로 상태 값을 바꿔주는 것은 혹시나 동기화 대상 서버가 다운되더라도 어디까지 동기화를 진행했는지 알기 위해서 입니다.

 

일단 앞에서 테스트한 값을 초기화 시키기 위해서 원본 데이터베이스에서 아래의 쿼리를 실행합니다.

TRUNCATE TABLE CODETABLE

GO

 

INSERT CODETABLE DEFAULT VALUES

GO 10

 

SELECT * FROM CODETABLE



 

그리고 테이블을 동기화 할 데이터베이스 B, C에 아래의 쿼리를 실행해서 테이블과 프로시저를 생성합니다.

USE TEMPDB

 

CREATE TABLE CODETABLE (

                  SEQ INT IDENTITY

                  , COL1 VARCHAR(10) DEFAULT('AAA')

                  , COL2 VARCHAR(10) DEFAULT('BBB')

                  , COL3 VARCHAR(10) DEFAULT('CCC')

)

GO

 

CREATE PROC USP_CODEUPDATE

@SEQ INT,

@COL1 VARCHAR(10),

@COL2 VARCHAR(10),

@COL3 VARCHAR(10)

AS

IF EXISTS (SELECT 1 FROM CODETABLE WHERE SEQ = @SEQ)

                  UPDATE CODETABLE SET COL1 = @COL1, COL2 = @COL2, COL3 = @COL3 WHERE SEQ = @SEQ

ELSE

                  INSERT CODETABLE (COL1, COL2, COL3) VALUES (@COL1, @COL2, @COL3)

 

패키지를 만들어보겠습니다.

 

1.       연결 관리자에서 새 OLE DB 연결을 추가하겠습니다.
새로 만들기 버튼을 클릭해서 연결 관리자 창을 여시고 원본 DB 접속 정보를 입력합니다.
생성을 마치면 연결 이름을 SourceDB라고 변경하겠습니다.




2.       위의 1번과 마찬가지 방법으로 동기화 대상 DB를 위한 연결을 두 개 더 추가합니다.
추가 후 이름을 각각 TargetDB1, TargetDB2라고 변경해주겠습니다.





3.       첫 번째 대상 테이블을 동기화 해주기 위해 원본 테이블의 상태 값을 바꿔주겠습니다.
SourceDB에는 위에서 만든 CODETABLE이 있어야 합니다.
SQL 실행 작업을 추가하고 이름을 상태 값 변경 (N->B)으로 변경하겠습니다.
Connection 속성은 SourceDB로 지정하고 SQLStatement 속성에 다음의 쿼리를 입력하겠습니다.

UPDATE CODETABLE SET STATUS = 'B' WHERE STATUS = 'N'




4.       데이터 흐름 작업을 추가하고 이름을 테이블 동기화 1으로 변경하겠습니다.
더블 클릭해서 데이터 흐름 디자인 창을 엽니다.
OLE DB 원본을 추가합니다.
OLE DB 원본을 더블 클릭해서 편집 창을 엽니다.
OLE DB 연결 관리자로 SourceDB를 지정하고 데이터 액세스 모드는 SQL 명령으로 선택합니다.
SQL 명령 텍스트로 아래의 쿼리를 입력합니다.

SELECT SEQ, COL1, COL2, COL3, STATUS FROM CODETABLE WHERE STATUS = 'B'



 

5.       OLE DB 명령을 추가합니다.
편집기를 열고 연결 관리자를 TargetDB1로 설정합니다.
구성 요소 속성 탭의 SqlCommand 속성에 아래의 쿼리를 입력합니다.

USP_CODEUPDATE ?, ?, ?, ?

 

열 매핑 탭에서 입력 열과 대상 열이 같은 이름으로 매핑이 되어 있는지 확인합니다.



사실 이 작업은 OLE DB 명령을 사용하기 보다 임시 테이블을 만들어 처리하는 방식이 좋을 수도 있습니다.
하지만 변경되는 건수가 적다면 굳이 매번 임시 테이블을 생성하지 않고 OLE DB 명령을 이용하는 것도 방법입니다.

이것으로 첫 번째 테이블의 동기화 부분이 구현되었습니다.
두 번째 테이블의 동기화는 첫 번째 테이블의 동기화와 구현은 거의 동일한데 쿼리에서 상태 값 변경과 대상 DB만 다릅니다.

6.       제어 흐름 디자인 창으로 돌아옵니다.
정석은 아니지만 작업이 비슷하니깐 잠시 사도를 걸어보겠습니다.
앞에서 만든 상태 값 변경 (N->B) 작업을 복사하고 같은 디자인 창에 붙여 넣기를 하겠습니다.
상태 값 변경 (N->B) 1이라는 작업이 복사되어 생성될 것 입니다.
이름을 상태 값 변경 (B->C)로 변경하고 테이블 동기화 1과 연결하겠습니다.
편집 창을 열어서 SQLStatement 속성의 쿼리를 살짝 바꿔주겠습니다.

UPDATE CODETABLE SET STATUS = 'C' WHERE STATUS = 'B'


속성 값만 조금 바뀌었을 뿐입니다.

 

7.       또 Copy & Paste를 하겠습니다. ^^;;;
테이블 동기화 1도 복사 & 붙여 넣기를 하면 테이블 동기화 1 1 이라는 작업이 생성될 것 입니다.
이름을 테이블 동기화 2로 변경하고 상태 값 변경 (B->C)와 연결해줍니다.



이런 모양이 되겠지요?

8.       더블 클릭해서 데이터 흐름 디자인 창을 열겠습니다.
OLE DB 원본과 OLE DB 명령이 이미 있지요?

OLE DB 원본의 편집 창을 열어서 SQL 명령 텍스트를 변경해주겠습니다.

SELECT SEQ, COL1, COL2, COL3, STATUS FROM CODETABLE WHERE STATUS = 'C'

 

9.       다음은 OLE DB 명령의 편집 창을 열겠습니다.
여기서는 연결 관리자만 TargetDB2로 설정해주면 됩니다.

10.    제어 흐름 디자인 창으로 돌아오겠습니다.
마지막으로 복사 & 붙여 넣기를 하겠습니다.;;;

상태 값 변경 (B->C) 작업을 복사해서 붙여 넣고 이름을 상태 값 변경 (C->Y)라고 변경하겠습니다.
테이블 동기화 2와 연결하고 편집 창을 열어 SQLStatement 속성에 아래의 쿼리를 입력합니다.

UPDATE CODETABLE SET STATUS = 'Y' WHERE STATUS = 'C'

 

11.    패키지가 완성 되었습니다.
현재 원본 DB의 CODETABLE에는 다음과 같은 데이터가 입력되어 있습니다.



또한 동기화 대상 테이블에는 아무 데이터가 없습니다.
F5를 눌러서 디버그 해보겠습니다.



정상적으로 동기화가 되었습니다.
그럼, 원본 DB 값을 UPDATE, INSERT 해보겠습니다.

UPDATE CODETABLE SET COL1 = 'QWERTY', COL2 = 'ASDFGH', COL3 = '12345' WHERE SEQ = 7

INSERT CODETABLE DEFAULT VALUES

INSERT CODETABLE (COL1, COL2, COL3) VALUES ('가나다', '123', 'ABC')

 

SELECT * FROM CODETABLE



패키지 실행 후 결과를 확인해보겠습니다.




12.    마지막으로 한가지 테스트를 더 해보겠습니다.
동기화 중 동기화 되려는 정보가 다시 변경이 된다면 어떻게 될까요?

이걸 테스트 하기 위해서 패키지 중간에 중단점을 추가하겠습니다.
첫 번째 테이블 동기화가 진행된 시점에 재 수정 이벤트가 발생했다고 가정하기 위해
테이블 동기화 1에서 오른쪽 마우스 클릭을 하고 중단점 편집을 선택하겠습니다.

OnPostExecute 이벤트를 받는 경우 중단에 체크하겠습니다.



자, 중단점이 생성됐기 때문에 테이블 동기화 1 옆에 빨간 점이 생겼을 것입니다.

그럼 테스트를 해볼까요?
먼저 원본 테이블을 UPDATE하겠습니다.

UPDATE CODETABLE SET COL1 = 'ZZZ' WHERE SEQ = 2

SELECT * FROM CODETABLE




패키지를 실행합니다.
그러면 중단점이 설정되었기 때문에 테이블 동기화 1만 실행하고 멈출 것 입니다.
패키지는 중지하지 말고 그대로 두겠습니다. 쿼리를 실행 후 계속 진행해야하니까요.
번거롭지만 여기까지 결과를 보겠습니다.
모두가 예상하신 대로,



상태 값을 통해 B DB서버의 동기화가 진행 중이라는 것과 실제로 TargetDB 1에만 동기화가 되었다는 것을 알 수 있습니다.
여기서 또 원본 DB에서 쿼리를 실행해 UPDATE를 해보겠습니다.

UPDATE CODETABLE SET COL1 = 'GOOD', COL2 = 'DAY' WHERE SEQ = 2

UPDATE CODETABLE SET COL1 = '12345', COL2 = '67', COL3 = '8' WHERE SEQ = 8

INSERT CODETABLE (COL1, COL2, COL3) VALUES ('LAST', 'TEST', 'DATA')

SELECT * FROM CODETABLE





자, 이제 멈춰있는 패키지를 계속 실행해보겠습니다.
F5 버튼을 다시 눌러서 진행하면 패키지는 무사히 완료되고 테이블은?



상태 값이 N으로 바뀌었기 때문에 동기화가 중단되었습니다.
대신 다음 동기화 주기 때 모두 동기화가 되겠지요.

데이터 흐름의 OLE DB 원본의 쿼리 조건을 조금 바꿔서 Y가 아닌 값은 모두 업데이트하는 모양으로 바꿔줘도 좋을 것 같네요.
그렇게 짰다면 TargetDB 2의 값은 동기화가 되어 있겠지요.

그 작업은 글을 읽으시는 분들께 맡겨보도록 하겠습니다. ^^;;;

남은 것은 Agent에 등록해서 정기적으로 실행되도록 하는 것인데요.
이 방법에 대해서는 기본 강좌에 올라와 있고 하니 게시물을 참고해주세요~

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

조회 변환 및 OLE DB 명령 변환을 이용한 데이터 처리  (0) 2011.08.27
구성 기능을 이용하여 패키지 연결 정보 관리하기  (0) 2011.08.27
이전 행의 값 저장하기 (스크립트 변환 이용)  (0) 2011.08.27
텍스트 데이터를 이용한 값 업데이트 하기  (0) 2011.08.27
SQL 쿼리와 SSIS 누가 더 빠른가  (0) 2011.08.27

이전 행의 값 저장하기 (스크립트 변환 이용)

 

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 





 Question
데이터를 가공할때 바로 전데이터의 정보를 이용해 값을 만들려고 합니다.
  1  A  50 
  2  B  100
위와 같은 데이터가 있다면
  1  A  50   0 
  2  B  100  50
과 같이 바로 전 데이터의 정보를 입력하고자 합니다.(B에서 A의 50을 기록하는 방식)
script에서 변수를 이용해 전 정보를 기록하고 다음 정보를 변수에 넣는 방법으로 했으나
실행하면 run time error로 변수에 input/write가 막혀있다는 메시지가 나옵니다.
데이터를 위와 같이 가공할 방법이 있나요?
 

다음과 같이 데이터 흐름 내에서 간단한 쿼리로 원본을 설정합니다.

SELECT 1 as Seq, 'A' as Col, 50 as Val

UNION ALL SELECT 2, 'B', 65

UNION ALL SELECT 3, 'B', 70

UNION ALL SELECT 4, 'B', 75

UNION ALL SELECT 5, 'B', 80

UNION ALL SELECT 6, 'B', 85

UNION ALL SELECT 7, 'B', 90

UNION ALL SELECT 8, 'B', 95

UNION ALL SELECT 9, 'B', 100

UNION ALL SELECT 10, 'B', 105

 

그런 다음, 스크립트 변환을 이 원본과 연결합니다.

 

스크립트 구성 요소를 열어서 아래와 같이 입력 열 중 스크립트 변환에서 이용할 열을 선택합니다.

 

입/출력 탭에서 아래와 같이 출력 부분에 새로운 열을 추가합니다. 본 예제에서는 prevVal이라 하겠습니다.


 

이제 스크립트 편집기(VSA)를 연 후, 아래와 같은 스크립트를 입력합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

 

 

    Dim prevVal As Integer

 

 

    Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)

 

        Row.prevVal = prevVal

        prevVal = Row.Val

 

        '너무 썰렁한가요?? 쩝. ㅡ,.ㅡ

 

    End Sub

 

    Public Sub New()

        prevVal = 0

 

    End Sub

End Class

 

 

 

스크립트 편집기를 닫고선 임의의 변환을 붙여서 데이터를 확인해 봅시다.

(데이터 뷰어를 통해서 가능합니다.)



저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

구성 기능을 이용하여 패키지 연결 정보 관리하기  (0) 2011.08.27
서버 간 데이터 동기화 구현  (0) 2011.08.27
텍스트 데이터를 이용한 값 업데이트 하기  (0) 2011.08.27
SQL 쿼리와 SSIS 누가 더 빠른가  (0) 2011.08.27
주기적 데이터 이관 작업 구현  (0) 2011.08.27

source.txt


 Question

이름 옆에 Y 라고 되어있는 사람의 no을 찾아 우리 테이블에도 update Y해야 되는 거거든요.
파일은 25000(명)줄정도 되고요. 테이블 row수는 150(명)정도로 훨씬 적습니다.
이걸 하루에 한번 배치로 돌리는데요.



우선 설명할 모든 테스트는 tempdb에서 작업하도록 하겠습니다.

------------------------------------------------------------------

--Query: 대상 테이블생성 및 가상데이터 입력

------------------------------------------------------------------

 

use tempdb

 

create table TB_Target (

idx int,

name varchar(10),

CheckYN char(1) )

go

 

insert TB_Target values (2, '이문세', 'N')

insert TB_Target values (3, '홍길동', 'N')

insert TB_Target values (5, '이로로', 'N')

 

select * from TB_Target


 

<결과>

 



대상 테이블과 데이터가 준비되었으니 SSIS에서 작업할 내용을 설명하겠습니다.

텍스트 파일의 데이터를 테이블에 반영하는 방법에는 여러 가지 솔루션이 있을 것 입니다.

여러 가지 솔루션 중에서 3가지 정도의 방안을 설명하겠습니다.

1. 임시 테이블을 이용한 처리
2. 임시 테이블을 사용하지 않고 OLE DB 명령을 이용한 직접 처리


첫 번째 방법...
첫 번째 방안에서는 가장 일반적으로 생각할 수 있는 방법으로 해결해보겠습니다.

 

큰 흐름은,

업데이트할 텍스트의 내용을 임시 테이블에 가져온다.

임시 테이블에 있는 값을 조회하여 대상테이블의 값을 변경한다.

입니다.

 

1.     ‘도구 상자 à 제어 흐름 항목 à SQL 실행 작업’을 하나 생성합니다.

2.     이후에 어떤 작업을 하는 것인지 확실히 알 수 있도록 이름을 바꿔줍니다.

‘임시 테이블 생성’이라고 변경하겠습니다.

 

<결과>

3.     임시 테이블 생성 항목을 더블 클릭하여 연결할 대상 DB와 쿼리를 지정하겠습니다.

먼저, Connection 속성을 클릭하여 <새 연결…>을 선택합니다.

 



OLE DB 연결 관리자 구성 창이 뜨면 새로 만들기를 클릭합니다.

 



연결 관리자 창이 뜨면 연결할 데이터 베이스의 정보를 입력합니다.

앞에서 환경을 가정했던 대로 연결할 데이터베이스는 tempdb로 설정합니다.

 



확인을 하고 나오면 SQL 실행 작업 편집기의 Connection 속성에 연결이 생성된 것을 확인할 수가 있습니다.

다음은 SQLStatement 속성을 클릭하여 아래의 쿼리를 입력합니다.

if not exists (select 1 from sys.sysobjects where name = 'Temp_Target')

create table Temp_Target (idx int)

 

 



확인을 클릭하여 제어 흐름 창으로 나옵니다.

그리고, ‘도구 상자 à 제어 흐름 항목 à 데이터 흐름 작업’을 생성하고 앞에서 작업한 ‘임시 테이블 생성’과 연결합니다. 데이터 흐름 작업의 이름은 ‘파일 읽기’라고 수정하겠습니다.

 


그리고 데이터 흐름 작업의 DelayValidation 속성을 True로 설정합니다. 이 속성에 대한 자세한 내용은 다른 강의를 참고하시면 될 것 같습니다. 앞에서 실행되는 작업에서 임시 테이블을 생성했고 그 임시 테이블에 텍스트 데이터를 입력할 것입니다. 그런데 패키지 실행 전 Validation 검사를 하면 그 임시 테이블이 없기 때문에 실행도 하기 전에 에러가 발생하기 때문에 패키지 실행이 되지 않습니다. 그것을 피하기 위해 DelayValidation 속성을 True로 설정하겠습니다.

 



데이터 흐름 작업(파일 읽기)을 더블 클릭해서 데이터 흐름 디자인 모드로 전환합니다.

‘도구 상자 à 데이터 흐름 원본 à 플랫 파일 원본’을 추가합니다. 이름은 ‘원본 파일’이라고 변경하겠습니다. 더블 클릭하여 ‘플랫 파일 원본 편집기’ 창을 띄워서 새로 만들기를 클릭합니다.

 



플랫 파일 연결 관리자 편집기 창이 뜨면 원본 파일을 선택해줍니다.

 



좌측의 열 메뉴를 선택합니다. 그러면 자동으로 열 매핑이 되고 미리 보기 창에서 매핑된 파일의 내용을 볼 수 있습니다.

 



그리고 고급 메뉴를 선택합니다. 일련 번호의 데이터 형식이 문자열로 설정되어 있는데 정수형으로 변경해줍니다. DataType 속성을 ‘부호 없는 4바이트 정수’로 설정해줍니다. 또 Name 속성을 ‘idx’로 변경해줍니다.

 



확인을 클릭하여 데이터 흐름 창으로 나오면 하단에 있는 연결 관리자에 지금까지 연결한 DB와 파일 연결에 대한 정보를 볼 수 있습니다.

DB연결 관리자는 ‘TargetDB’로 파일 연결 관리자는 ‘SourceFile’로 변경하겠습니다.

 



‘도구 상자 à 데이터 흐름 원본 à 조건부 분할’을 추가해서 앞에서 작업한 ‘원본 파일’과 연결해줍니다.

 


‘조건부 분할’을 더블 클릭해서 편집기를 열고 ‘열2’를 조건에 드래그하여 추가합니다.

조건 식을 [열 2] == “Y”로 입력하고 이름을 적절히 정해줍니다.

 



이제 텍스트 데이터를 임시 테이블에 입력하는 작업을 하겠습니다. 하지만 다음 작업을 하기 전에 임시 테이블을 한 번 생성해주겠습니다. 왜냐하면 입력할 대상 테이블(임시 테이블)을 지정해줘야 하는데 tempdb에 그 테이블이 없으면 테이블 목록에 보이지 않기 때문입니다.

 

다음의 쿼리를 DB에서 실행합니다.

 

use tempdb

if not exists (select 1 from sys.sysobjects where name = 'Temp_Target')

             create table Temp_Target (idx int)

 


다시 SSIS의 데이터 흐름 디자이너 모드로 돌아와서 ‘도구 상자 à 데이터 흐름 대상 à OLE DB 대상’을 추가하고 ‘조건부 분할’과 연결해줍니다.

입/출력 선택 창이 뜨는데 위에서 설정해준 업데이트 대상으로 설정해줍니다.

 



‘OLE DB 대상’을 더블 클릭하여 편집기 창을 띄웁니다.

OLE DB 연결 관리자는 앞에서 설정해둔 TargetDB로 지정하고 데이터 액세스 모드는 테이블 또는 뷰 – 빠른 로드로 지정합니다. 테이블 또는 뷰 이름에는 바로 위에서 임시로 생성한 Temp_Target테이블로 지정합니다.

 



좌측 메뉴에서 매핑을 클릭합니다. 앞에서 ‘데이터 변환’ 작업에서 idx라는 이름으로 컬럼을 생성해줬기 때문에 매핑 메뉴를 클릭하면 자동으로 열이 매핑됩니다.

 



확인을 클릭하여 데이터 흐름 디자이너로 나오면 다음과 같이 데이터 흐름 작업이 디자인 된 것을 확인할 수 있습니다.

 



상단의 제어 흐름 탭을 선택하여 제어 흐름 디자인 모드로 전환합니다.

제어 흐름 창에 ‘도구 상자 à 제어 흐름 항목 à SQL 실행 작업’을 추가합니다.

이름을 ‘업데이트 및 임시 테이블 삭제’로 변경합니다.

 



‘업데이트 및 임시 테이블 삭제’ 항목을 더블 클릭하고 Connection 속성은 TargetDB로 지정합니다. SQLStatement 속성은 아래의 쿼리를 입력합니다.


update dbo.TB_Target set CheckYN = 'Y' where idx in

(select * from tempdb.dbo.Temp_Target with (nolock))

 

if exists (select 1 from sys.sysobjects where name = 'Temp_Target')

drop table dbo.Temp_Target

 

 



확인을 클릭하여 편집기를 빠져나옵니다.

모든 작업의 작성이 완료되었습니다.

 

확인을 해보겠습니다.

위에서도 확인했지만 현재 대상 테이블에는 아래와 같은 데이터가 입력되어 있습니다.

 



패키지를 실행해봅니다. F5로 디버깅을 해줍니다. 정상적으로 작성이 됐다면 제어 흐름과 데이터 흐름에서 각각 아래와 같은 결과를 확인할 수 있습니다.

 



select * from TB_Target

쿼리를 실행하여 테이블의 데이터를 확인합니다.

 

 

 

두 번째 방법.

앞에서 설명한 첫 번째 방법에선 임시 테이블(Temp_Target)을 사용했었습니다. 두 번째 방법에선 이 임시 테이블을 사용하지 않는 방법을 생각해보겠습니다.

                 

앞에서 구현한 첫 번째 방법에서 조금 수정해보겠습니다.

 

<제어 흐름>

제어 흐름 디자인 창에서 과감히 ‘임시 테이블 생성’과 ‘업데이트 및 임시 테이블 삭제’를 삭제합니다.

제어 흐름 디자인 창에는 파일 읽기(데이터 흐름) Task 하나만 남았습니다.

 

<데이터 흐름>

데이터 흐름 디자인 창에서 마지막에 추가한 ‘OLE DB 대상’을 삭제합니다.

대신, ‘도구 상자 à 데이터 흐름 변환 à OLE DB 명령’을 추가하고 ‘조건부 분할’과 연결 시켜줍니다. 유형 1에서 했던 것 처럼 ‘입/출력 선택’ 창이 뜨면 출력을 ‘업데이트 대상’으로 설정해줍니다. ‘OLE DB 명령’을 더블 클릭해서 편집창을 띄웁니다. 연결 관리자를 TargetDB로 설정합니다.

 



구성 요소 속성 탭의 SqlCommand 속성에 쿼리를 입력합니다.

update TB_Target set checkYN = 'Y' where idx = ?

 



SqlCommand 속성에서 ? 매개변수를 주었기 때문에 열 매핑 탭에서 idx와 매핑된 것을 확인합니다.

 


데이터 흐름 디자인 창에는 아래와 같이 구성되었습니다.

 

완료되었습니다.

패키지를 실행하기 전에 앞에서 반영한 테이블의 값을 초기화 하겠습니다.

use tempdb

 

truncate table TB_Target

 

insert TB_Target values (2, '이문세', 'N')

insert TB_Target values (3, '홍길동', 'N')

insert TB_Target values (5, '이로로', 'N')

 

select * from TB_Target

 

F5로 디버깅을 해서 실행한 후 테이블을 확인해보면 첫 번째 방법과 동일한 결과를 볼 수 있습니다.

그럼 왜 간단한 이 방법을 먼저 설명하지 않고 조금 더 복잡하고 임시 테이블도 사용하는 방법을 먼저 설명했을까요?

데이터의 수에 따라서 부하와 속도에 차이가 있기 때문입니다. 두 번째 방법의 ‘OLE DB 명령’을 보시면 알겠지만 이 방법은 건 by 건으로 업데이트를 하는 커서 방식입니다. 데이터 수가 많을수록 부하도 커지고 속도는 느려집니다.


저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

서버 간 데이터 동기화 구현  (0) 2011.08.27
이전 행의 값 저장하기 (스크립트 변환 이용)  (0) 2011.08.27
SQL 쿼리와 SSIS 누가 더 빠른가  (0) 2011.08.27
주기적 데이터 이관 작업 구현  (0) 2011.08.27
FOR 루프 컨테이너를 이용한 무한 루프 구현하기  (0) 2011.08.27

SQL 쿼리와 SSIS 누가 더 빠른가?

 

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자


 

 Question
A01 A02 A03 .... A053 으로 되어있는 테이블을
A 01
VAl 값
A 02
VAL 값
..
A53
VAL 값
이런식으로 언피벗을 하려는데요.

데이터가 10개 이면 * 53으로 ROW가 생기는 상황입니다.
실제 데이터 건수는 500만건 정도 되니.. ROW가 굉장히 많이 생깁니다.

언피벗을 다시 가공해서 테이블에 적재해야하는데 언피벗을 할때 SSIS의 피벗해제와 SP에서 T-SQL의 UNPIVOT을 사용하는 것의 성능 차이가 얼마나 있는지 궁금하네요.




우선 SSIS 가 빠른지 DB 처리가 빠른지를 논(?)하기 전에 다음과 같은 사항이 먼저 고려됩니다.

   "동일한 인스턴스에서 처리하는 SQL 데이터이냐? 아니냐?"

표현이 좀 어색하긴 하지만, 다음과 같이 설명할 수 있습니다.

(이건 순전히 저의 주관적 & 경험적인 의견이긴 합니다만..)
만약, 수행하려는 작업이 DB 작업, 좀 더 정확히는 SQL DB의 데이터를 이용하는 작업이고, 대상 테이블 또한 동일한 서버에 있는 데이터베이스의 테이블인 경우에는, 가급적 SSIS 말고 쿼리로 처리하라는 것입니다.

간단한 예로, ADCServer라는 인스턴스에 DatabaseA, DatabaseB 라는 데이터베이스가 있고,
DatabaseA에 있는 테이블을 읽어서 정렬 및 집계, 변환을 한 다음, DatabaseB 테이블에 넣고자 한다면,

SSIS 패키지로 작업할 경우, 데이터 흐름 작업 내에 정렬, 집계, 열 추가와 같은 변환 작업이 있기 때문에 이를 이용하면 되겠네~라고 생각하고 데이터 흐름 작업으로 구성할 수도 있습니다.
구현은 잘 되고, 뽀다구도 나겠지만, 성능은 안좋습니다. DB에서 바로 처리될 수 있는 것을 다른 프로그램(=패키지)에서 읽어서 변환한 다음 다시 넣어주는 형태이기 때문입니다.


그럼 데이터 흐름을 쓰지 말아야 하는 것이냐~~.
앞에서도 말했듯이, 데이터 흐름 보다 저장 프로시저 또는 쿼리를 이용한 처리가 좋은 경우는 동일한 인스턴스에 있는 테이블 처리인 경우에 한합니다.
만 약 텍스트 파일이나 다른 이기종 데이터베이스에서 읽어와서 이를 처리하는 경우는 또 상황이 다르며, 같은 SQL Server이더라도 원격지에 있는 경우에도 다를 수 있습니다. 또한 멀티캐스트, 조건부 분할, 행 갯수, 오류 열 리디렉션 등과 같이 쿼리로는 구현 못하는 기능들을 이용하기 위해 데이터 흐름을 이용할 수도 있습니다.


잠깐 주제를 벗어나서..
어떤 경우에는 패키지라고 만든 후에 보면 전부 SQL 실행 작업들로만 구성되어 있는 경우가 종종 있습니다. 이걸 보고 패키지 참 못만들었다~(^^)라고 할 수는 없습니다. 모두 SQL 실행 작업들로만 구성되었어도 여러 잇점들이 많이 있습니다. 한 번 생각나는데로 주절대 보겠습니다.

1. 쿼리를 병렬 처리 할 수 있습니다.
       
 SSIS 패키지로 구성한다면 위와 같이 동시에 쿼리 2와 쿼리 3이 돌아가도록 구성할 수 있습니다. 저장 프로시저로 만든다면 순차적인 실행 밖에 안되겠지요.

2. 로깅 및 모니터링, 에러 핸들링을 이용한 관리도 용이합니다.
저 장 프로시저도 역시 몇 번째 줄에서 에러가 났다라고 메시지를 뿌리긴 하지만, SSIS의 로깅 기능을 이용한다면 훨씬 더 관리하기 용이합니다. 또한 에러 핸들링 기능을 이용해서 에러가 나면 어떤 작업(예를 들어 관리자한테 메일 보내라)들도 쉽게 구현 가능하지요.

3. 원격 서버에서 수행해도 성능에 큰 저하가 없다.
만약 저장 프로시저로 처리한다면, 해당 서버에 저장 프로시저를 만들어 놓고선 해당 서버에서 돌려야 합니다. 물론 Agent를 이용해서 원격지에서 이를 수행하도록 구현할 수는 있으나, 복잡합니다. 또한 원격지에서 RPC(Remote Procedure Call)을 이용해서도 호출할 수 있지만 썩 추천하지는 않지요.
또한 까다로운 서버 관리자가 절때 프로시저 못 만들게 한다면? 갑갑하지요...
SSIS 패키지로 만든다면 원격지에서 패키지를 수행하더라도, 로컬 DB에서 수행하는 방식대로 실행할 수 있습니다. 뭔 말이냐면..간단히 프로그램을 생각해 보면 웹 서버는 DB 서버와 다른 곳에 있더라도 프로그램에서 DB 연결을 맺고선 쿼리 등을 실행하듯이 SSIS 패키지도 패키지 내부에서 명령을 수행할 DB에 대해 연결을 맺고선 쿼리를 날리는 방식이라는 것이지요. 성능? 좋습니다.

4. 저장 프로시저보다 뽀다구(=폼, 모양새) 납니다.
Visual Studio를 경멸하는 분들도 있고, Window의 아이콘보다 Shell 환경의 검은 바탕의 흰 색 글자가 더 뽀다구 난다고 생각하는 고수(^^)분들도 계시겠지만, 쿼리로만 쫙 있는 것 보다 시각적으로 구성된 패키지가 더 보기좋은 경우도 있습니다..


쓰다보니 주제가 다른 곳으로 빠졌네요. 이제 질문에 답변 드리겠습니다.
SSIS 피벗 기능과 쿼리의 피벗 기능 중 누가 더 빠르냐면, 그때 그때 다릅니다.(ㅡ,.ㅡ)
우선 데이터 형태가 많은 영향을 미칠 것이고, 처리되는 빈도도 영향을 주겠지요.

그러면 동일한 데이터베이스가 아니고 다른데 있는 데이터인 경우라면 SSIS가 더 빠른가요? 그것도 상황따라 다릅니다.(욕 나오시죠?)
앞에서 원격 서버인 경우, SSIS가 더 낫다는 듯이 말하더니만 왠 소리냐.
원 격 데이터를 처리한다면 SSIS가 쿼리보다 더 나을 수는 있습니다. 그러나, 만약 원격 데이터 전체를 Bulk Loading으로 로컬 임시 테이블로 가져온 다음, 로컬에서 쿼리로 처리하는 것이 더 빠른 경우도 있다는 것이지요. 요놈은 SSIS와 쿼리를 둘 다 섞어서 쓰는 형태이겠지요?

그래서 제가 말씀드릴 답변은, 절대적으로 빠르고 느린 것은 말씀드릴 수 없고, 상황에 따라 적절한 방법을 찾는 것이 답이다 입니다.


월요일 아침부터 해괴한 글로인해 열 받게 해 드린 것은 아닌가 모르겠습니다만, 제가 패키지를 만들 때 고려하는 중요한 사항이고 격있는 말로는 편하게 쓸 수 없기에 이렇게 자유롭게 써봤습니다.

그럼..^^
저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

이전 행의 값 저장하기 (스크립트 변환 이용)  (0) 2011.08.27
텍스트 데이터를 이용한 값 업데이트 하기  (0) 2011.08.27
주기적 데이터 이관 작업 구현  (0) 2011.08.27
FOR 루프 컨테이너를 이용한 무한 루프 구현하기  (0) 2011.08.27
패키지 내의 연결 정보 읽어오기  (0) 2011.08.27

주기적 데이터 이관 작업 구현

 

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

 Question
다름이 아니라 대상에서의 테이블의 마지막 날짜를 변수로 지정하고 싶은데요..
대상에서의 마지막 날짜를 변수로 넣고 이날자를 이용해서 원본의 where 절에 넣어서 그 이후의 값을 대상으로 가져오고 싶은데요...

  

서로 다른 서버에 있는 데이터를 주기적으로 동기화하는 간단한 예제를 설명 드리겠습니다. 본 예제에서는 질문에서와는 달리, 날짜 대신 자동 증가하는 Seq 값을 이용해서 설명드리겠습니다.

 

다음과 같이 두 개의 테이블을 서로 다른 서버에 만듭니다. 하나는 원본 테이블이고, 다른 하나는 대상 테이블 입니다.

 

원본 서버

USE tempdb

GO

 

--원본 테이블

CREATE TABLE SourceTable

(

                  Seq INT identity,

                  DataCol1 int default(rand()*10000)

)

GO

 

--1000개의 기본 데이터 입력

INSERT SourceTable DEFAULT VALUES

GO 1000

 

 

대상 서버

USE tempdb

GO

 

--대상 테이블

CREATE TABLE TargetTable

(

                  Seq INT,

                  DataCol1 int

)

GO

 

 

대상 서버에 저장되어 있는 최종 Seq 값을 읽은 후, 원본 서버에서 이 값 이후의 데이터를 가져오는 패키지를 구현하겠습니다.

 

빈 패키지를 하나 연 다음, 원본 서버와 대상 서버에 대한 OLE DB 연결을 설정합니다.

 

 


변수 창에서 다음과 같이 maxSeq라는 이름의 Int32형 변수를 하나 추가합니다.

이 변수에는 대상 테이블로부터 최대 Seq값을 읽어와서 저장할 것입니다.


 

이제, 대상 테이블로부터 최대 Seq값을 읽어온 후, maxSeq 변수에 저장하는 작업을 구성하겠습니다. 제어 흐름 영역에 SQL 실행 작업을 하나 추가하고 다음과 같이 설정합니다.


 

SQLStatement에는 다음과 같은 쿼리를 입력하고, ResultSet을 단일 행으로 설정합니다.

SELECT ISNULL(MAX(Seq), 0) AS MaxSeq FROM TargetTable WITH (NOLOCK)

 

이제 결과 집합 탭에서 아래와 같이 설정합니다.


 

확인을 눌러 편집기 창을 닫고, 데이터 흐름 작업을 하나 추가해서 SQL 실행 작업과 연결합니다.


 

데이터 흐름 작업을 더블 클릭해서 데이터 흐름 영역을 연 다음, OLE DB 원본을 추가합니다.

추가한 OLE DB 원본의 편집기를 연 다음, 아래와 같이 설정합니다.


 

오른쪽에 있는 매개 변수(P) 버튼을 클릭해서 쿼리의 매개 변수를 지정합니다.


 

이제, 열 탭을 클릭해서 원본의 열을 확인한 후, 편집기를 닫습니다.

 

도구 상자에서 OLE DB 대상을 추가한 후, OLE DB 원본과 연결하고 편집기를 열어 다음과 같이 설정합니다.


 

매핑 탭을 클릭해서 저장될 열 매핑을 확인하고 편집기를 닫습니다.

 

자, 다 되었습니다. 패키지를 한 번 실행해 봅시다. 지금 현재는 원본에만 1000개의 데이터가 입력되어 있기 때문에 다음과 같이 1000개의 데이터가 대상 테이블로 이동하겠지요.


 

원본에다가 1,234개 행을 입력한 후에 패키지를 실행해 봅시다.




 

 

참고 사항

데이터를 이관하는 패키지를 간단히 만들어봤습니다. 본 패키지를 이용해서 확장할 수 있는 사항들에 대해서 추가 설명하겠습니다.

 

1) 로깅 기능 설정

- Seq가 얼마부터 몇 개의 행이 넘어갔는지를 별도의 테이블로 남기는 로깅 기능을 구현할 수 있겠지요. 데이터 흐름 작업에서 원본과 대상 사이에 행 개수 변환을 넣은 다음, 이관된 데이터 행 수를 저장하도록 설정합니다. 그런 다음, 작업의 가장 마지막 부분에 SQL 실행 작업을 추가해서 기록을 로그 테이블에 저장하도록 하면 됩니다. 좀 더 나가서는 전송되는 건을 모니터링 할 수도 있습니다. 이 때는 데이터 흐름 작업의 이벤트 처리기에서 구현해야 합니다.

 

2) 입력 쿼리

- 본 예제에서는 아주 간단하게 입력 쿼리에 매개 변수를 넘기는 식으로 구현하였지만, 쿼리가 조금만 복잡해지면 매개 변수가 제대로 설정되지 않습니다. (서브 쿼리 형태인 경우)

이럴 경우, 쿼리를 저장할 변수를 하나 만들고 식을 이용해서 쿼리를 만들거나 스크립트 작업을 이용해서 쿼리를 만든 다음, OLE DB 원본의 데이터 액세스 모드에서 변수를 사용한 SQL 명령으로 지정하면 됩니다. 또는, 저장 프로시저로 만든 다음 이를 사용하는 것도 좋은 방법입니다.
저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

텍스트 데이터를 이용한 값 업데이트 하기  (0) 2011.08.27
SQL 쿼리와 SSIS 누가 더 빠른가  (0) 2011.08.27
FOR 루프 컨테이너를 이용한 무한 루프 구현하기  (0) 2011.08.27
패키지 내의 연결 정보 읽어오기  (0) 2011.08.27
자식 패키지의 연결 정보 변경하기  (0) 2011.08.27

FOR 루프 컨테이너를 이용한 무한 루프 구현하기

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 


 Question
FOR 문을 이용해서 무한적으로 반복해서 실행하고 싶은 작업이 있습니다.
그러면서도 작업과 작업 사이에 대기시간을 5초 정도 주고 싶습니다.
즉, 매번 반복되면서 5초에 한번씩 A 라는 곳에서 select 해서 B 라는 곳으로 Insert 하는 작업이 필요합니다.
대기시간 주는 것은 여기 사이트에 예시도 있어서 가능 할 것 같은데 계속 실행되면서  select 하려면 어떻게 해야 할까요?



 

다음과 같이 For루프 컨테이너를 추가합니다.

 

 

루프 편집기에서 다른 조건은 설정할 필요 없이 EvalExpression 속성 값에 true를 입력합니다.

 

이렇게 설정하는 것은 쿼리에서

 

WHILE (1=1)

BEGIN

 …

END

 

와 비슷한 형태입니다.

 

이제, For 루프 컨테이너 안에 필요한 작업들을 설정해서 넣고선, 5초 동안 대기하는 것을 구현해야 합니다.

 

이전에 올린 글에 FOR 루프 컨테이너를 이용해서 일정 시간 동안 대기하는 것을 구현한 것이 있습니다. 하지만 이 방법은 쓰지 마시기 바랍니다. (CPU가 과도하게 높이 올라갑니다.)

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=523

 

대신, SQL 명령을 이용하는 것이 가장 바람직할 것 같습니다.

 

작업들을 구성한 후, 가장 마지막 부분에 SQL 실행 작업을 추가한 후,

 

DB 연결을 설정하고, 다음과 같은 간단한 명령을 사용합니다.

예) 5초 동안 대기를 해야 할 경우

                  WAITFOR DELAY ’00:00:05’

 

 

 

 

매 번 루핑 때 마다 마지막 단계에서 5초간 대기하는 무한 루프가 만들어졌습니다.


저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

SQL 쿼리와 SSIS 누가 더 빠른가  (0) 2011.08.27
주기적 데이터 이관 작업 구현  (0) 2011.08.27
패키지 내의 연결 정보 읽어오기  (0) 2011.08.27
자식 패키지의 연결 정보 변경하기  (0) 2011.08.27
스크립트 변환에서 오류 처리하기  (0) 2011.08.27

패키지 내의 연결 정보 읽어오기

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 



 Question

연결값을 하드코딩을 해서 자식패키지로 물려주는데요.
만약에 하드코딩이 아닌 부모 패키지에서 쓰고 있는 값을 그대로 자식 패키지로 물려준다고 할때, 커넥션 스트링 값이나 이니셜 카탈로그 값을 어떻게 변수 값으로 등록할수 있는지 그 방법을 알고 싶습니다.

 

 

패키지의 연결 정보를 읽어오는 방법에 대해 간단히 설명하겠습니다.

 

 


패키지에서 위와 같은 여러 연결이 있다고 할 때, 각 연결의 속성 정보 등을 다음과 같은 방법을 이용해서 읽어올 수 있습니다.

 

 

 

각 연결 정보의 ConnectionString 속성을 저장할 변수를 추가합니다.

 

 

제어 흐름 영역에 스크립트 작업을 추가한 후, ReadWriteVariables 속성에 위의 세 변수를 추가합니다.

 

 

VSA에 다음과 같은 스크립트를 입력합니다.

 

 

Imports System

Imports System.Data

Imports System.Math

Imports System.IO

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Data.SqlClient.SqlConnection

 

 

 

Public Class ScriptMain

 

 

 

                  Public Sub Main()

 

        ''''''''''''''''''''''''''''''''''''

        'ADO.NET 연결인 경우

        ''''''''''''''''''''''''''''''''''''

        Dim myDBConnection As SqlClient.SqlConnection

        myDBConnection = _

            DirectCast(Dts.Connections("ADONETConn").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

        MsgBox("ConnectionString : " & myDBConnection.ConnectionString, MsgBoxStyle.Information, "ADONETConn")

        MsgBox("InitialCatalog : " & myDBConnection.Database, MsgBoxStyle.Information, "ADONETConn")

 

        '변수에 저장

        Dts.Variables("ADONETConnStr").Value = myDBConnection.ConnectionString

 

 

        ''''''''''''''''''''''''''''''''''''

        'OLE DB 연결인 경우      

        ''''''''''''''''''''''''''''''''''''

        MsgBox("ConnectionString : " & Dts.Connections("DBServerConn").ConnectionString, MsgBoxStyle.Information, "ADONETConn")

 

        '변수에 저장

        Dts.Variables("DBConnStr").Value = Dts.Connections("DBServerConn").ConnectionString

 

 

        ''''''''''''''''''''''''''''''''''''

        'FIle 연결인 경우

        ''''''''''''''''''''''''''''''''''''

        Dim myFlatFileConnection As String

        myFlatFileConnection = _

            DirectCast(Dts.Connections("FlatFileConn").AcquireConnection(Dts.Transaction), String)

        MsgBox("ConnectionString : " & myFlatFileConnection, MsgBoxStyle.Information, "FlatFileConn")

 

        '변수에 저장
        Dts.Variables("FileStr").Value = myFlatFileConnection

 

 

        ''''''''''''''''''''''''''''''''''''

        'FIle 연결인 경우

        ''''''''''''''''''''''''''''''''''''

        MsgBox("ConnectionString : " & Dts.Connections("FTPConn").ConnectionString, MsgBoxStyle.Information, "FTPConn")

 

        '변수에 저장

        Dts.Variables("FTPStr").Value = Dts.Connections("FTPConn").ConnectionString

 

 

 

        Dts.TaskResult = Dts.Results.Success

                  End Sub

 

End Class

 

 

 

 

ADO.NET 연결 문자열

 

 

ADO.NET 연결 DB

 

 

 

OLE DB 연결 문자열

 

 

File 연결 문자열

 

 

FTP 연결 문자열



저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

주기적 데이터 이관 작업 구현  (0) 2011.08.27
FOR 루프 컨테이너를 이용한 무한 루프 구현하기  (0) 2011.08.27
자식 패키지의 연결 정보 변경하기  (0) 2011.08.27
스크립트 변환에서 오류 처리하기  (0) 2011.08.27
[DTS] 데이터 로딩 과정에서 필터링 하기  (0) 2011.08.27

Package.zip


자식 패키지의 연결 정보 변경하기

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

 

 

 Question

Connection Managers 에 관련한 질문인에요.

예를 들어서 Sales 라는 패키지가 있는데요 이 패키지는 대략 4-5개의 서브 패키지를 호출합니다. 각각의 패키지들은 Connection Manager에 Source, DIL 이라는 연결이 만들어져있구요. 각각의 패키지들은 source라는 DB에서 data를 추출 DIL이라는 DB에 디멘젼이나 팩트 테이블을 형성합니다.
source와 DIL이라는 데이터베이스가 한개씩이 아니고 슈퍼마다 존재하는데요. 
일괄적으로 커넥션을 스위치 해줄수 있는 방법이 없을까해서 문의드립니다.

 

 

우선, 다음과 같이 자식 패키지를 간단히 만들겠습니다.

 

 

1.     테스트 확인을 위한 용도로 이용할 ServerName 이라는 문자열 변수를 추가합니다.

2.     연결 관리자에서 OLE DB 연결을 추가한 후, 이 연결의 이름을 DBConn 이라고 변경합니다.

3.     제어 흐름 영역에 SQL 실행 작업을 추가한 후, 다음과 같은 간단한 쿼리를 입력합니다.

 

SELECT @@ServerName AS ServerName

 

4.     SQL 실행 작업 편집기에서 ResultSet 속성을 단일 행으로 변경하고, 결과 집합 탭에 다음과 같이 결과를 SSIS 변수로 받아오는 설정을 합니다.

 

 



 

5.     이제 스크립트 작업을 추가한 후, SQL 실행 작업과 연결하고 ReadOnlyVariables 속성에 ServerName 이라는 변수를 추가합니다.

 

6.     VSA(Visual Studio for Application)을 열어서 다음과 같은 간단한 스크립트를 입력합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

 

Public Class ScriptMain

 

                 

 

                  Public Sub Main()

 

                            MsgBox(Dts.Variables("ServerName").Value.ToString)

 

                                   Dts.TaskResult = Dts.Results.Success

                  End Sub

 

End Class

 

 

완성되었으면 패키지를 실행해서 출력 결과를 확인해 봅시다.

 

 

이제부터 부모로부터 연결 정보를 받아오는 부분을 설정합시다.

Visual Studio의 상단에 있는 메뉴 중 SSIS(S) à 패키지 구성(C) 를 선택해서 구성 설정 창을 엽니다.

 

패키지 구성 설정(E)을 체크하고선 추가(A)를 클릭한 후, 다음과 같이 구성 유형을 부모 패키지 변수로 설정하고 부모 변수(P) 부분에 부모 패키지의 변수 명을 설정합니다. 본 예제에서는 간단히 DBConn 이라 설정하겠습니다.

 


이제 다음(N)을 눌러 부모 변수로부터 넘겨받은 값을 지정할 속성을 설정하도록 합니다. 어디에 설정하면 될까요?

아래와 같이 DBConn 연결의 ConnectionString에 적용되도록 합니다.


 

다음(N)을 눌러 이 구성에 대한 이름을 적절히 지정해주고선 구성 설정 작업을 완료합니다.

 

 

이제, 부모 패키지를 만들어봅시다.

빈 패키지를 추가한 후, 자식에게 넘겨줄 변수인 DBConn 이라는 문자형 변수를 추가합니다.

이 변수에 여러 값을 넘기도록 설정을 하겠습니다. Foreach 루프 컨테이너의 여러 열거자(Enumerator) 유형을 이용해서 구현할 수 있겠지요. 본 예제에서는 Foreach 루프 컨테이너의 Item 열거자(Enumerator)를 이용해서 간단히 직접 서버 정보를 입력하는 형태로 구성하도록 하겠습니다. 제어 흐름 영역에 Foreach 루프 컨테이너를 하나 추가한 후, 다음과 같이 설정합니다.


 

변수 매핑 탭에서 이 열거 값들을 저장할 변수를 설정해 줍니다.


 

확인을 눌러 편집기를 닫은 후, Forach 루프 컨테이너 내에 패키지 실행 작업을 추가하고 위에서 만든 자식 패키지를 실행하도록 설정합니다.


 

실행해 봅시다!!

 

(첫 번째 루프)

 

 

(두 번째 루프)


 

(세 번째 루프)


 

 

본 예제에서는 DB 서버의 연결 전체를 변경하도록 설정하였습니다. 만약 DB 서버는 동일한데 DB 명만 다르다면 구성(Configuration) 설정에서 ConnectionString 속성 대신 InitialCatalog를 변경하도록 설정하면 됩니다.


hosang yun 도와주셔서 감사합니다.
읽다보니 좀 궁금한 점이 있는데요.
제 가 예제를 따라할때는 연결설정이 없이 하다보니 패키지 구성 설정에서 부모로부터 물려받은 변수 값을 지정하는 부분에서 DBConn이라는 연결설정이 안보이더라구요. 첫 SQL 작업 편집기를 보면 이미 패키지 안에 DBConn이라는 커넥션이 존재하는 데요. 그 DBConn를 어떻게 만들어줘야하는건지 궁금합니다. OLEDB, FlatFile, ADO.NET등등 여러가지 설정중에 어떤것을 써야하는건지. 어떤식으로 초기화를 시켜줘야하는건지. 궁금합니다.		 2008/03/17

한대성 "2. 연결 관리자에서 OLE DB 연결을 추가한 후, 이 연결의 이름을 DBConn 이라고 변경합니다."
다음과 같이 OLE DB 연결 설정을 하고 이름을 DBConn이라고 하라는 부분 있는데요...^^		 2008/03/17

hosang yun 앗... 죄송합니다.. 정신없이 보다가 한줄 미스했네요..
덕분에 DB서버 이름과 DB 바꾸는 건 어느정도 감을 잡은거 같네요. ^^;;
한 가지 더 궁금한게 있는데요. 예제에서는 연결값을 하드코딩을 해서 자식패키지로 물려주는데요. 만약에 하드코딩이 아닌 부모 패키지에서 쓰고 있는 값을 그대로 자식 패키지로 물려준다고 할때, 커넥션 스트링 값이나 이니셜 카탈로그 값을 어떻게 변수 값으로 등록할수 있는지 그 방법을 알고 싶습니다.		 2008/03/18

한대성 다음 글을 참고하세요~
http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=1983		 2008/03/18

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

FOR 루프 컨테이너를 이용한 무한 루프 구현하기  (0) 2011.08.27
패키지 내의 연결 정보 읽어오기  (0) 2011.08.27
스크립트 변환에서 오류 처리하기  (0) 2011.08.27
[DTS] 데이터 로딩 과정에서 필터링 하기  (0) 2011.08.27
동일한 구조의 테이블로 결과 보내기  (0) 2011.08.27

스크립트 변환에서 오류 처리하기

 

한대성

 

 

 Question
문의드리고 싶은 것은 스크립트 변환 에서 오류가 발생하였을 경우,
그 원본 행을  DB의 다른 테이블에 저장시켜두고 에러메시지도 같이 포함해두고 싶습니다.

원 본은  csv 파일이고, 스크립트 변환에서 일부 문자열 파싱 작업이후 DB에 넣는데, 구조적이지 않은 문자열이라서 예외가 상당히많아서 에러가 발생하면 처리하던 원본행을 db에 넣고, 기존 작업은 그대로 진행시키는 방식으로 구현하려 합니다. (차후에 에러난것만 다시 확인해서 처리)

(참고로 다수의 원본파일을 처리해야해서 foreach 루프컨테이너 안에 데이터 변환작업으로 처리하고 있습니다.)

oledb 나 조회변환은 오류처리가 쉽게 되는데, 스크립트 변환은 어찌 처리해야될지 모르겠습니다;;


 

다음과 같은 입력 데이터에 대해 스크립트 변환에서의 오류 처리를 구현해 보겠습니다.

 

스크립트 변환을 이용한연산 :

 

                  (OutputCol) = 120 / (InputCol)

 

 입력 데이터 :

InputCol

구분

1

정상

2

정상

3

정상

aaa

오류 : 숫자 데이터여야 하는데 문자

0

연산 과정에서 0으로 나누기 오류

4

정상

5

정상

 

 

다음과 같이 데이터 흐름에서 간단한 원본 데이터를 만듭니다. 

 


원본에 대해 스크립트 변환을 추가한 후 연결합니다.


 

스크립트 변환 편집기의 입력 열 탭에서 입력에 사용할 열을 선택한 후, 입/출력 탭에서 다음과 같이 작업을 설정합니다. 


 

①    출력 0 부분에 OutputCol 이라는 4바이트 정수형 열을 추가합니다.

②    출력 0 을 선택한 상태에서 ExclusionGroup 값을 0 이외의 값을 설정합니다.

③    SynchronousInputID 의 값이 기본 값인 입력 “입력 0”(XX) 으로 설정되었는지 확인합니다.

 

 

이제, 오류 데이터를 출력하는 것을 구현합니다.

아래에 있는 출력 추가(A) 버튼을 클릭해서 출력을 추가한 후 Error로 이름을 변경합니다. (안해도 됩니다.^^)


 

①    이 출력에 대해서도 위와 같이 ExclusionGroup의 값을 0 이외의 다른 값(앞에서 설정한 것과도 다른 값)으로 설정하고, SynchronousInputID의 값도 위와 같이 설정합니다.

②    출력 열 부분에 ErrorDesc라는 이름의 문자형 변수를 추가합니다. (적절한 길이로 설정합니다.)

 

 

 

이제 스크립트 탭에서 스크립트 디자인(S)을 클릭해서 VSA를 연 후, 다음과 같은 스크립트를 입력합니다.

  

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

 

    Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)

 

        Dim Output As Integer 

        Try

            Output = CInt(120 / CInt(Row.InputCol))

 

            ' 정상인 경우 출력하는 부분

            Row.OutputCol = Output  ' 정상 출력 결과

            Row.DirectRowTo출력0()

 

        Catch ex As Exception

            ' 에러인 경우 출력하는 부분

            Row.ErrorDesc = ex.Message ' 에러 메시지

            Row.DirectRowToError()

 

        End Try

 

    End Sub 

 

End Class

 

 

자, 이제 스크립트 변환에 대상이나 다른 변환을 추가할 경우, 하나의 녹색 선이 아닌 두 개의 경로 중 선택하는 창이 나타납니다. (조건부 분할과 유사함)

이를 다음과 같이 대충 구성한 다음 처리 결과를 확인해 봅시다.

 


 

* 입력 데이터


 

* 정상 처리된 데이터


 

* 오류 처리된 데이터 및 에러 원인 데이터



저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

패키지 내의 연결 정보 읽어오기  (0) 2011.08.27
자식 패키지의 연결 정보 변경하기  (0) 2011.08.27
[DTS] 데이터 로딩 과정에서 필터링 하기  (0) 2011.08.27
동일한 구조의 테이블로 결과 보내기  (0) 2011.08.27
FTP 작업에서 파일 없는 경우 처리 방법  (0) 2011.08.27

[DTS] 데이터 로딩 과정에서 필터링 하기

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

 

Question
현재 CSV파일을 DTS를 이용해서 디비에 저장하고있는데요.
이 CSV파일이 몇만건 되다보니까 부담스러워서 CSV파일 속의 데이터에 조건을 걸어서 걸러내고 디비에 넣고 싶은데요.
엑셀 파일은 쿼리가 바로 먹혀서 셀렉트 문으로 걸러서 저장했는데 CSV는 바로 걸러내기가 불가능한가요? 디비에 저장하고 거기서 다시 걸러내는 방법 밖에는 없는지...

다음과 같은 예제 CSV 파일 중, DataGroup 열의 값이 D인 데이터만 테이블에 넣는 것을 SQL 2000 DTS 패키지로 구현하는 간단한 예를 설명 드리겠습니다.


 

전체 파일을 임시 테이블에 넣고선 DataGroup 열의 값을 비교해서 해당되는 데이터만 넣을 수도 있겠지만, 다음과 같이 넣는 과정에서 바로 필터링을 해서 넣을 수도 있습니다.

 

 

우선, 다음과 같이 CSV 파일에서 대상 테이블(본 예제에서는 tempdb.dbo.Sample)에 넣는 형태로 패키지를 구성합니다.


 

데이터 변환 작업(검은색 선)을 더블 클릭해서 연 다음 원본, 대상, 변환 탭을 클릭해서 적절히 열을 매핑시킵니다. 이 때 이름을 이용해서 자동으로 매핑하게 됩니다.


 

변환 탭의 아래에 있는 모두 선택(S) 버튼을 클릭해서 열 들을 모두 선택한 후, 모두 삭제(A)를 선택해서 현재 자동으로 설정된 연결들을 제거합니다. 그런 다음, (모두 선택되어 있는 상태에서)중간에 있는 새로 만들기(W) 버튼을 클릭한 후, 새 변환 만들기 창에서 ActiveX Script를 선택합니다.


 

이어서 나타나는 변환 옵션 창에서 속성(P) 버튼을 클릭하면 자동으로 현재 변환에 대한 ActiveX Script가 생성되며 스크립트를 이용할 수 있는 창이 나타납니다.

 

여기서 다음과 같이 스크립트를 변경합니다.

 

[기존]

'**********************************************************************

'  Visual Basic Transformation Script

'************************************************************************

 

'  각 원본 열을 대상 열로 복사합니다.

Function Main()

                  DTSDestination("Val") = DTSSource("Val")

                  DTSDestination("DataGroup") = DTSSource("DataGroup")

                  DTSDestination("Seq") = DTSSource("Seq")

                  Main = DTSTransformStat_OK

End Function

 

[변경]

'**********************************************************************

'  Visual Basic Transformation Script

'************************************************************************

 

'  각 원본 열을 대상 열로 복사합니다.

Function Main()

 

                 

                  DTSDestination("Val") = DTSSource("Val")

                  IF DTSSource("DataGroup") = "D" THEN

                                   DTSDestination("DataGroup") = DTSSource("DataGroup")

                  ELSE

                                   Main = DTSTransformStat_SkipRow

                                   Exit Function

                  END IF

 

                  DTSDestination("Seq") = DTSSource("Seq")

                  Main = DTSTransformStat_OK

End Function

 

 

이와 같이 변경한 후, 스크립트 편집 창을 닫으면 다음과 같이 변환의 모양이 바뀝니다.


 

자, 테스트 해 봅시다.


 

전체 데이터인 10000 건이 옮겨진 것처럼 보입니다. 하지만 실제 대상 테이블에는 아래와 같이 DataGroup이 “D”인 데이터만 들어가 있습니다.


 


이와 같은 방식으로 DTS에서도 임시 테이블을 사용하지 않고 읽어오는 과정에서 대상을 필터링 또는 변환할 수 있습니다. 참고로, 이와 같은 부분이 바로 SQL 2005 SSIS의 데이터 흐름 작업 부분입니다.


GreatHuman 정말 감사합니다 많은 도움이되었습니다. 엑티브x 스크립트를 이용하는 방법으로 가능하군요~^^ 2008/02/18

GreatHuman //**********************************************************************
// Java Transformation Script
//************************************************************************

// 각 원본 열을 대상 열로 복사합니다.
function Main()
{
if DTSSource("JobCode1") = "160" then
DTSDestination("jobcode1") = DTSSource("JobCode1");
else
Main = DTSTransformStat_SkipRow
Exit Function
end if
DTSDestination("id_num") = DTSSource("AdId");
return(DTSTransformStat_OK);
}
이렇게 if문을 넣고 실행하면
7번째 줄에 ')' 가 필요하다는 오류가 생기는데요 sql2000을 쓰고있는데 if문을 빼면 실행되고 넣으면 오류가 생기네요..
오류 해결 방안이 없을까요..??		 2008/02/18

GreatHuman 위에 ')' 가 아니라 '(' 네요.. 2008/02/18

한대성 위의 글 부분에서의 스크립트를 JScript로 구현한다면 다음과 같습니다.

//**********************************************************************
// Java Transformation Script
//************************************************************************

// 각 원본 열을 대상 열로 복사합니다.
function Main()
{
if (DTSSource("DataGroup") == "D")
DTSDestination("DataGroup") = DTSSource("DataGroup");
else
{
return(DTSTransformStat_SkipRow);
exit(-1);
}

DTSDestination("Val") = DTSSource("Val");
DTSDestination("Seq") = DTSSource("Seq");
return(DTSTransformStat_OK);
}
 2008/02/18

GreatHuman 감사합니다^^ 2008/02/18

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

자식 패키지의 연결 정보 변경하기  (0) 2011.08.27
스크립트 변환에서 오류 처리하기  (0) 2011.08.27
동일한 구조의 테이블로 결과 보내기  (0) 2011.08.27
FTP 작업에서 파일 없는 경우 처리 방법  (0) 2011.08.27
스크립트 변환을 이용한 문자열 분리  (0) 2011.08.27

동일한 구조의 테이블로 결과 보내기

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자



Question
제가 궁금한 것은요... 동일한 구조를 가진 테이블이 여러 sql서버에 존재할 경우, foreach loop내에서 연결을 동적으로 바꾸면서 데이터흐름작업을 할 수 있을까요?
테이블에 서버 목록을 넣어 놓고 이 것을 읽어 들이면서 루핑을 돌리면 될 듯도 한데..참 어렵습니다..


질문과 비슷한 형태인데, 가져오기 대신 내보내기 형태로 구현하면서 설명 드리겠습니다.

다음과 같이 하나의 원본 테이블을 서로 다른 서버에 있는 동일한 형태의 테이블들로 이관하는 예입니다.






다음과 같이 원본 테이블 및 대상 테이블들을 준비합니다.



--Source Table

USE TEMPDB

GO


CREATE TABLE SOURCETABLE

(

        SEQ INT IDENTITY,

        COL VARCHAR(10) DEFAULT('SourceData')

)

GO



INSERT SOURCETABLE DEFAULT VALUES

GO 10



--Target Table in DSHAN

USE TEMPDB

GO


CREATE TABLE TARGETTABLE

(

        SEQ INT,

        COL VARCHAR(10)

)

GO



--Target Table in DSHAN\SS2000

USE TEMPDB

GO


CREATE TABLE TARGETTABLE

(

        SEQ INT,

        COL VARCHAR(10)

)

GO



--Target Table in DSHAN\SS2005

USE TEMPDB

GO


CREATE TABLE TARGETTABLE

(

        SEQ INT,

        COL VARCHAR(10)

)

GO


SSIS 빈 패키지를 하나 추가한 후, 다음과 같이 DB 연결에 필요한 속성을 받기 위한 변수들을 설정합니다.




기본 설정을 하기 위해 ConnectionString을 제외한 나머지 변수들에는 초기값을 넣어줍니다.



이제, ConnectionString을 선택한 상태에서 속성 창을 살펴봅시다. 속성 창의 여러 항목 중 Expression을 선택합니다. 이 속성의 오른쪽 부분 끝에 … 버튼이 있습니다. 이를 클릭해서 ConnectionString 변수에 대한 식(Expression)을 설정하는 창을 엽니다.




식 작성기에서 다음과 같이 연결 문자열을 만드는 식을 입력합니다.



"Provider=SQLOLEDB.1;Data Source=" + @[사용자::ServerName] + ";User ID=" + @[사용자::LoginID] + ";Password=" + @[사용자::Password] + ";Initial Catalog=" + @[사용자::DBName] + ";Persist Security Info=True;"





확인을 눌러 식 작성기 창을 닫고, 속성 부분에서 EvaluateAsExpression의 속성값을 False에서 True로 변경합니다.



이제, 연결 관리자에서 OLE DB 연결을 하나 추가한 후, 이름을 TargetServer로 변경합니다. 이 때 연결 정보는 위에서 변수에서 설정한 것과 같이 대상 테이블 중 하나에 맞도록 설정합니다. 그런 다음 위에서와 같이 TargetServer 연결을 선택한 상태에서 속성 창의 Expression을 선택해서 식을 설정합니다.




앞에서 변수에 대한 식을 설정했을 때와는 달리 여러 속성을 선택할 수 있는 속성 식 편집기가 나타납니다. 여기서 ConnectionString을 선택한 후 @[사용자::ConnectionString] 변수로 식을 설정합니다.



자, 이제 연결 관리자에 Source 테이블에 대한 OLE DB 연결을 추가하고, 이 이름을 SourceServer라고 변경합니다.






제어 흐름 영역에 Foreach 루프 컨테이너를 추가합니다. 서버 정보를 하나씩 가져오는 부분을 구현할 것인 것 본 예제에서는 간단히 Foreach Item 열거자를 이용해서 직접 서버 정보들을 입력하는 형태로 구현하겠습니다.



Foreach 루프 컨테이너의 속성 창을 연 다음, Enumerator를 Foreach Item 열거자로 변경하고 아래와 같이 대상 서버들에 대한 정보를 입력합니다.




변수 매핑 탭에서 다음과 같이 각 열 정보에 대해 매핑되는 변수를 설정합니다.





확인을 눌러 편집기 창을 닫고, 컨테이너 안에 데이터 흐름 작업을 하나 추가합니다.

데이터 흐름 작업에서 OLE DB 원본과 OLE DB 대상을 추가한 후, 각각 SourceServer, TargetServer로 연결과 테이블을 설정합니다.



다 되었습니다. 패키지를 실행하면서 반복해서 각 테이블들에 데이터가 들어가는지 확인해 봅시다.



저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

스크립트 변환에서 오류 처리하기  (0) 2011.08.27
[DTS] 데이터 로딩 과정에서 필터링 하기  (0) 2011.08.27
FTP 작업에서 파일 없는 경우 처리 방법  (0) 2011.08.27
스크립트 변환을 이용한 문자열 분리  (0) 2011.08.27
데이터 필터링 구현  (0) 2011.08.27

FTP 작업에서 파일 없는 경우 처리 방법

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자



 Question
FTP 전송 작업에서 원격 파일이 존재 하지 않을경우, 에러로 처리되는데, 에러를 막을 수 있는 방법이 있을가요..?
해당 서버에 파일이 올라올수도있고 없을수도 있어서 파일이 없다고 에러로 처리되면 안되거든요..ㅡ.ㅜ
이점이 안되면 ftp command text 파일만들어서 실행을...ㅡ.ㅜ



SSIS의 FTP 작업 중 에러 처리를 구현하는 방법에 대해 간단히 살펴보겠습니다.

 

 

만약 FTP 작업을 통해 FTP 서버에 있는 파일을 가져올 경우, 파일이 있는 경우에는 정상적으로 수행됩니다.

 

 

예)  FTP 서버에 /Document/INVOICE_ADC.doc 이라는 파일이 있는 경우

 


 

 

예) 파일이 없는 경우 (INVOICE_ADC1.doc 라는 파일은 서버에 없다.)

 



 


 

 

작업이 실패로 처리되는 대신, 파일이 없는 경우라도 정상적으로 성공으로 수행되며, 대신 다음 작업을 진행하지 않도록 설정하고자 한다면, 다음과 같은 방식을 이용하는 것은 어떨까 합니다.

 

 

1. 우선 FTP 작업의 성공 여부를 기록할 String 형 변수를 하나 추가합니다. 본 예제에서는 FileExistsYN이라고 하고, 기본값으로 “Y”로 설정토록 하겠습니다.



 

2. FTP 작업을 설정한 후, FTP 작업을 선택한 상태에서 속성 창에서 ForceExecutionResult의 값을 None에서 Success로 변경합니다. 이렇게 하면 작업이 실패하든 성공하든 무조건 결과는 성공으로 처리됩니다.




 

3. 자, 그럼 이제는 파일이 없을 경우, FileExistsYN 변수 값을 N으로 변경하는 것을 추가합니다. 이것은 어디서 설정할까요? 가져올 파일이 없는 경우, FTP 작업 개체는 에러를 발생하게 됩니다. 에러는 발생하였지만 2단계에서 설정한 ForceExecutionResult의 속성에 따라 수행 결과는 성공으로 처리합니다. 즉, 파일이 없는 경우에는 에러를 처리하게 되고, 이 때 변수의 값을 변경해 주면 되겠지요. 어디서? FTP 작업의 OnError 이벤트 처리기에서 입니다.

FTP 작업 개체를 선택한 후, 윗부분에 있는 이벤트 처리기를 선택합니다. 여러 이벤트 중, OnError 이벤트를 선택합니다.



 

자, 이제 이벤트 처리기 영역에 스크립트 작업을 추가한 후, 다음과 같이 ReadWrite변수에 FileExistsYN을 추가합니다.



 

아래에 있는 스크립트 디자인(S)를 클릭해서 VSA를 연 후, 다음과 같은 간단한 스크립트를 입력합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

 

Public Class ScriptMain            

 

             Public Sub Main()

                           Dts.Variables("FileExistsYN").Value = "N" 

                           Dts.TaskResult = Dts.Results.Success

             End Sub

 

End Class

 

 

 

4. VSA를 닫고 다시 제어 흐름 영역으로 돌아온 다음, 아무 작업 개체를 FTP 작업과 연결합니다. 연결된 녹색 선을 더블 클릭한 후 나타나는 선행 제약 조건 편집기를 아래와 같이 설정합니다.




 

5. 자, 이제 패키지를 실행해 봅니다.

 

1) 파일이 있는 경우

                 

 

                  2) 파일이 없는 경우

                 

 

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

[DTS] 데이터 로딩 과정에서 필터링 하기  (0) 2011.08.27
동일한 구조의 테이블로 결과 보내기  (0) 2011.08.27
스크립트 변환을 이용한 문자열 분리  (0) 2011.08.27
데이터 필터링 구현  (0) 2011.08.27
스크립트 구성 요소를 이용한 원본, 변환 구현하기  (0) 2011.08.27
스크립트 변환을 이용한 문자열 분리


한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자


Question
[A]가 [B제품]을 발송하였다.
[A]가 [C 제품]을 [D] 에게 판매하였다.
[B]가 [K지역]에서 [A]에게 물품을 구매하였다

이런식입니다;;;
앞에 category열이 발송/판매/거래 등의 의미로 구분할수 있지만, 실제 저 텍스트를 분석해서 테이블로 밀어넣고 분석해야 합니다.

문자열에서 위처럼 [] 토큰 등으로 구분가능합니다. 다만, 순서가 다를수 있습니다
현재 분석할수 있는 자료는 아래처럼 테이블로 데이터를 정리한게 있습니다.

10 [주체] [제품]
20 [주체] [제품] [대상]

문자열 파싱하는데 스크립트 작업이 필요할텐데..저 데이터 정의 테이블을 어떻게 끌고와서
응용해야될지 모르겟습니다.


(우선 제가 질문의 의도를 정확히 이해 했는지 확신을 못하겠습니다. 만약 잘못 짚었다면 그냥 다음 내용을 참고만 하시기 바랍니다.^^)

다음과 같은 처리 방식을 생각해 보겠습니다.

우선, 입력되는 문자열 데이터에서 [..] 형태의 토큰을 분리해 내는 작업을 먼저 수행합니다.

이를 구현하기 위해서는 스크립트 변환 요소를 이용해서 입력된 문자열을 이용해서 이를 분리하는 기능을 구현합니다.


단순히 토큰만 분리해 내는 것이 아니라 분석 대상 자료와 비교를 해야 합니다.

하지만, 어떤 경우에는 다음과 같이
      [주체] [제품] [대상]

으로 입력이 될 수 있지만 
      [주체] [대상] [제품]

또는
      [대상] [주체] [제품]

형식으로 입력이 될 수도 있을 것입니다.


이에 대한 처리를 위해서 저는 다음과 같은 (단순 & 무식한) 방법을 생각해 보았습니다.

『토큰을 정렬해서 출력하고, 분석 대상 또한 정렬한 후 비교하자. 』 입니다.


예를 들어,

       [B]가 [K지역]에서 [A]에게 물품을 구매하였다.

는 [B] [K지역] [A] 라고 토큰이 분리되겠지만, 이를 [A] [B] [K지역] 이라고 정렬해서 출력한다는 것입니다.


주체와 제품, 대상이 서로 성격이 다른 데이터(예를 들어, 사람 이름과 지역 명, 제품 명 등)인 경우에는 서로 동일한 데이터가 거의 없기 때문에 이와 같이 정렬을 해서 비교를 하여도 잘못된 비교를 할 확률이 낮지 않을까라고 가정해봤습니다.


여기까지가 저 혼자의 망상(^^)으로 만들어 낸 시나리오이고, 이를 바탕으로 다음과 같은 스크립트 변환 작업을 작성해 봤습니다.

(이것 짜서 올리면 저의 프로그래밍 실력이 뽀록(!)나기에 많이 고민하다가 그냥 참고하시고 잘 변형해서 사용하시라고..)


데이터 읽어오는 부분이나 분석 대상 테이블과의 비교는 조회 변환 등을 참고해서 구현해 보시고 스크립트 변환 부분만을 올립니다.





Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

 

    Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)

 

        Row.ParseStr = StringExtraction(Row.context, "[", "]")

 

    End Sub

 

 

    Public Function StringExtraction(ByVal sExpression As String, _

                                    ByVal sNearFrontString As String, _

                                    ByVal sNearBackString As String, _

                                    Optional ByVal oCompareMethod As CompareMethod = CompareMethod.Text) As String

 

        Dim Result(10) As String

        Dim ResultSeed As Integer = 0

        Dim tmpString As String

        Dim StartPoint As Integer

        Dim EndPoint As Integer

        Dim ResultStr As String

        Dim i As Integer, j As Integer

 

        Try

 

            '입력값 유효성 판단

            If sExpression.Length = 0 Or sExpression Is Nothing Then Exit Try

            If sNearFrontString.Length = 0 Or sNearFrontString Is Nothing Then Exit Try

            If sNearBackString.Length = 0 Or sNearBackString Is Nothing Then Exit Try

 

            For i = 1 To sExpression.Length

                StartPoint = InStr(sExpression, sNearFrontString, oCompareMethod)

                If StartPoint <= 0 Then Exit For

 

                tmpString = Mid(sExpression, StartPoint)

                EndPoint = InStr(sExpression, sNearBackString, oCompareMethod)

                Result(ResultSeed) = Mid(sExpression, StartPoint, EndPoint - StartPoint + 1)

                sExpression = Mid(sExpression, EndPoint + 1)

                i = i + EndPoint

                ResultSeed += 1

            Next

 

            '결과 데이터 정렬

 

            For i = 0 To ResultSeed - 1

                For j = i + 1 To ResultSeed - 1

                    If Result(j) < Result(i) Then

                        tmpString = Result(i)

                        Result(i) = Result(j)

                        Result(j) = tmpString

 

                    End If

                Next

            Next

 

            '결과 데이터 준비

            ResultStr = Result(0)

            For i = 1 To ResultSeed - 1

                ResultStr += "," + Result(i)

            Next

 

 

        Catch ex As Exception

            ResultStr = ""

 

        End Try

 

        Return ResultStr

 

    End Function

 

 

 

End Class



저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

동일한 구조의 테이블로 결과 보내기  (0) 2011.08.27
FTP 작업에서 파일 없는 경우 처리 방법  (0) 2011.08.27
데이터 필터링 구현  (0) 2011.08.27
스크립트 구성 요소를 이용한 원본, 변환 구현하기  (0) 2011.08.27
파일에 저장된 쿼리를 데이터 원본에서 사용하기  (0) 2011.08.27

데이터 필터링 구현

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 Question
원본이 텍스트 파일이고, 각각의 카테고리 ID 가 있습니다.
여기서 제외해야될  ID  값이 있는데, 이게 한 100여개도 가능합니다.

간단한 필터링은 [조건부분할] 로 사용해서 처리가능하였는데, 정작 원하는 필터링은 일일이 입력할수 없어서 난감한 상태입니다.
아래와 같은 식인데..10번/20번/30번/40번.... 천번단위로 있고, 여기서 제외해야될 id 가
별도의 테이블등으로 존재한다고 할때, 어떠한 방식으로 처리해야 할지요?

단순히 테이블로 밀어넣은 이후에 삭제하는 방법이 꽤 비효율적으로 생각되어 문의드립니다.
예) 원본 텍스트 파일
category  content
10 AAA
10 AAA
20 BBB



 

다음과 같이 데이터 테이블을 준비합니다.

USE TEMPDB

GO

 

CREATE TABLE DataTable

(

             seq int identity,

             category int,

             content varchar(100)

)

GO

 

SET NOCOUNT ON

 

DECLARE @I AS INT

SET @I = 1

WHILE (@I<=10000)

BEGIN

             INSERT DataTable(category, content)

             SELECT CAST(rand()*1000 AS INT) * 10,

                          REPLICATE(CHAR(CAST(rand()*26 AS INT)+65), rand()*100)

 

             SET @I = @I + 1

END

 

SET NOCOUNT OFF

GO

 

 

 

 

 

위의 데이터 중 필터링을 할 대상을 저장하는 FilterTable 테이블을 생성합니다. 본 예제에서는 구분하기 용이하게 seq 열이 11부터 90 사이에 있는 category 값을 필터 할 대상으로 생성하겠습니다.

 

CREATE TABLE FilterTable

(

             category int

)

GO

 

INSERT FilterTable

SELECT DISTINCT category FROM DataTable

WHERE seq between 11 and 90

GO

 

 

 

 

패키지로 구현하기에 앞서 잠시 쿼리로 생각해 봅시다.

만약 위와 같은 테이블 들에 대해 필터링 할 경우에는 다음과 같은 쿼리를 생각할 수 있습니다.


 


성능을 고려한다면 다음과 같이 쿼리를 사용할 수도 있겠지요.

 

 


이와 같은 형식으로 패키지를 구현하고자 합니다. 데이터 원본은 텍스트 파일이든 OLE DB 원본이든 동일하기 때문에 편의상 OLE DB 원본으로 설명하겠습니다.

 

 

빈 패키지 파일을 만든 다음, 제어 흐름 영역에 데이터 흐름 작업을 추가하고, OLE DB 원본으로 위의 DataTable을 지정합니다.

 



 

조건부 분할을 이용해서 데이터를 필터 할 수 있지만, 필터 할 대상이 자주 변하거나 본 예제와 같이 별도의 테이블로 관리를 해야 한다면, 조건부 분할 대신 조회 변환을 이용하는 것이 유용할 수 있습니다.

만약 필터링 대상 테이블이 수 만 건 이상인 경우에는, SQL 2005 SSIS에서는 메모리 문제나 조회 대상 데이터 캐싱 문제로 인하여 성능 저하가 발생할 수도 있지만, 본 요구 사항과 같이 수 십에서 수백 건 정도라면 조회 변환을 이용하는데 큰 문제가 없습니다.

 

조회 변환을 추가한 후, OLE DB 원본과 연결합니다.

 


 

다음과 같이 조회 변환 편집기에서 조회 대상 테이블을 선택하거나 쿼리를 작성합니다. 가급적 테이블을 직접 지정하는 대신 쿼리를 사용하는 것을 권장합니다.



 

열 탭에서 아래와 같이 단순히 연결만 시킵니다.


 

 

그런 다음 화면 아래에 있는 오류 출력 구성(G)를 선택한 후, 오류 부분을 행 리디렉션으로 변경합니다.


 


 

이제, 멀티캐스트 또는 OLE DB 대상 등 아무 변환이나 대상을 추가한 후, 아래와 같이 조회 변환과 빨간 색 선으로 연결시킵니다.



 

각 경로에 데이터 뷰어를 추가한 후 처리되는 데이터를 확인해 봅시다.




 

오류 출력이기 때문에 본 데이터 외에 Error 및 ErrorColumn, ErrorCode-설명 열이 추가되지만, 특별히 사용할 필요는 없습니다.

 


 

조회가 되는 경우는 필터 되는 데이터이고, 조회가 안되어서 오류가 발생하는 데이터가 필터 되고 남은 데이터 입니다.

 

만약 FilterTable 테이블을 모두 지운 후에 패키지를 실행시키면 필터 되는 대상이 없기 때문에 모든 데이터가 출력되겠지요.






 

 
저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

FTP 작업에서 파일 없는 경우 처리 방법  (0) 2011.08.27
스크립트 변환을 이용한 문자열 분리  (0) 2011.08.27
스크립트 구성 요소를 이용한 원본, 변환 구현하기  (0) 2011.08.27
파일에 저장된 쿼리를 데이터 원본에서 사용하기  (0) 2011.08.27
Foreach 루프 컨테이너를 이용한 패키지 반복 실행  (0) 2011.08.27

스크립트구성요소.dtsx


스크립트 구성 요소를 이용한 원본, 변환 구현하기

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

 

 Question
아래의 데이터를  COL1기준으로 중복을 제거하고 나열하고 싶은데요. (순서는 상관없습니다.)
COL1     COL2
---------------
    1       'AAA'
    2       'BBB'
    3       'CCC'
    2       'ZZZ'
    2       '1111' 
    2       'ZZZ'
  
<원하는 결과셋>
COL1     COL2
---------------
    1       'AAA'
    2       'BBB' 'ZZZ' '1111'
    3       'CCC'

 

 

본 글에서는 다음과 같은 내용에 대해 다루겠습니다.

 

1.       스크립트 변환을 이용해서 원본 데이터 생성하기

2.       정렬을 이용한 데이터 중복 제거

3.       비동기(Asynchronous) 스크립트 변환 작업

 

 

 

 

 

1. 스크립트 변환을 이용한 원본 데이터 생성

 

데이터 처리 작업에서 원본이 되는 데이터 소스는 텍스트 파일, 데이터 테이블, 엑셀 파일 등 여러 형태가 될 수 있습니다. 어떤 경우에는 데이터 원본을 창조(!! ㅎㅎ)해야 하는 경우도 있겠지요. 테스트 데이터나 샘플 데이터 또는 데이터 처리에 필요한 임의의 규칙을 적용한 데이터를 생성해야 할 때 등입니다.

 

이런 경우에, 스크립트 구성요소의 원본 기능을 이용할 수 있습니다. 참고로, 제어 흐름의 스크립트 작업과는 다른 작업 개체입니다.

 

자, 이제 이런 기능을 구현해 봅시다.

 

우선, 제어 흐름 영역에 데이터 흐름 작업을 하나 추가한 후, 이를 더블 클릭해서 데이터 흐름 영역을 엽니다. 여기에 도구 상자의 데이터 흐름 변환 중 스크립트 구성요소를 선택하여 추가합니다. 추가할 경우, 이 스크립트 구성요소를 원본으로 사용할 지 또는 변환이나 대상으로 사용할 지를 선택하는 다음과 같은 창이 나타납니다.

 

 

원본(S)으로 선택하고 확인을 눌러 창을 닫은 후, 해당 작업 개체를 더블 클릭해서 스크립트 변환 편집기를 엽니다.

 

스크립트 변환 편집기는 다음과 같이 세 개의 탭으로 구성되어 있습니다. 탭의 각 기능에 대한 자세한 정보는 다음 링크의 글을 참고하시기 바랍니다.

 

 

입/출력(I) 부분의 출력 0을 확장한 후, COL1과 COL2라는 열을 추가합니다.

COL1은 부호 없는 4바이트 정수(DT_I4), COL2는 문자열(DT_STR) 10자리로 설정합니다.

 

이제 스크립트 탭에서 스크립트 디자인(S)를 클릭하여 VSA(Visual Studio for Applications)를 열고 다음과 같은 코드를 입력합니다. (VB.net은 제가 어떻게 설명할 내용이 아니기 때문에 설명은 생략합니다.^^)

 

 

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

 

 

 

    Public Overrides Sub CreateNewOutputRows()

       

        With 출력0Buffer

            .AddRow() : .COL1 = 1 : .COL2 = "'AAA'"

            .AddRow() : .COL1 = 2 : .COL2 = "'BBB'"

            .AddRow() : .COL1 = 3 : .COL2 = "'CCC'"

            .AddRow() : .COL1 = 2 : .COL2 = "'ZZZ'"

            .AddRow() : .COL1 = 2 : .COL2 = "'111'"

            .AddRow() : .COL1 = 2 : .COL2 = "'ZZZ'"

        End With

 

    End Sub

 

   

End Class

 

 

자, 이제 멀티캐스트 변환이나 파생 열 변환과 같이 임의의 변환 작업을 연결한 후, 데이터 뷰어를 통해 제대로 데이터가 발생되는지 확인해 봅니다.

 

 

 

 

 

 

2. 정렬(Sort) 변환을 이용한 중복 제거하기

 

위의 예제에서 2, ‘ZZZ’ 데이터는 두 번 나옵니다.

만약 데이터 원본이 MSSQL 이라면 간단히

SELECT distinct …

구문을 이용해서 중복을 제거할 수도 있습니다.

 

하지만, 만약 데이터 원본이 텍스트 파일이나 위에서와 같이 쿼리를 적용할 수 없는 데이터 원본이라면 어떻게 해야 할까요? 읽어온 데이터를 임시 테이블에 넣고 다시 이를 읽어오는 방식으로 해야 할까요? 이러한 방식이 스테이징(Staging) 단계를 이용하는 형태입니다.

어떻게 보면, 이러한 스테이징 단계를 거치는 방법은 불필요한 디스크 I/O를 발생시키는 비효율적인 방식입니다.

 

이럴 경우, SSIS에서는 정렬 변환을 이용할 수 있습니다. 정렬 변환은 입력 데이터를 정해진 순서대로 정렬시키는 변환입니다. 하지만, SSIS의 정렬 변환은 단순 정렬뿐만 아니라 중복 제거라는 유용한 기능을 포함하고 있습니다. 정렬 변환을 이용한 데이터 중복 제거는 다음 글을 참고하시기 바랍니다.

 

여기서는 단순히 구현만 하도록 하겠습니다.

 

1의 예제에서 멀티캐스트 변환을 제거한 후, 대신 정렬 변환을 추가하고 스크립트 구성요소와 연결합니다.

 

본 예제에서는 정렬 기능은 크게 중요한 사항은 아닙니다. 전체 데이터 중에서 중복을 제거하는 것이기 때문에 사용 가능한 입력 열 부분에서 열을 모두 선택한 후, 아래에 있는 중복되는 정렬 값이 있는 행 제거(R)부분을 체크하고 확인합니다.

 

여기서도 1과 같이 멀티캐스트 같은 변환을 추가한 후, 데이터 뷰어를 통해서 처리되는 데이터를 확인해 보시기 바랍니다.

 

 

 

 

 

3. 비 동기(Asynchronous) 스크립트 변환 작업 구현하기

 

이제 위의 두 기능과는 조금 복잡한 기능을 구현해 봅시다. 우선 동기(Synchronous)와 비동기(Asynchronous)작업에 대해 아주 간단히 설명하고 넘어가겠습니다.

동기 작업은 입력되는 대로 바로 출력하는 변환 형태입니다. 파생 열 변환과 같이 일정 크기의 버퍼를 통해 전체 데이터 중 일부라도 로딩이 되어 변환 작업에 들어오면 이 데이터들만 바로 처리해서 출력하는 형태의 작업입니다. 이에 비해 비동기 작업은 정렬이나 집계 등과 같이 전체 데이터를 로딩한 후에 이를 대상으로 연산 작업을 한 후, 출력하는 형태입니다.

 

위의 예제를 이용해서 계속 진행하겠습니다.

 

정렬 변환 뒤에 스크립트 구성 요소 – 변환을 추가합니다.

 

 

스크립트 구성 요소가 변환으로 설정된 경우, 원본의 경우와는 달리 입력 열 탭이 하나 더 있습니다. 스크립트 변환에서 사용할 입력 열을 선택하는 단계이며, 본 예제에서는 COL1, COL2 모두 선택하도록 합니다.

 

입/출력 탭에서 아래와 같이 출력 0을 선택하고선, 오른쪽의 속성 부분에서 SynchronousInputID의 속성값을 없음으로 변경합니다.

 

그런 다음, 아래와 같이 두 열을 추가합니다. COL1이라는 부호 없는 4바이트 정수(DT_I4)와 COL3이라는 문자열(DT_STR) 50자리 열을 추가합니다. (필요한 경우, 조절해서 사용하세요.)

 

스크립트 탭에서 스크립트 디자인(S) 버튼을 클릭해서 VSA를 연 다음 아래와 같은 스크립트를 입력합니다.

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

 

    Dim COL2() As String, maxSeq As Integer = 0

 

    Public Overrides Sub 입력0_ProcessInput(ByVal Buffer As 입력0Buffer)

 

        While Buffer.NextRow()

            입력0_ProcessInputRow(Buffer)

        End While

 

        If Buffer.EndOfRowset Then

 

            Dim i As Integer

 

 

            For i = 1 To maxSeq

                If COL2(i).ToString <> "" Then

                    With 출력0Buffer

                        .AddRow()

                        .COL1 = i

                        .COL3 = COL2(i).ToString

 

                    End With

 

                End If

            Next

 

            출력0Buffer.SetEndOfRowset()

        End If

 

    End Sub

 

 

    Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)

 

        If COL2(Row.COL1) <> "" Then

            COL2(Row.COL1) = COL2(Row.COL1) + " " + Row.COL2.ToString

        Else

            COL2(Row.COL1) = Row.COL2.ToString

        End If

 

        If Row.COL1 > maxSeq Then

            maxSeq = Row.COL1

        End If

 

    End Sub

 

 

    Public Sub New()

        ReDim COL2(10000)

    End Sub

End Class

 

 

(스크립트 엉망이라고 뭐라고 하지 마십쇼~!!ㅎㅎ 더 좋은 방법 있으면 알려주십쇼~^^)

 

VSA를 닫고서 제대로 처리되는지 확인해 봅니다.

 

 

 

 


본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.




저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

스크립트 변환을 이용한 문자열 분리  (0) 2011.08.27
데이터 필터링 구현  (0) 2011.08.27
파일에 저장된 쿼리를 데이터 원본에서 사용하기  (0) 2011.08.27
Foreach 루프 컨테이너를 이용한 패키지 반복 실행  (0) 2011.08.27
패키지 외부에서 데이터 원본 및 대상 설정하기  (0) 2011.08.27

파일에 저장된 쿼리를 데이터 원본에서 사용하기

 

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

 

 Question
데이터 원본에서 테이블 보다는 쿼리를 이용해 패키지 개발을 하는게 편리한데요,
이 쿼리를 패키지 내에 두지 않고 따로 파일로서 관리 할 수 있는 방법이 있을까요?

 

 

제어 흐름의 SQL 실행 작업에는 파일에 저장된 쿼리를 직접 읽어와서 실행시킬 수 있지만, 데이터 흐름 내의 OLE DB 원본 등에서는 이러한 기능이 없습니다.

 

 

 

이를 구현하기 위해서는 다음과 같은 방식으로 대신할 수 있지 않을까 합니다.

             

 

             [단계 1] 파일 연결(기존 파일)로 지정된 파일의 내용을 변수에 저장

             [단계 2] 이 변수를 이용하여 데이터 흐름의 원본에서 사용

 

 

다음과 같이 간단한 SQL 문이 포함된 파일을 만든 후, 이를 지정하는 파일 연결(기존 파일)을 설정합니다. 본 예제에서는 SQLSource라고 하겠습니다.

 

 

 

이제 쿼리를 실행할 DB 연결을 설정한 후, 다음과 같이 String 형 변수를 하나 추가합니다.

 

값 부분은 변경되겠지만, 우선 패키지를 작성하기 위해서는 기본값을 입력해 줘야 합니다.

 

 

제어 흐름 영역에 스크립트 작업을 하나 추가한 후, ReadWriteVariables에 위에서 추가한 변수를 설정합니다.

 

 

 

 

스크립트 디자인(S) 버튼을 클릭하여 VSA를 연 후, 다음과 같은 스크립트를 입력합니다.

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

 

 

Public Class ScriptMain

    Public Sub Main()

 

        '파일 연결로부터 파일 위치를 획득

        Dim filename As String = Dts.Connections("SourceSQL").ConnectionString

        Dim queryFile As StreamReader

 

        queryFile = File.OpenText(filename)

 

        '파일 내의 내용을 SourceSQL1 변수에 저장

        Dts.Variables("SourceSQL1").Value = queryFile.ReadToEnd()

        queryFile.Close()

 

        Dts.TaskResult = Dts.Results.Success

    End Sub

 

End Class

 

 

이제 데이터 흐름 작업을 추가한 후 스크립트 작업과 연결하고선 데이터 흐름 영역 내에 OLE DB 원본을 추가합니다.

 

데이터 액세스 모드를 변수를 사용한 SQL 명령으로 선택하고, 변수 이름을 위에서 지정한 SourceSQL1 변수로 변경합니다.

 

 

 

 

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

데이터 필터링 구현  (0) 2011.08.27
스크립트 구성 요소를 이용한 원본, 변환 구현하기  (0) 2011.08.27
Foreach 루프 컨테이너를 이용한 패키지 반복 실행  (0) 2011.08.27
패키지 외부에서 데이터 원본 및 대상 설정하기  (0) 2011.08.27
루핑 처리 방법  (0) 2011.08.27
Foreach 루프 컨테이너를 이용한 패키지 반복 실행  

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

 Question
 FLATFILE 하나당 패키지를 하나씩 만들고 별도의 패키지에서 '패키지 실행 작업' 연결을 통해 일괄처리하고자 합니다.
문제는 패키지명에 해당하는 테이블의 내용을 ETL 작업전에 DELETE 해주어야 하는데 단위 패키지마다 'SQL 실행작업' 으로 DELETE 를 해주는 방법외에 단위 패키지가 실행되기 전에 패키지명을 읽어서 해당테이블의 데이터를
DELETE 해줄 수 있는 방법이 있을까요.
 
 

 

다음과 같은 테스트 환경을 고려하겠습니다.

 

 

1.       TableA, TableB, TableC, … 라는 테이블에 데이터를 입력하는 개별 패키지를 TableA.dtsx, TableB.dtsx. TableC.dtsx, … 라고 만듭니다.

2.       테이블의 종류는 더 늘어날 수 있습니다.

3.       부모 패키지에서 이 패키지들을 호출하는데, 호출하기 전에 해당 테이블의 내용을 모두 지우는 작업을 구현합니다.

 

 

 

우선 TableA, TableB, TableC, … 라는 임시 테이블을 만듭니다.

USE TEMPDB

GO

 

CREATE TABLE TableA (Seq INT Identity, COL VARCHAR(10) DEFAULT('AAA'))

GO

CREATE TABLE TableB (Seq INT Identity, COL VARCHAR(10) DEFAULT('BBB'))

GO

CREATE TABLE TableC (Seq INT Identity, COL VARCHAR(10) DEFAULT('CCC'))

GO

 

이 테이블에 데이터를 입력하는 패키지들을 간단히 만듭니다.

 

본 예제에서는 다음과 같은 간단한 입력 쿼리를 실행하는 패키지로 설정하였습니다.

INSERT TableA DEFAULT VALUES

GO

INSERT TableA DEFAULT VALUES

GO

INSERT TableA DEFAULT VALUES

GO

INSERT TableA DEFAULT VALUES

GO

INSERT TableA DEFAULT VALUES

GO

 

이렇게 생성된 패키지를 D:\Packages\ 라는 디렉터리로 복사해 놓습니다.

 

이제 이를 실행할 패키지를 만듭니다.

빈 패키지를 추가한 후, 다음과 같은 변수들을 추가합니다.

 

 

PackageName은 패키지 명을 저장하는 변수이며, 이 변수 중에서 이름 부분만을 잘라내어 TableName 이라는 변수에 저장할 것입니다. TableName 식 정의 부분은 뒤에서 구현하겠습니다.

PackagePath 는 패키지가 저장된 경로를 지정하는 변수입니다. 만약 패키지 경로가 변경될 경우, 이 변수 값만 변경해 주면 수행될 수 있도록 하기 위해 별도로 변수로 설정하였습니다.

 

이제, 지정된 위치의 모든 패키지 명을 읽어오는 작업을 설정합니다.

 

제어 흐름에 Foreach 루프 컨테이너를 추가한 후, 다음과 같이 설정합니다.

 

 

 

 

 

@[사용자::PackagePath]라는 변수에 저장된 경로에, *.dtsx 라는 파일 명(이름만)을 읽어온 후, 이를 @[사용자::PackageName]이라는 변수에 반복적으로 저장하도록 설정하는 것입니다.

 

 

이제 PackageName이라는 변수로부터 TableName을 읽어오도록 설정하겠습니다.

 

변수에서 TableName을 선택한 후, 오른쪽의 속성 창에서 Expression 부분을 선택합니다.

 

 

 

… 버튼을 클릭해서 식 작성기를 연 다음, 다음과 같은 식을 입력합니다.

 

REPLACE(@[사용자::PackageName],".dtsx","")   

 

 

확인을 눌러 식 작성기를 닫은 후, EvaluateAsExpression 속성값을 True로 변경해 줍니다.

 

 

  

연결 관리자에서 다음과 같이 OLE DB 연결을 추가합니다. 이 OLE DB 연결의 대상은 위에서 만든 테이블들이 있는 DB로 설정합니다.

 

 

이제, Foreach 루프 컨테이너 내에 SQL 실행 작업을 하나 추가한 후, SQL 실행 작업 편집기에서 다음과 같이 Connection 부분만 지정합니다.

 

식 탭으로 이동한 후, 다음과 같이 SqlStatementSource 속성을 선택한 후, 오른쪽의 … 버튼을 클릭하여 식 작성기를 엽니다.

 

 

이제 읽어온 테이블 이름을 이용해서 DELETE 명령을 수행하는 식을 만듭니다.

 

 

식 작성기와 SQL 작업 편집기를 닫은 후, 패키지 실행 작업을 추가하여 SQL 실행 작업과 연결합니다.

 

연결 관리자에서 새 파일 연결을 선택한 후, 사용 유형을 기존 파일로 선택하고 D:\Packages\ 디렉터리 내에 있는 임의의 패키지를 하나 선택합니다.

 

확인을 눌러 편집기를 닫은 후, 해당 연결을 선택한 상태에서 속성 창의 Expression에 다음과 같은 식을 설정합니다. (본 예제에서는 이 연결의 이름을 『패키지 파일』로 변경하였습니다.)

 

속성          : ConnectionString

식              : @[사용자::PackagePath] + @[사용자::PackageName]

 

 

이제 패키지 실행 작업을 연 다음 아래와 같이 패키지 탭을 설정합니다.

 

자, 모두 끝났습니다. 패키지를 수행해서 정상적으로 처리되는지 확인해 봅니다.

 

 

 

(1회 수행 시)

 

 

(2회 수행 시)

 
 
저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

스크립트 구성 요소를 이용한 원본, 변환 구현하기  (0) 2011.08.27
파일에 저장된 쿼리를 데이터 원본에서 사용하기  (0) 2011.08.27
패키지 외부에서 데이터 원본 및 대상 설정하기  (0) 2011.08.27
루핑 처리 방법  (0) 2011.08.27
동적 대상 설정하기  (0) 2011.08.27

Directory.zip

Sample.dtsx


패키지 외부에서 데이터 원본 및 대상 설정하기

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

 

 Question
폴더 A -- 노트북1.txt/데스크탑3.txt/키보드5.txt/마우스7.txt
폴더 B -- 노트북2.txt/데스크탑7.txt/키보드3.txt/마우스9.txt
폴더 C -- 노트북5.txt/데스크탑9.txt/키보드6.txt/마우스3.txt
 
예를들어 위와같이 각 폴더에 4개의 text 파일이 존재합니다.
이 파일들은 1개의 DB에 그리고 각각 다른 테이블에  insert하게 됩니다.
 이것을 패키지로 실행유틸리티로 했을때 A폴더에 대한 데이터를 실행해야하고, B폴더에 대한 데이터를 실행해야하고, C폴더에 대한 데이터를 실행해야합니다. 폴더의 경로가 다르고, 파일명도 조금씩 다릅니다. 즉, 패키지를 실행할때마다, 선택해야하는 경로 및 파일이 바뀌어야합니다. 이런경우 패키지 실행전에 경로&파일에 대한 설정을 그때그때 바꿔서 작업을 할수 있는지 궁금합니다.

 

 

 

다음과 같이 GroupA, GroupB, GroupC 라는 디렉터리에 여러 텍스트 데이터 파일들이 있는 경우를 가정합니다.


 

각각의 텍스트 파일은 동일한 열을 가집니다.

 

그리고 대상 테이블로 TableA, TableB, TableC가 있습니다.

USE TEMPDB

GO

 

CREATE TABLE TABLEA

(

                  [Group] varchar(20),

                  [File] varchar(20),

                  Qty INT,

                  Amount Money

)

GO

 

CREATE TABLE TABLEB

(

                  [Group] varchar(20),

                  [File] varchar(20),

                  Qty INT,

                  Amount Money

)

GO

 

CREATE TABLE TABLEC

(

                  [Group] varchar(20),

                  [File] varchar(20),

                  Qty INT,

                  Amount Money

)

GO

 

 

본 예제에서 만들려고 하는 기능은, 단순히 텍스트 파일에서 대상 테이블로 넣는 패키지를 만든 후, 패키지를 실행할 때 텍스트 파일과 대상 테이블 지정할 수 있는 것을 구현하고자 합니다.

 

 

빈 패키지에 위에서 만든 데이터베이스에 대한 OLE DB 연결을 추가합니다.


 


또한, 연결 관리자 내부에서 마우스 오른쪽을 클릭해서 나타나는 메뉴 중 새 플랫 파일 연결(A)를 선택한 후, 아래와 같이 연결 관리자 이름 및 파일 이름을 지정합니다. 파일 이름은 준비된 데이터 파일들 중 아무거나 선택합니다.

 


이제 열 탭을 클릭한 후, 텍스트 파일에 대한 행/열 구분 기호를 확인하고, 고급 탭을 클릭해서 각 열에 대한 이름과 유형을 설정합니다.

 


Group 및 File은 길이 20의 String형으로 설정하고 Qty 및 Amount는 부호 없는 4바이트 정수로 설정합니다.


 

자, 이제 도구 상자에서 데이터 흐름 작업을 추가한 후, 다음과 같이 파일로부터 대상 테이블에 넣는 기능을 간단히 구현합니다.

      

  

    

   (이 부분은 간단히 넘어가겠습니다. 이전의 예제들을 참고하시기 바랍니다.)

 

자, 여기까지 해서 간단히 기능을 구현하였습니다.

 

 

이제부터 동적으로 패키지를 구현할 수 있는 기능들을 추가해 보도록 합시다.


우선, 제어 흐름을 클릭한 후, 제어 흐름 영역의 빈 곳을 클릭하고선, 마우스 오른쪽 버튼을 눌러서 나타나는 메뉴 중 에서 변수(S)를 클릭하여 변수 설정 창을 띄웁니다.

   


아래와 같이 SourceFile 및 TargetTable 이라는 두 개의 String형 변수를 추가합니다. 이 때, 값에다가는 임시로 File 명과 Table 명을 입력하도록 합니다.

   


패키지 외부에서 이 변수에 값을 할당하면 이 변수들이 텍스트 원본 및 대상 테이블에 지정이 되는 방식으로 구현할 것입니다.

 

일단 위와 같이 추가했으면, 연결 관리자의 SourceFile이라는 연결을 선택한 후, 오른쪽에 있는 속성 창의 여러 항목 중 Expressions 부분에 있는 ... 버튼을 클릭합니다.

   


속성을 ConnectionString을 선택하고, 옆의 식 부분에서 ... 버튼을 클릭해서 나타나는 식 작성기 창에서 아래와 같이 @[사용자::SourceFile] 이라는 변수를 추가하고 확인을 누릅니다.

   

 

이제, 데이터 흐름 작업을 더블 클릭한 후 나타나는 데이터 흐름 영역에서 OLE DB 대상을 클릭해서 OLE DB 대상 편집기를 엽니다.

 


데이터 액세스 모드(A) 부분을 아래와 같이 『테이블 이름 또는 뷰 이름 변수』로 변경하고 아래의 변수 이름(V) 부분에 위에서 추가한 TargetTable이라는 변수로 지정합니다.

 

   

 

자, 이제 패키지가 완성되었습니다. 이 패키지를 D:\Sample.dtsx 라는 이름으로 저장하도록 하겠습니다.

 

 

 

우선 패키지를 그냥 실행시키면 기본값으로 지정된 D:\Directory\GroupA\Desktop3.txt 파일로부터 tempdb.dbo.TableA로 입력됩니다. 

   

   

 

만약, D:\Directory\GroupB\Keyboard3.txt 이라는 파일을 tempdb.dbo.TABLEB라는 테이블로 입력하고 싶다면 다음과 같이 설정하면 됩니다.

 

우선 D:\Sample.dtsx 패키지를 더블 클릭해서 패키지 실행 유틸리티(DTEXECUI)를 띄웁니다.

   


값 설정 탭을 클릭한 후, 다음과 같이 속성과 값을 추가합니다.

 

 

속성 경로

값

\Package.Variables[사용자::SourceFile].Properties[Value]

D:\Directory\GroupB\Keyboard3.txt

\Package.Variables[사용자::TargetTable].Properties[Value]

tempdb.dbo.TABLEB

 

일단, 이와 같이 설정한 후 패키지를 실행합니다.

 

 

 


자, 그럼 매번 이렇게 해야 할까요? 조금 편하게 설정합시다. (배치 파일로 만들어서 사용하기) 


패키지 실행 유틸리티 창에서 가장 아래에 있는 명령줄 탭을 클릭해서 나타나는 명령줄을 확인합니다. 

 

/FILE "D:\Sample.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI  /SET "\Package.Variables[사용자::SourceFile].Properties[Value]";"D:\Directory\GroupB\Keyboard3.txt" /SET "\Package.Variables[사용자::TargetTable].Properties[Value]";"tempdb.dbo.TABLEB"

 

위와 같은 긴 명령줄이 생성되었습니다. 이를 어디에 이용하느냐..
dtexec.exe 라는 커맨드 형식의 패키지 실행 유틸리티가 있습니다.

 

아래와 같이 배치 파일을 만들어서 사용하면 됩니다.

   

 

   

 

   

 

 

SQL Agent의 작업으로 등록할 때에도 위와 비슷합니다. 작업 유형 부분에서 Integration Services 패키지를 선택하면 위와 같이 패키지의 여러 속성을 설정할 수 있는 탭이 나타나며, 이 중 값 설정 부분에서 원하는 속성을 설정하면 됩니다.

 

 

 

 본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.
저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

파일에 저장된 쿼리를 데이터 원본에서 사용하기  (0) 2011.08.27
Foreach 루프 컨테이너를 이용한 패키지 반복 실행  (0) 2011.08.27
루핑 처리 방법  (0) 2011.08.27
동적 대상 설정하기  (0) 2011.08.27
스크립트 변환을 이용한 RowNumber 및 파생열 만들기  (0) 2011.08.27
루핑 처리 방법

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 Question -  등록일 : 2007-09-27 오전 11:18:00
다름이 아니라 만약 while 문과 변수를 통한 루프문을 만들때 어떻게 해야하는지 해서 질문올려봅니다
 
테이블1
이행의 한행 한행의 컬럼값을 변수로 받아서 이 변수를 이용한 저장프로시져 실행하려고 합니다
 테이블1의 행들은 한 행이 처리되면 checkid 란 컬럼값이 1에서 0으로변하면서 끝났다는 처리를 해야합니다..
 
순서로 보면
 테이블의 checkid 값이 1인게 몇개인지 파악.  1인값이 몇개인지 만큼 루프값을 돌린다
 
첫번째 행부터 값을 받아서 저장프로시져를 실행 저장프로시져가 성공하면 checkid 값이 0으로 바뀌고
다음 테이블1의 행을 실행 변수 받아서 다시 저장프로시져 실행 다시 checkid  값이 0 으로
이런식의 루프구문인데요 어떤 방법이 잴 좋은지 부탁드리겠습니다^^ 

 

에공. 사이트를 운영하는 입장이지만 제목에 이름 뜨는 것은 여전히 생소하네요..^^

 

 

저도 직접 환경을 보지 못해서 어떤 것이 최선인지는 모르겠습니다.

하지만, 다음과 같은 제가 이전에 해봤던(^^) 형태로 한 번 설명 드리겠습니다.

 

USE TEMPDB

GO

CREATE TABLE TABLE1

(

       SEQ INT IDENTITY,

       CHECKID TINYINT NOT NULL DEFAULT(ROUND(RAND(), 0)),

       PROCYN CHAR(1) NOT NULL DEFAULT('N')

)

GO

 

INSERT TABLE1 DEFAULT VALUES

GO 1000

 

 

1. Results 라는 Object 형 변수를 추가한다.

 

2. 연결 관리자에서 위에서 만든 테이블에 해당하는 연결을 지정한다.

 

3. SQL 실행 작업을 추가한 후, 다음과 같은 쿼리를 입력하고 아래와 같이 설정한다.

SELECT SEQ FROM TABLE1 WHERE CHECKID = 1

 

4. 결과 집합 탭에서 다음과 같이 결과를 저장할 변수를 설정한다.

 

5. Foreach 루프 컨테이너를 추가한 후, SQL 실행 작업과 연결한다.

 

6. 변수 창에서 다음과 같이 Seq 라는 변수를 추가한다.

 

7. Foreach 루프 컨테이너를 더블 클릭해서 편집기를 연 다음 아래와 같이 설정한다.

 

8. 변수 매핑 탭에서 다음과 같이 설정한다.

Foreach 루프 컨테이너에서 Object형 변수로부터 읽어 들인 값 중 첫 번째 열(Seq)의 값을 Seq라는 변수에 반복해서 넣는 것이 설정되었습니다.

 

9. 이제 컨테이너 안에 SQL 실행 작업을 하나 넣는다.

 

10. 이 SQL 실행 작업의 편집기에서 다음과 같이 설정한다.

UPDATE TABLE1 SET PROCYN = ‘U’ WHERE SEQ = ?

 

11.매개 변수 매핑 탭에서 다음과 같이 설정한다.

 

12. 패키지를 실행한다.

 

13. 수행된 결과를 확인한다.

 

 

CheckID 가 1인 값의 PROCYN 값이 ‘N’에서 ‘Y’로 변경되었습니다.

 

분명 말씀 드리는 사항인데, 이것이 최선은 아닐 수 있습니다. 단순히 데이터 흐름 작업에서 OLE DB 명령을 이용해서 처리할 수도 있는 사항입니다.

이러한 방법도 있다라는 것을 참고만 해 주시기 바랍니다.

 


신익준 답변감사합니다..그런데 여기서는 변수를 1을 받아서 처리하는건데요 만약 그 변수를 1 나 받아서 루프 컨테이너안에서 다시 그 변수를 이용하여(변수맵핑) sql실행 작업을 실시하고 그 실행한 작업에서 변수를 2개 받아서 다시 실행작업(저장프로시져로 매개변수를 2개받아(파라매터) 저장프로시져를 실행)을 돌릴때 어떤 식으로 구현해야할까용 ㅡㅜ~~복잡스럽네용 2007/09/28

신익준 만약 질문이 난해하다면 만약 실행작업에서 프로시져를 돌릴때 매개변수가 2개일대는 어떠식으로 변수맵핑을 하는건지 궁금합니다^^위에서는 1개일때인데 2개 이상이라면 어떤식으려 변수맵핑을 하는지요 2007/09/28

사이트지기 쿼리 문에서 매개 변수 부분마다 ? 로 처리하시고, 11단계에서 매개변수 이름이 0, 1,2, ... 식으로 매핑해주면 됩니다. 2007/09/28



저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

Foreach 루프 컨테이너를 이용한 패키지 반복 실행  (0) 2011.08.27
패키지 외부에서 데이터 원본 및 대상 설정하기  (0) 2011.08.27
동적 대상 설정하기  (0) 2011.08.27
스크립트 변환을 이용한 RowNumber 및 파생열 만들기  (0) 2011.08.27
잠긴 파일 처리  (0) 2011.08.27

동적 대상 설정하기

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

Question

mysql 자료를 mssql로 이전하는 작업을 하려고 합니다... 

데이터 흐름에서  

odbc 원본(mysql)

         |

     ole db 대상

 

이런식으로 했을때 대상을 테이블로 지정하면 이상없이 넘어오는데 

대상을 sql 쿼리문으로 (날짜를 동적으로 하려고^^) 했을때

에러가 나더군요 대상을 동적으로 처리할때는 어떤식으로 해야하나요

동적내용을 저장프로시져로 해도 안되고 대상에 대한 설정이 궁금합니다...

--쿼리문--

declare @tdate char(10)
declare @Tsql varchar(1000)

set @tdate = left(convert(char(10),dateadd(mm,-1,getdate()),112),6)

SET  @Tsql = 'SELECT a,b from test' + @tdate
 EXEC (@Tsql)

 

 

 

변수에 테이블 이름을 설정하고선, 대상 테이블 이름에 이 변수값을 이용하도록 설정하면 됩니다.

 

 

[방법 1 : 식(Expression)을 이용]

1. String 형 변수를 하나 추가합니다.

 

 

2. 변수를 선택한 후, 속성 부분의 Expression 항목에 있는 … 버튼을 클릭합니다.

(참고 : 서비스 팩 2 이전에는 이 버튼이 없습니다. 서비스 팩 2를 설치하지 않으셨다면 설치하시기 바랍니다.)

 

 

3. 식 작성기에서 다음과 같이 식을 작성합니다.

식 : "[tempdb].[dbo].[test" +  REPLACE(SUBSTRING((DT_WSTR, 30) DATEADD( "MM", -1, GETDATE()), 1, 7), "-","") + "]"

 

4. 확인을 눌러서 식 작성기를 닫은 후, 다시 속성 부분에서 아래와 같이 EvaluateAsExpression 속성 값을 True로 설정합니다.

 

5. 데이터 흐름 영역의 OLE DB 대상의 대상 편집기에서

데이터 액세스 모드(A)를 “테이블 이름 또는 뷰 이름 변수” 또는 “테이블 이름 또는 뷰 이름 변수 – 빠른 로드”로 선택하고, 변수 이름 부분에 위에서 설정한 변수 명을 선택합니다.

 

5. 매핑 탭에서 열 매핑을 설정하고 확인을 누르시면 됩니다.

 

 

 

[방법 2 : SQL 실행 작업을 이용하는 방법]

1. 방법 1에서와 같이 String 형 변수를 하나 추가합니다. 이 때, 값 부분에 임의의 대상 테이블 이름을 입력하도록 합니다. (이 값은 아래 부분에서 변경될 것이지만, 매핑 설정 단계에서 필요하기 때문에 기본값으로 지정해 줘야 합니다.)

 

2. SQL 실행 작업을 하나 추가한 후, 임의의 연결을 설정하고, 다음과 같이 설정합니다.

Ÿ           SQLStatement

declare @tdate char(6)

declare @Tsql varchar(1000)

set @tdate = convert(char(6),dateadd(mm,-1,getdate()),112)

SELECT  '[tempdb].[dbo].[test' + @tdate + ']' as TableName

Ÿ          결과 집합의 ResultSet : 단일 행

          

 

3. 결과 집합 탭에서 추가(A)를 클릭한 후, 결과 이름을 TableName, 변수 이름을 1에서 추가한 [사용자::TargetTable]으로 설정합니다.

 

4. 이제 SQL 실행 작업을 데이터 흐름 작업과 연결한 후, 방법 1의 5단계에서와 같이 OLE DB 대상 부분에서 대상 테이블을 변수로 설정합니다.

 

 

 

[방법 2]는 복잡한 식을 사용하지 않고 SQL 쿼리를 이용해서 변수에 값을 설정하는 방식이며, 경우에 따라서는 SSIS의 식(Expression)을 이용하는 것 보다 편리할 수 있습니다.



관련 글 : Re) SSIS에서 개체전송 작업시 원본 테이블설정

안녕하세요. 답변이 늦었네요.

우선 간략히 말씀드리자면, SQL Server 개체 전송 작업에 대상 테이블을 지정할 수는 없습니다.

일별로 생성되는 테이블을 이관하고자 한다면, 데이터 흐름 작업을 이용하시기 바랍니다.

데이터 흐름 작업의 데이터 흐름 원본 부분에서 변수를 이용한 원본 지정 등을 이용해서 동적으로 설정할 수 있습니다.


다음 글은 대상 부분에 일별로 테이블을 만드는 것인데 이를 참고해서 원본에도 적용하시면 될 것 같네요.
http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=1485




작성자 : 김범준 , 등록일 : 2008-07-01 오후 12:19:00
강좌를 보면서 혼자하다가 안되서 질문에 올려봅니다.
도움 부탁드립니다.

SSIS에서 개체 전송작업 패키지를 만들고 있습니다.
원본DB에서 TB_Log_20080701 과 같은 매일 새로 생성되는 테이블을 타겟DB로 전송하는 작업입니다.
그래서 아래와 같은 수식을 사용하고 싶은데, 안되네요..
수식을 어떻게 작성해야 해당하는 날짜 테이블만 전송하도록 만들수 있을까요..



저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

패키지 외부에서 데이터 원본 및 대상 설정하기  (0) 2011.08.27
루핑 처리 방법  (0) 2011.08.27
스크립트 변환을 이용한 RowNumber 및 파생열 만들기  (0) 2011.08.27
잠긴 파일 처리  (0) 2011.08.27
스크립트 변환으로 RowNumber 구현하기  (0) 2011.08.27

RowNumber와 파생열 구현하기

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

 

 Question

제가 하고자 하는 질문은

'|'구분자를 가지는 TEXT파일의 원본을 가지고 OLE DB에 넣고자합니다.

그냥 DB에 넣는게아니라 한 필드를 이용하여 새로운 필드를 추가하여 넣는것입니다.

openrowset을 이용하여 쿼리문으로 넣는건 만들었는데 SSIS를 이용하자니 막히네요.

제가 openrowset에서 사용했던 쿼리는 아래와 같습니다.

select left('AA980020000000' , 13-len(row_number() over(order by A_NO)))
     + cast(row_number() over(order by A_NO)  as varchar(10)) AS 'KEY'

 

설명을 드리자면 A_NO필드를 기준으로 row_number를 생성합니다. 숫자형태임으로 형변환을 하고

KEY필드의 자리수는  14자리 고정임으로 'AA980020000000'에서 row_number된 자리수만큼을 빼고 row_number를 넣어주는 것입니다.

 

필드명만 바꿔서 select해보시면 어떤 형태를 원하는건지 아실수있으실것입니다.

 

위와같은 방법을 SSIS에 적용하고자하는데 [파생열 변환]을 이용했습니다. 근데 거기서는 over나 row_number등 함수에 대한 제약이 있는듯한다.

위와같은 방법을 실현할수 있는방법이 궁금합니다.  

 

 

질문하신 사항으로 하기 위해서는 입력 데이터에 대해 Row_Number 기능이 구현되어야 합니다. Row_Number를 간단히 구현하는 방법으로는 스크립트 변환을 이용하면 됩니다.

 

1. 다음과 같은 텍스트 파일을 원본 데이터로 이용하겠습니다.

 

2. AA980020000000라는 값이 상황에 따라 바뀔 것도 같습니다. 이 값을 변수에 저장하도록 합시다. 만약 값이 변하더라도 변수의 값만 변경하면 되겠지요.

제어 흐름 영역의 빈 곳을 선택한 후, 마우스 오른쪽 클릭해서 나타나는 메뉴 중 변수(S)를 선택하고선 KeyCol이라는 String형 변수를 하나 추가하고, 기본 값으로 AA980020000000을 설정합니다.

 

 

3. 데이터 흐름 작업을 추가한 후, 해당 데이터 흐름 영역에서 플랫 파일 원본을 추가한 후, 위의 텍스트 파일에 대한 열을 설정합니다.

 

4. 스크립트 구성 요소(변환)을 추가한 후, 플랫 파일 원본과 연결합니다.

 

5. 스크립트 구성 요소를 더블 클릭해서 편집기를 연 다음, 입력 열 탭에서 사용 가능한 입력 열 부분을 체크합니다.

 

6. 입/출력 탭에서 출력 0 부분을 확장한 후, 출력 열을 선택하고선, 아래에 있는 열 추가(C)를 클릭해서 새로운 열을 하나 추가합니다. 새로 추가한 열 이름은 RowNum이라 하고, DataType을 부호 없는 4바이트 정수(DT_I4)로 설정합니다.

 

7. 스크립트 탭에서 스크립트 디자인(S)를 클릭해서 VSA를 띄운 후, 다음과 같은 스크립트를 입력합니다.

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

 

    Dim i As Integer

 

    Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)

        i = i + 1

        Row.RowNum = i

 

    End Sub

 

    Public Sub New()

        i = 0

    End Sub

End Class

 

 

 

자, 여기까지가 RowNumber를 추가하는 부분을 구현하였습니다. VB.net 스크립트라서 복잡하게 보이지만, 몇 번 하다 보면 금방 익숙해지실 것입니다.

 

 

8. 이제, 도구 상자에서 파생 열 변환을 추가한 후, 스크립트 구성 요소와 연결합니다.

 

9. 파생 열 변환 편집기에서 다음과 같은 파생 열을 추가합니다.

 

식 : SUBSTRING(@[사용자::KeyCol],1,14 - LEN((DT_STR,14,949)RowNum)) + ((DT_STR,14,949)RowNum)

 

10. 추가된 데이터를 확인해 보면 다음과 같습니다.

 

 

 

 

SSIS의 파생 열 변환을 이용해서 이와 같이 구현하시면 됩니다.

하지만~!! 복잡합니다.

 

이러한 방법은 어떨까요?

 

6단계부터 다음과 같이 해 봅시다.

6’. RowNum 열 뿐만 아니라 Key라는 열까지 추가합니다. KeyCol 열은 String형, 14자리로 설정합니다.

 

7’. 스크립트 탭에서 ReadOnlyVariables 부분에 KeyCol이라고 입력한 후, 아래 스크립트 디자인(S)을 클릭해서 VSA를 엽니다.

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

 

    Dim i As Integer

    Dim keyStr As String

 

    Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)

        i = i + 1

        keyStr = Me.Variables.KeyCol.ToString

 

 

        Row.RowNum = i

        Row.Key = keyStr.Substring(0, keyStr.Length - i.ToString.Length) + i.ToString

 

    End Sub

 

    Public Sub New()

        i = 0

 

    End Sub

End Class

 

 

 

 

스크립트 구성 요소가 어렵게 보일 수도 있지만, 복잡한 로직을 간단히 구현할 수 있는 유용한 기능입니다.

 

 

저작자표시

'연구개발 > DTS & SSIS' 카테고리의 다른 글

루핑 처리 방법  (0) 2011.08.27
동적 대상 설정하기  (0) 2011.08.27
잠긴 파일 처리  (0) 2011.08.27
스크립트 변환으로 RowNumber 구현하기  (0) 2011.08.27
TEXT 형이 포함된 Excel 데이터 가져오기 문제  (0) 2011.08.27

+ Recent posts

티스토리툴바