'연구개발/DTS &amp; SSIS' 카테고리의 글 목록 (3 Page)

잠긴 파일 처리

한대성

MS SQL Server MVP

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

Question

File System Task나 기타 File 관련 작업을 하는 Task를 중에서 다른 Process가 해당 파일을 사용하고 있으면 이것을 Skip하고 싶은데, 어떤 예외처리를 해줘야 하는지..

간단한 예로 특정 파일을 열고 있는 상태에서 해당 파일을 지우는 작업을 수행하게 되면 다음과 같이 에러가 발생합니다.

Foreach 루프 컨테이너 등을 이용하여 여러 파일을 삭제할 때 이와 같이 다른 프로세스에서 사용중인 파일은 건너뛰도록 설정하는 방법에 대해 살펴보겠습니다.

간단히 해당 작업의 MaximumErrorCount 속성을 1에서 2로만 변경해도 에러가 발생했을 때 에러라고 출력하지 않고 그냥 성공으로 처리합니다.

하지만, 이렇게 설정한 경우에는 다른 프로세스에서 사용 중이어서 삭제가 실패한 경우 외에도 파일이 없는 경우와 같이 다른 모든 유형에 대해서도 에러를 발생하지 않고 성공으로 처리하기 때문에 문제가 있을 수 있습니다.

다른 일반적인 방법으로 선행 제약 조건에서 실패로 경로를 설정한 후, 후행 작업을 구현할 수도 있습니다.

일반적인 방법이긴 하지만 필자 개인적으로는 선호하지 않는 유형입니다.

제어 흐름 내에서 한 작업의 실패는 상위 컨테이너까지 전파됩니다. 즉, 위와 같이 파일 시스템 작업이 실패가 되어 다음 단계로 진행되어서 정상적으로 처리가 되었더라도 해당 작업의 상위 컨테이너인 패키지는 자식 구성 요소 중에 실패한 놈(^^)이 있기 때문에 실패로 처리합니다.

간단한 예로, 위의 패키지를 SQL Agent의 작업으로 등록해서 실행하면 다음과 같이 실패로 처리됩니다.

이러한 이유 때문에 가급적 패키지에서 에러를 발생시키지 않는 방법으로 구현하고자 합니다.

다음과 같은 방법은 어떨까요? 파일 시스템 작업 전에 해당 파일의 잠금 상태를 확인한 후, 해당 파일이 잠겨있을 경우에는 작업을 진행하지 않도록 수행하는 것입니다.

이를 구현하기 위해 우선 패키지에 FileLockYN 이라는 String형 변수를 하나 추가합니다.

파일 시스템 작업 전에 스크립트 작업을 추가한 후, 다음과 같이 설정합니다.

스크립트 작업의 ReadWriteVariables에 방금 추가한 FileLockYN 변수를 설정하고, 스크립트 디자인(S)를 열어 다음과 같은 스크립트를 입력합니다. 본 예제에서는 TargetFile이라는 파일 연결을 사용합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Imports System.Security.Principal

Imports System.Security.Permissions

Public Class ScriptMain

Public Sub Main()

Dim FileName As String

FileName = Dts.Connections("Target").ConnectionString

If IsFileInUse(FileName) < 0 Then

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

Else

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

End If

Dts.TaskResult = Dts.Results.Success

End Sub

Function IsFileInUse(ByVal filename As String) As Integer

If File.Exists(filename) Then

On Error GoTo noFileHandles

Dim oFp As FileIOPermission = New FileIOPermission(FileIOPermissionAccess.Write, filename)

oFp.Assert()

Dim objWriter As New IO.StreamWriter(File.Open(filename, IO.FileMode.Open))

objWriter.Close()

objWriter = Nothing

IsFileInUse = 1

GoTo endFunc

Else

GoTo noSuchFile

End If

noSuchFile:

IsFileInUse = -3

Exit Function

inUse:

IsFileInUse = -1

Resume endFunc

noFileHandles:

IsFileInUse = -2

Resume endFunc

endFunc:

End Function

End Class

그런 다음, 스크립트 작업과 파일 시스템 작업 사이의 선행 제약 조건(연결선)에 다음과 같은 조건식을 입력합니다.

만약 파일이 잠겨 있는 경우에는 다음과 같이 삭제 작업으로 진행하지 않습니다.

파일이 잠겨있지 않은 경우에는 다음과 같이 삭제 작업을 진행합니다.

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

저작자표시

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

동적 대상 설정하기 (0)	2011.08.27
스크립트 변환을 이용한 RowNumber 및 파생열 만들기 (0)	2011.08.27
스크립트 변환으로 RowNumber 구현하기 (0)	2011.08.27
TEXT 형이 포함된 Excel 데이터 가져오기 문제 (0)	2011.08.27
부모 패키지에서 자식 패키지 변수값 읽어오기 (0)	2011.08.27

스크립트 변환으로 RowNumber 구현하기

한대성

질문 원본: http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intSeq=1223

SSIS 에서,

1. 컬럼에 랭크(값이 높은 순 혹은 낮은 순, 가나다라 순, ABCD순 등)를 주는 방법이 있습니까?

2. 정렬된 값에 시퀀스를 주는 방법이 있을까요? ->(이런 방법으로 랭크를......^^)

SQL Server 2005 에서,

3. 오라클에서는 Rownum 이라는 것이 있잖아요. SQL Server 에서 같거나 유사한 것이 있습니까?

다음 링크에 비슷한 예제가 있긴 하지만, 제차 소개하겠습니다.

다음과 같은 정렬된 데이터에 대해 시퀀스를 붙이는 경우를 예로 들겠습니다.

(데이터 원본의 쿼리에서 정렬을 하거나 정렬 변환을 이용해서 원하는 형태로 정렬을 수행해도 됩니다.)

스크립트 구성 요소-변환 을 추가한 후에 원본과 연결합니다.

스크립트 변환 편집기의 입/출력 탭에서 출력0을 확장한 후, 출력 열 부분에 열을 하나 추가합니다. 본 예제에서는 OrderSeq라는 4바이트 정수(DT_I4)형으로 추가하였습니다.

스크립트 탭에서 스크립트 디자인(S)를 클릭하여 VSA(Visual Studio for Applications)을 연 후, 다음과 같이 스크립트를 입력합니다. (굵은 글씨가 추가한 스크립트 입니다.)

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 OrdSeq As Integer

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

Row.OrderSeq = OrdSeq

OrdSeq = OrdSeq + 1

End Sub

Public Sub New()

OrdSeq = 1

End Sub

End Class

VSA를 닫고, 임의의 데이터 대상을 연결시킨 후, 데이터 뷰어를 통해 처리되는 결과를 확인해 보면 순번 열이 추가된 것을 확인하실 수 있습니다.

스크립트를 응용하면 누적 합 또는 n개 행 평균 등과 같은 다양한 연산들을 구현할 수 있습니다.

만약, 스크립트로 구현하기 싫다면 다음 링크에 있는 변환을 다운 받아서 사용하면 됩니다. 하지만, 제 개인적인 취향(^^)은 변환을 추가 설치하는 것을 좋아하지는 않습니다. 만약, 회사 프로젝트 단위로 중요한 기능인 경우에는 사용해야 하겠지만, 이와 같이 단순한 기능인 경우에 이용하려고 한다면 패키지를 실행하거나 편집할 서버 또는 PC에 해당 컴포넌트를 일일이 설치하고 등록해야 하며, MS 공식적인 컴포넌트가 아닌 경우에 안정성을 의심해야 하기 때문입니다.^^

참고 : http://www.sqlis.com/93.aspx

두 번째 질문으로, 오라클의 RowNum과 같은 기능이 MSSQL 2005에도 있습니다.

예제 쿼리 입니다. 온라인 도움말에서 RANK(), DENSE_RANK(), ROW_NUMBER(), NTITLE()을 참고하시기 바랍니다.

SELECT

RANK() OVER(ORDER BY ManagerID) AS RANK,

RANK() OVER(PARTITION BY ManagerID ORDER BY Title) AS PART_RANK,

DENSE_RANK() OVER(ORDER BY Title) AS DENSE_RANK,

ROW_NUMBER() OVER(ORDER BY Title) AS ROW_NUM,

NTILE(4) OVER(ORDER BY Title) AS NTILE_4,

NationalIDNumber, Title, ManagerID

FROM HumanResources.Employee

ORDER BY Title, ManagerID

참고하시기 바랍니다.

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

저작자표시

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

스크립트 변환을 이용한 RowNumber 및 파생열 만들기 (0)	2011.08.27
잠긴 파일 처리 (0)	2011.08.27
TEXT 형이 포함된 Excel 데이터 가져오기 문제 (0)	2011.08.27
부모 패키지에서 자식 패키지 변수값 읽어오기 (0)	2011.08.27
SQL 2005 Agent 작업에서 프록시 설정하기 (0)	2011.08.27

qBank[0].sql

qBank_org.xls

TEXT 형이 포함된 Excel 데이터 가져오기 문제

한대성

질문 원본: http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intSeq=1218

말씀해주신 내용을 확인하고 적용해 보았으나 여전히 안됩니다.

그래서 DB에 입력되어 있는 내용을 '데이터 내보내기'를 통해 엑셀로 저장하고 동일하게 새로운 테이블 생성하고 그곳에 '데이터 가져오기'를 시도했습니다.

오류검사는 통과가 되었지만 짤림현상으로 인해 실행을 중지한다는 메시지와 함께 정지가 되더군요.

'데이터 내보내기'를 통해 가져온 데이터를 다시 넣는데 바로 안되는 이유는 무엇인가요?

'데이터 내보내기'를 통해 저정한 엑셀파일도 함께 보냅니다.

우선, Excel의 데이터를 Excel Driver로 읽어올 때에는 다음과 같은 여섯 가지의 유형으로 변환해서 읽어오게 됩니다.

Excel 유형	변환 유형
Currency	Currency(DT_CY)
Boolean	Boolean(DT_BOOL)
Date/time	Date(DT_DATE)
String	Unicode string, Length 255(DT_WSTR)
Memo	Unicode text stream(DT_NTEXT)

(참고 : http://msdn2.microsoft.com/en-us/library/ms141683.aspx)

위의 표에서와 같이 Excel의 문자형 데이터는 모두 Unicode형 255자리의 데이터로 인식을 하게 됩니다.

그런데 첨부하신 엑셀 파일의 데이터를 잠시 살펴보겠습니다.

tBody 열의 크기를 len() 함수를 이용해서 확인해 본 결과 위와 같이 439, 650 등과 같이 255자리를 넘습니다.

그럼 SSIS, 즉 데이터 가져오기/내보내기에서 에러가 나는 원인에 대해 설명 드리겠습니다.

해당 엑셀 파일을 가져오기/내보내기를 통해 설정한 후, 실행하지 말고 SSIS 패키지로 저장하면 다음과 같은 패키지가 만들어집니다.

여기서, 원본 – qBank$ 부분이 엑셀 파일에서 데이터를 읽어오는 부분이며, 읽어온 데이터를 데이터 변환 1이라는 변환 작업으로 출력합니다. 이 때, SSIS 엔진에서는 읽어오는 데이터들의 열 정보를 파악하게 되는데, Excel Driver를 통해서 각 열의 메타 정보를 읽고 설정하게 됩니다.

tBody열이 String형이기 때문에, 무조건 그냥 DT_WSTR(Unicode형) 255자리로 설정되었습니다.

에러는 여기에서 발생합니다. 만약 패키지를 실행시키면, 원본 – qBank$라는 작업에서 실제 엑셀 파일(qBank_org.xls)에서 데이터를 읽어오게 됩니다. 이 때 tBody의 자릿수는 255자리라고 설정을 했는데, 들어오는 데이터는 이 크기 이상의 데이터가 들어오게 되어 잘림 오류가 발생하는 것입니다.

이전 버전인 DTS에서도 이와 비슷한 에러가 발생됩니다. (Excel Driver를 이용하는 건 동일하기 때문이겠지요..)

좀 더 명확히 확인하기 위해 다음과 같이 쿼리 분석기에서 쿼리를 수행하고 결과 테이블을 생성한 후에 열을 확인해 보겠습니다.

SELECT * INTO AAA

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=d:\qBank_org.xls', 'Select * from [qBank$]')

--(80개 행 적용됨)

SP_COLUMNS AAA

우선, 모든 String형 데이터가 255 자리로 인식되는 것을 확인할 수 있습니다. 그리고 에러는 발생하지 않았습니다. 하지만, 255자리가 넘는 데이터는 에러 없이 그냥 255자리로만 잘려서 입력됩니다.

결론은,

『255 자릿수를 넘는 문자형 데이터를 가진 엑셀 데이터를 원본으로 사용할 경우에는 잘림 에러가 발생한다. 』 입니다.

만약, 에러가 발생되면 해당 행을 버리고서라도, 또는 별도의 경로로 에러 정보를 관리하고자 한다면 오류 처리라는 것을 이용해야 합니다.

오류 처리는 가져오기/내보내기 부분에서 직접 설정할 수는 없으며, SSIS 패키지로 저장한 후에 설정해야 합니다. 원본 – qBank$을 더블 클릭해서 편집기를 연 다음, 오류 출력 탭에서 각 열 별로 오류 출력에 관한 사항을 설정할 수 있습니다.

(자세한 것은 다음 세미나 자료 중, Demo2_3 참고)

그럼 어떻게 해야 하냐면,

1. 엑셀 대신 Access나 다른 DBMS를 직접 지정해서 사용합니다.

2. 또는,

A. 텍스트 형 열 데이터와 일반 데이터를 분리해서 생성합니다. 일반 데이터를 엑셀로 내보내고 가져오는 데에는 특별한 문제가 없습니다.

B. 텍스트 형 열의 데이터는 엑셀 파일 외에 Access 또는 텍스트 한정자를 지정한 플랫 파일 등을 이용합니다.

물론 더 다른 방법들도 있을 것인데, 테스트 해 보고 좋은 방법이 있으면 덧붙이겠습니다.

참고하시기 바랍니다.

저작자표시

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

잠긴 파일 처리 (0)	2011.08.27
스크립트 변환으로 RowNumber 구현하기 (0)	2011.08.27
부모 패키지에서 자식 패키지 변수값 읽어오기 (0)	2011.08.27
SQL 2005 Agent 작업에서 프록시 설정하기 (0)	2011.08.27
오늘 날짜에 추가된 파일만 복사해오기 (0)	2011.08.27

부모자식패키지데모.zip

부모 패키지에서 자식 패키지 변수값 읽어오기

한대성

MS SQL Server MVP

에이디컨설팅 | SQLLeader.com 운영자

작성자 : 야추 , 등록일 : 2007-04-13 오후 3:52:00

...

지금 난간에 빠져있는게 하나 있는데...

가령

패키지안에서 패키지실행작업으로 패키지를 호출하는 게 있다고 할때,

부모 패키지가 자식 패키지의 변수를 가져와서 그 값을 읽을 수 있는지?

자식 패키지에서는 부모 패키지의 값을 패키지 구성 마법사로 읽어 들이는 것 같은데,

반대의 경우에는 어떻게 해야 하는지.. 몰라서요..

즉, 자식 패키지에서 변환이 이뤄진 데이타 건수를

부모 패키지에서 건수로 판단해, 에러, 정상으로 처리하려고 하거든요..

...

작년(2006년) 1월 MCPWorld 세미나 때 발표한 데모가 있습니다. (여기 글 참고)

질문 올라오면 설명해야지 했는데..드뎌 올라왔네요.^^ 감사..ㅎㅎ

잠깐 정리하고 설명드리겠습니다.

SSIS는 "내리 사랑" 입니다.

즉, 자식은 부모의 모든 것을 읽어서 이용할 수 있습니다. 하지만~! 부모는 자식의 것을 이용할 수 없습니다.

이것을 컨테이너 개념으로 생각하면 좀 더 간단합니다.

세미나나 교육 때 자주 언급하는 사항으로

『SSIS 패키지도 하나의 컨테이너 입니다. 따라서 SSIS는 총 5개의 컨테이너가 있습니다.』

1. 시퀀스 컨테이너

2. For 루프 컨테이너

3. Foreach 루프 컨테이너

4. 작업 호스트 컨테이너

5. 패키지 컨테이너

입니다.

라는 것입니다. 1~3은 작업 개체로 있고, 4는 작업 자체를 말하는 것이고, 5가 개념적인 것입니다.

즉, 부모-자식 패키지 관계에서 자식 패키지도 하나의 컨테이너로 생각할 수 있다라는 것이지요.

이렇게 생각하면 좀 더 명확하겠죠. 패키지 내에 있는 컨테이너는 컨테이너 수준에서 정의된 변수 및 패키지 전체 수준의 변수를 다 사용할 수 있습니다. 하지만, 패키지 수준에서는 컨테이너 내부의 변수를 읽을 수 없습니다.

여기까지가 원칙적인 이야기이고, 이번 글에서는 반대의 경우를 구현하는 방법에 대해 살펴보겠습니다.

부모 패키지가 자식 패키지를 호출한 후, 자식 패키지의 변수값, 또는 처리된 결과 값을 읽어오는 경우입니다.

자식 패키지가 하나의 함수 또는 라이브러리 역할을 하는 것이지요.

이를 구현하기 위해서는 스크립트 작업을 이용해서 구현해야 합니다.

a. 스크립트 작업에서 패키지 개체를 생성한 후,

b. 자식 패키지로 설정하고,

c. 패키지를 실행하고,

d. 패키지 개체의 변수값을 읽어오는

방식입니다.

다음의 스크립트를 참고하시기 바랍니다.

Imports System

Imports System.Data

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

MsgBox(Dts.Variables("ParentVar").Value.ToString, _

MsgBoxStyle.Information, "부모 패키지 초기값")

Dim pkg As String

pkg = "d:\Demo4_2_자식패키지.dtsx"

Dim app As Application = New Application()

Dim p As Package = app.LoadPackage(pkg, Nothing)

'자식 패키지에 값을 전달할 때 : 부모 패키지의 ParentVar값을 자식 패키지의 ChildVar로 전달

'p.Variables("ChildVar").Value = Dts.Variables("ParentVar").Value

p.Execute()

'자식 패키지에 값을 읽어올 때 : 자식 패키지의 ChildVar 값을 부모 패키지의 ParentVar에 저장

Dts.Variables("ParentVar").Value = p.Variables("ChildVar").Value

MsgBox(Dts.Variables("ParentVar").Value.ToString, _

MsgBoxStyle.Information, "부모패키지변경값")

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

데모 패키지를 참고하세요.

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

저작자표시

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

스크립트 변환으로 RowNumber 구현하기 (0)	2011.08.27
TEXT 형이 포함된 Excel 데이터 가져오기 문제 (0)	2011.08.27
SQL 2005 Agent 작업에서 프록시 설정하기 (0)	2011.08.27
오늘 날짜에 추가된 파일만 복사해오기 (0)	2011.08.27
T-SQL과 JET-SQL 쿼리 비교 (0)	2011.08.27

SQL 2005 Agent 작업에서 프록시 설정하기

한대성

MS SQL Server MVP

에이디컨설팅 | SQLLeader.com 운영자

질문자 : 배우미 , 등록일 : 2007-04-12 오전 10:17:00

안녕하세요.

질문좀 드릴가 해서요 ^^a

Package 파일을 Agent에 올려서 실행 하는데 실행이 자꾸 안되구 아래와 같은 에러 메시지만 뜨는데 ㅠ.ㅡ 도통 모르겠습니다.

그냥 파일을 실행하거나 VS에서 실행하면 문제 없이 잘 되거든여

참고로 해결방안이라고 하시는 보안 변경 모두 해봐도 같습니다... ㅠ.ㅡ

>> Error Message : The job failed. The Job was invoked by User JOBMA\kkk. The last step to run was step 1 (HS_AS). Executed as user: JOBMA\SYSTEM. The package execution failed. The step failed.

SQL Agent에 SSIS 패키지를 실행했을 때 수행이 안 되는 몇 가지 경우가 있는데, 대부분이 권한 문제로 인해서 발생합니다. 작업을 실행시키는 사용자가 패키지를 실행 시킬 권한이 없다거나 패키지의 보안 설정과 다른 경우에 발생할 수 있습니다.

현재 질문하신 내용으로 보아, 패키지를 만든 계정과 수동으로 실행해서 성공한 계정은 JOBMA\kkk 인데, 실제 패키지가 SQL Agent 작업에서 실행할 때에는 JOBMA\SYSTEM 이라는 계정으로 호출되어 에러가 발생한 것 같습니다.

이 경우, 다음과 같은 방법도 하나의 해결 방안이 될 수 있어서 소개 드립니다.

SQL Server 2000 Agent에서 작업을 등록할 때 아래와 같이 작업을 수행할 때 사용할 계정을 지정할 수 있습니다.

즉, 작업 자체(Agent Job)를 실행시키는 것은 다른 사용자이더라도, 작업 내용을 실행할 때에는 다른 계정으로 사용하라고 지정하는 것입니다.

이와 마찬가지로 SQL 2005에서도 이러한 사용자 지정을 설정할 수는 있지만, 약간 달라졌습니다. 직접 사용자를 지정하는 대신, 『자격 증명』과 『프록시』라는 것을 이용합니다.

SSMS에서 아래와 같이 [보안] -> [자격 증명] 을 선택한 후, 새로운 자격을 등록합니다.

자격 증명 이름은 적절히 정하고, ID에는 현재의 사용자 계정(예 : JOBMA\kkk)을 지정하고 암호르 입력합니다.

SQL Server 에이전트에서 아래와 같이 프록시 부분에서 새 프록시를 생성합니다.

프록시 이름을 적절히 지정하고, 자격 증명 이름에 좀 전에 추가한 자격 증명을 선택합니다. 그런 다음, 다음 하위 시스템에 대해 활성화(A) 부분에서 SQL Server Integration Services 패키지를 선택합니다.

이제, SQL Agent 의 패키지 실행 작업을 연 후, 작업 단계 속성에서 다음 계정으로 실행(R)부분에 좀 전에 추가한 프록시를 지정합니다.

이러한 프록시 설정은 SSIS 패키지 외에도 다른 작업 유형에도 적용 가능한 사항입니다.

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

저작자표시

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

TEXT 형이 포함된 Excel 데이터 가져오기 문제 (0)	2011.08.27
부모 패키지에서 자식 패키지 변수값 읽어오기 (0)	2011.08.27
오늘 날짜에 추가된 파일만 복사해오기 (0)	2011.08.27
T-SQL과 JET-SQL 쿼리 비교 (0)	2011.08.27
엑셀 파일과 테이블 데이터 동기화하기 (0)	2011.08.27

20070406_TodayFileCopy.dtsx

오늘 날짜에 추가된 파일만 복사해 오기

한대성

MS SQL Server MVP

에이디컨설팅 | SQLLeader.com 운영자

작성자 : 배우미 , 등록일 : 2007-04-06 오전 11:26:00

안녕하세요 ^^

전에 질문했던 내용에 답변 해주신 내용 많은 도움이 되었습니다.

감사 합니다.

------질문-------

1일 1번 지정된 폴더의 기존 있던 파일이 아닌 그날 생성된 파일을 전송 하고자 합니다.

FTP 전송 이나 파일전송을 이용 하면 될거 같은데..

문제는 새로 생성된 파일만 알아야 한다는 문제점이 있습니다.

이런 경우 어떤 방법으로 해야 효율적일지 해서 질문을 올리게 되었습니다.

그럼 수고 하세용 ^^a

안녕하세요^^

여러 방법 중에 다음과 같은 형태로 설명 드리겠습니다.

우선 파일 정보를 한 번 보겠습니다.

여기서의 만든 날짜는 파일이 생성된 날짜가 아니고, 해당 디렉터리에 파일이 만들어진 날짜입니다. 외부에서 이 디렉터리(D:\Temp)에 복사해 온 날짜이거나, 만약 이 파일이 이 디렉터리에서 새로 만들어진 경우라면 파일의 생성 날짜가 되겠지요.

이 정보를 이용하면 될 것 같습니다.

a. Foreach 루프 컨테이너로 가지고 올 파일의 경로에 있는 모든 파일 이름을 읽어오도록 설정하고,

b. 가지고 온 파일 명을 이용하여, 어떠한 방법을 이용하여 파일의 만든 날짜를 얻은 후,

c. 만든 날짜가 오늘인 경우에만 파일을 복사해 오도록 구현

하면 될 듯 합니다.

Foreach 루프 설정은 간단한데, b번의 어떠한 방법은? SSIS의 기본 작업 개체로는 없습니다. 대신, 간단히 스크립트 작업을 이용하도록 합시다.

그런 후, 파일의 날짜를 얻은 후 c와 같이 판단을 하려면? 날짜를 변수에 저장시키고, 선행 제약 조건의 조건 식을 이용하여 오늘 날짜인지를 판별하여 조건이 맞은 경우에만 진행하면 될 것 같습니다.

1. 다음과 같이 Foreach 루프 컨테이너를 추가한 후, Foreach File 열거자를 선택하고(기본적으로 선택되어 있을 것입니다.), 폴더(F)를 C:\Windows로 정하고, 파일(I)을 *.bmp 로 변경합니다.

변수 매핑 탭에서 파일 이름을 저장할 변수를 설정합니다. 변수가 없는 경우에는 String형으로 하나 추가해 줍니다.

이제, C:\Windows 폴더 내에 있는 bmp 파일들의 이름을 차례대로 읽어서 FileName 변수에 저장하도록 설정은 되었습니다.

2. FileCreateDate라는 String 형 변수를 하나 더 추가합니다. 이 때 범위는 Foreach 루프 컨테이너만으로 한정되어도 상관없습니다.(전역 변수라도 상관 없습니다.)

3. Foreach 루프 컨테이너 안에 스크립트 작업을 하나 추가한 후, ReadOnlyVariables에는 FileName, ReadWriteVariables에는 FileCreateDate 변수를 설정합니다.

그런 다음, 스크립트 디자인(S)를 눌러 VSA를 열고선 다음과 같은 스크립트를 입력합니다.

Imports System

Imports System.Data

Imports System.Math

Imports System.IO

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim FileName As String = Dts.Variables("FileName").Value.ToString

If File.Exists(FileName) Then

Dts.Variables("FileCreateDate").Value = _

File.GetCreationTime(FileName).Date.ToString.Substring(0,10)

End If

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

스크립트는 대충~ 아시겠지요? 파일 있으면 파일의 날짜 읽어서 변수에 이 날짜를 입력하라는 것입니다.

4. 이제, 파일 시스템 작업을 추가하고, 스크립트 작업과 연결합니다. 파일 시스템 작업의 설정은 다음과 같이 FileName 변수를 이용해서 파일을 복사해 오도록 설정하고, 대상 위치는 필요한 곳으로 정합니다.

그런데, 여기서 잠깐..!!

설정을 마쳤는데도, 빨간 경고가 뜨네요. FileName이라는 변수에 값이 없어서 그렇다라는 것인데, 이것은 유효성 검사로 인해서 나타나는 것입니다.

파일 시스템 작업을 선택하고선, 속성 창을 보면 DelayValidation 옵션이 있습니다. 이 값을 False에서 True로 변경합니다. 이 속성에 대해서는 다음 링크의 글을 참고하세요.

5. 스크립트 작업과 파일 시스템 작업 간의 연결선인 선행 제약 조건을 더블 클릭한 후, 다음과 같은 식을 추가합니다.

평가 작업(E)을 식 및 제약 조건으로 변경하고, 식(X) 부분에 다음과 같은 조건식을 입력합니다.

@FileCreateDate == SUBSTRING((DT_STR, 32, 949)GETDATE(), 1, 10)

자, 설정이 끝났습니다. 임의로 C:\Windows 폴더에 오늘 날짜로 bmp 파일을 생성시킨 후, 패키지를 테스트 해 봅시다.

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

저작자표시

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

부모 패키지에서 자식 패키지 변수값 읽어오기 (0)	2011.08.27
SQL 2005 Agent 작업에서 프록시 설정하기 (0)	2011.08.27
T-SQL과 JET-SQL 쿼리 비교 (0)	2011.08.27
엑셀 파일과 테이블 데이터 동기화하기 (0)	2011.08.27
외부에서 패키지 값 속성 설정하기 (0)	2011.08.27

T-SQL과 JET-SQL 쿼리 비교

한대성

MS SQL Server MVP

에이디컨설팅 | SQLLeader.com 운영자

다음과 같은 질문에 대해 다음과 같이 파생열 변환 또는 OPENROWSET 을 이용하라고 답변을 드렸습니다.

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

Question)

다름이 아니오라~ 엑셀 파일을 db에 패키지로 만들어 가져오는데

원본 파일의 특정 필드에 대해 FLAG같은걸 만들어서 새로운 필드를 추가할려고 하는데

테이블 복사 스크립트에서 SQL의 CASE문을 쓸려고 했는데 계속 구문 오류가 나드라구요

예)

select *, case when type='매출' then '01' else '02' end as TYPE_CODE

from `경영계획$`

위와 같이 흔한 SQL문의 CASE문을 썻습니다. 왜 구문 오류가 나는건지요?

아니면 다른 방법이 있는건가요?

하지만, 답변을 드려놓고도 진짜 안될까~ 라는 생각에 여러 방법을 찾다보니 다음과 같이 처리할 수도 있는 것을 알게 되어 소개합니다.

Excel, Access 등과 같이 JetEngine을 이용하여 데이터를 가져오는 SQL 쿼리와 SQL Server의 데이터를 이용하는 T-SQL 쿼리는 상당히 많이 비슷하지만, 몇 가지 차이가 있습니다.

우선, 위의 경우는 다음과 같은 쿼리를 이용하면 됩니다.

SELECT * , IIF(Type="매출", "01","02") as TYPE_CODE

FROM `경영계획$`

이와 관련된 글이 있어서 다음과 같이 비교/ 정리해봤습니다. 참고하시기 바랍니다.

구분	T-SQL	JET SQL
Null 처리	COALESCE(Value, ValueToReturnIfNull) or ISNULL(Value, ValueToReturnIfNull)	NZ(Value, ValueToReturnIfNull)
Null 체크	WHERE Value IS NULL	WHERE Value IS NULL or WHERE ISNULL(Value)
문자열 중 부분 읽어오기	SUBSTRING(StringVal, StartPos, length ) ※ length 필수	MID(StringVal, StartPos, [length]) ※ length 옵션
문자열 내에서 문자 찾기	CHARINDEX(start, StringToSearch, StringToFind)	INSTR(start, StringToSearch, StringToFind)
문자열을 반대로 바꾸기	REVERSE(StringVal)	STRREVERSE(StringVal)
데이터 포멧 설정	어플리케이션이나 리포트에서 설정해야 함	Format(Value, FormatSpecification) ※ 출력 결과는 String형
LIKE 패턴 매칭	WHERE Column LIKE '%string%' WHERE Column LIKE '_string_' WHERE Column LIKE '[^a-z]'	WHERE Column LIKE "string" WHERE Column LIKE "?string?" WHERE Column LIKE "[!a-z]"
공백 제거	RTRIM(LTRIM(val))	TRIM(val)
데이터 유형 변경	CONVERT(DATATYPE, value) or CAST(value AS datatype)	CINT(value), CDBL(value), CDEC(value), CSTR(value), CDATE(value),CBOOL(value)
조건 처리	CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END	IIF(Condition, ReturnIfTrue, ReturnIfValue)
날짜 데이터 처리	WHERE SomeDate = '2006-01-01' ※ 문자열이 날짜로 암시적 변환이 일어나는 것임.	WHERE SomeDate = #1/1/2005#
날짜 데이터 함수	Date() 함수 이용	DATESERIAL(year,month,date)
시간 데이터 함수	Time() 함수 이용	TIMESERIAL(Hour, minute, second)
현재 날짜 및 시간 얻기	SELECT getdate()	SELECT now()
Boolen 값	WHERE Active=1 or WHERE Active=0 (Active 는 bit 데이터 타입)	WHERE Active = True or WHERE Active = False (Active는 Boolean 데이터 타입)
문자열 사용	SELECT 'This is a string'	SELECT "This is a string"
나머지(Modulo) 구하기	SELECT value1 % value2	SELECT value1 MOD value2
문자열 붙이기	Val1 + Val2	Val1 & Val2
SELECT 문에서 열 별칭 사용	예) A + B를 X로 정의한 경우 SELECT X, X+C as D FROM (SELECT A+B as X, C FROM ... ) tmp	예) A + B를 X로 정의한 경우 SELECT A+B as X, X+C as D FROM ...

참고 : http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

저작자표시

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

SQL 2005 Agent 작업에서 프록시 설정하기 (0)	2011.08.27
오늘 날짜에 추가된 파일만 복사해오기 (0)	2011.08.27
엑셀 파일과 테이블 데이터 동기화하기 (0)	2011.08.27
외부에서 패키지 값 속성 설정하기 (0)	2011.08.27
파일 처리 패키지 구현 (0)	2011.08.27

엑셀데이터동기화.zip

엑셀 파일과 테이블 동기화 구현

한대성

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

Question : MS-SQL 2005가 설친된 서버의 특정 폴더의 특정 엑셀 파일을 읽어서 DB에 Import시키려고 합니다.

이걸 패키지로 만들어 작업 스켸줄을 걸어서 매일 1시간에 한번씩 읽어서 DB에 있는 값을 비교 한후 새로운 데이터만

Impot하려고 하는데...또는 기존 테이블의 데이터를 TRUNCATE(인덱스와 PK때문에 DROP은 안뎁니다..ㅠㅠ)시키고

계속 데이터가 쌓이는 엑셀 파일을 Import해도 괜찮습니다..(이왕이면 첫번째 방법을 원하긴 하지만..ㅠㅠ)

이걸 패키지로 만드는 패턴도 예제라도 알려주시면 정말 감사하겠습니다..ㅠㅠ

다음과 같은 질문이 들어와서 이와 관련되어 간단한 예제를 만들어 설명드리겠습니다.

기존의 조회 변환을 이용한 데이터 동기화 작업과 비슷하지만 엑셀 파일을 이용한다는 차이가 있긴 하지만 거의 비슷합니다..

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

우선, 다음과 같은 InputExcel.xls이라는 예제 엑셀 파일이 있다고 하고,

이 데이터를 localhost의 tempdb의 TargetTable 테이블에 추가 시키는 경우로 예를 들겠습니다.

USE TEMPDB

GO

--임시 테이블 생성

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'TARGETTABLE')

DROP TABLE TARGETTABLE

GO

CREATE TABLE TARGETTABLE

(

SEQ INT NOT NULL,

VALUE1 VARCHAR(20) NULL DEFAULT('AAAAA'),

CONSTRAINT PK_TARGETTABLE PRIMARY KEY (SEQ)

)

GO

--임시 데이터 적재(1 ~ 1000까지 데이터 입력)

DECLARE @I AS INT

SET @I = 1

WHILE (@I<=1000)

BEGIN

INSERT TARGETTABLE(SEQ) VALUES (@I)

SET @I = @I + 1

END

GO

--10개의 임의 데이터 삭제

DELETE FROM TARGETTABLE

WHERE SEQ IN (118,141,224,252,269,332,340,342,349,359)

GO

엑셀 파일(InputExcel.xls)에서 데이터를 읽어와서 TEMPDB의 TARGETTABLE 중, Seq값이 없는 건에 대해서만 추가 입력하도록 하는 예입니다.

결론부터 먼저 말씀 드리자면, 29개의 엑셀 데이터 중, 10개만 신규 데이터입니다. 이 건들만 TARGETTABLE에 들어가게 됩니다.

1. SSIS 패키지에서 데이터 흐름 작업을 추가한 후, Excel 원본을 선택하고 위의 예제 Excel 파일을 지정합니다.

2. 연결 관리자에서 OLE DB 연결을 추가한 후, 대상 DB(본 예제에서는 tempdb)를 가리키는 연결을 만듭니다.

3. 데이터 변환을 추가한 후, Excel 원본과 연결하고 Seq열을 배정밀도 부동 소수점 수[DT_R8]에서 부호 없는 4바이트 정수[DT_I4]로 변경하는 새로운 열 Seq1을 만듭니다. 또한 Value1을 선택한 후, 문자열[DT_STR] 20자리로 변경하는 새로운 열 Value2를 만듭니다. 이런 단계를 거치는 것은 엑셀 데이터가 숫자는 Real 형, 문자는 NVARCHAR(255)형으로만 들어오기 때문에 테이블에 맞게끔 변경하는 역할을 합니다.

3. 조회 변환을 추가한 후, 데이터 변환과 연결을 맺고, 다음과 같은 조회용 쿼리를 입력합니다.

SELECT Seq FROM TargetTable

4. 열 탭에서 보면 자동으로 입력 Seq열과 조회 Seq열이 매핑되어 있습니다. 이 매핑을 제거하고 Seq1과 조회 열의 Seq를 매핑 시킵니다.

5. 이제 OLE DB 대상을 추가한 후, 조회 변환의 적색 선과 연결시킵니다. 즉, 매핑이 안되어서 오류가 발생한 데이터를 OLE DB 대상이 가리킬 TargetTable로 저장하는 것입니다. 여기서, 매핑이 안되었다라는 것은 TargetTable의 Seq열과 매핑이 안되었다라는 것이며, 이 데이터들이 신규 데이터인 것입니다.

적색 선을 연결시키면 다음과 같이 오류 출력 구성 창이 나타납니다. 오류 부분에서 구성 요소 실패로 되어 있는 부분을 행 리디렉션으로 변경합니다.

6. OLE DB 대상에서 대상 테이블을 TargetTable로 지정하고, 매핑 탭에서 자동으로 연결되는 매핑선을 다 지워주고 Seq1을 SEQ로, Value2를 Value1으로 매핑시킵니다.

7. 패키지를 실행시켜서 처리되는 결과를 확인합니다.

29개의 데이터를 엑셀 파일에서 읽은 후, 테이블에 없는 10개의 데이터를 저장시켰습니다.

다시 한 번 패키지를 실행시키면 다음과 같습니다. 이미 모든 건들이 다 들어가 있기 때문에 추가되는 행이 없습니다.

첨부 패키지 및 파일을 참고하시기 바랍니다..

좋은 하루 되세요..

저작자표시

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

오늘 날짜에 추가된 파일만 복사해오기 (0)	2011.08.27
T-SQL과 JET-SQL 쿼리 비교 (0)	2011.08.27
외부에서 패키지 값 속성 설정하기 (0)	2011.08.27
파일 처리 패키지 구현 (0)	2011.08.27
SQL Agent에서 프로세스 실행 작업 패키지 수행 (0)	2011.08.27

외부에서 패키지 값 속성 설정하기

한대성

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

다음과 같은 질문이 올라와서 이에 대해 설명드리겠습니다.

생성하는 패키지에 사용자변수를 설정하고 (package 단위 변수) 값을 입력하여 실행하면 정상 처리됩니다.

하지만 dtexec 에서 변수를 /SET \Package.Variables[사용자변수명].Value;"사용자변수값" 로 변경처리하면 dtxs 가 새 변수를 받지를 못하네요..

dtexec 에서 변수값을 사용자에 따라 가변적으로 처리해야 하는데 명령문이 처리가 어떻게 되는지

답변 부탁드립니다.

말씀하신 부분 중 .Value 부분이 잘못되었습니다.

다음과 같이 지정하시면 됩니다.

/SET "\Package.Variables[사용자::사용자변수명].Properties[Value]";"밖에서 지정한 값"

참고로, 위와 같은 속성 설정 부분을 쉽게 얻을 수 있는 방법을 설명 드리겠습니다.

1. BIDS의 SSIS(S)메뉴에서 패키지 구성(C)를 눌러서 패키지 구성 도우미를 띄운 후에 임의의 구성을 설정합니다.

2. 내보낼 속성 선택 창에서 지정할 속성을 선택합니다. 예를 들어 [사용자변수명] 변수의 값(Value)을 지정한다고 하겠습니다.

3. 다음을 눌러 나오는 창을 보면 해당 속성 정보를 확인할 수 있습니다.

이것을 복사해서 이용하시면 됩니다.

구성을 사용할 필요가 없다면 취소 버튼을 클릭하시고, 패키지 구성 도우미 창에서패키지 구성 설정 체크를 빼 주시면 됩니다.

4. SQL Agent의 SSIS 패키지 설정 부분 또는 패키지 실행 유틸리티에서 값 설정 탭에서 이 속성을 입력하시고 설정 값을 입력하면 됩니다.

5. 만약 이 설정을 명령줄로 지정하고자 한다면, 패키지 실행 유틸리티의 명령줄 탭을 참고하시면 됩니다.

저작자표시

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

T-SQL과 JET-SQL 쿼리 비교 (0)	2011.08.27
엑셀 파일과 테이블 데이터 동기화하기 (0)	2011.08.27
파일 처리 패키지 구현 (0)	2011.08.27
SQL Agent에서 프로세스 실행 작업 패키지 수행 (0)	2011.08.27
파일 이름 변경하기 (0)	2011.08.27

파일 처리 패키지 구현하기

한대성

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

다음과 같은 질문에 대해 조금 길게 답변 형식으로 설명 드리겠습니다.

1, USER가 자신의 PC의 특정 폴더에 특정 이름의 파일을 한 달에 한 두번 작성합니다. 일종의 매출작성표 같은거구요..

이걸 DB에서 해당 user의 IP를 읽어와서 그 해당 엑셀파일을 1주일에 한번씩 DB에 Import시켜주는 기능이 가능 한지요~

2. 다른 PC의 유저파일을 DB 서버에 주기적으로 파일을 복사해서(덮어쓰기) 그 파일을 DB에 Import시켜주는 것도 가능한지요~

위의 둘다 목적은 패키지로 만들어 일정한 주기의 JOB을 걸어 특정 엑셀 데이터를 해당 테이블에 Insert시키는게 목적입니다.

이런 구현할수 있는지...만약 구현된다면 어떻게 하는것인지..이외에 또 다른 방법들이 있는지..

우선, 결론부터 말씀 드리자면.. 충분히 SSIS 패키지로 구현 가능한 부분입니다.

물론 SSIS 패키지 외에도 단순히 SQL 쿼리만을 가지고서 만들 수도 있을 것이고, 프로그램을 짜서 만들 수도 있을 것입니다.

질문 내용에 조금 범위를 넓혀 잠깐 잡설(^^) 조금 하겠습니다.

SSIS 패키지에 관해서 가끔 질문 받는 내용 중 일부는

『이러이러한 기능 됩니까? 이러이러한 기능을 하는 패키지 만들 수 있습니까? 』

와 같은 형태가 많습니다.

항상 대답은

『네.. 100% 가능합니다.. 』

입니다. 뭘 믿고 그렇게 100%라고 할 수있냐면..

SSIS에서 기본적으로 제공되는 여러 잡다한 작업 개체들로 구현해도 안 되는 경우에는 그냥 VB.NET 코드를 짜서 스크립트 작업에 넣어버리면 되겠지요. 그것도 귀찮으면 그냥 어플리케이션 형태로 만들어서 패키지에서 프로세스 실행 작업을 이용해서 해당 프로그램을 실행시켜 버리면 되겠지요. 어쨌든 그런 것도 SSIS 패키지라고 할 수 있기 때문에 패키지로 모두 구현 가능하다라는 당돌한(^^), 어떻게 보면 억지 형태인 대답을 드리는 것입니다.

그럼, 모냐면.. 말하고자 하는 것이.. 어떠한 작업을 할 때 어떤 방식이 가장 효율적이면서 간편하게, 우수한 성능으로, 관리 용이하게 구현할 수 있느냐가 문제입니다.

따라서 정확한 질문은

『이러이러한 작업을 SSIS로 구현했을 때 효과적일까요? 』

라는 것입니다.

(질문자님의 질문 방식에 대해 잘못되었다라고 말씀드릴 의도로 작성하는 것이 절대(!!) 아님을 이해 부탁 드립니다..^^ 설명을 쓰다 보니 뉘앙스가 꼭 그렇게 쓰여지는 것 같아서 죄송합니다^^)

자. 그럼 이제 질문에 대해 SSIS 패키지로 구현할 때의 방법과 제약 사항 등에 대해 한 번 살펴보겠습니다.

(1) User가 자신의 PC의 특정 폴더에 특정 이름의 파일을 한 달에 한두 번 작성합니다.

- 한 번도 아니고, 한두 번.. 흑~

우선, User가 한 달 내에 특정 일, 특정 시까지 파일을 만든다는 보장은 못할 것 같습니다. 따라서 패키지는 매일 또는 1주일에 한 번 또는 불특정 주기로 User의 PC의 특정 폴더에 해당 파일이 있는지, 정확히 말해서 새로운 파일이 생겼는지를 확인하는 반복 작업을 수행해야 할 것 같습니다.

여기서 또한 조금 복잡해집니다. 새로운 파일이 생겼는지를 확인하기 위해서는 파일 목록을 저장하는 메타 테이블이 별도로 있어야 할 것 같습니다.

User의 특정 폴더의 파일 목록 리스트를 읽어와서 테이블 목록 메타 테이블과 비교해서 새로운 파일 명 정보를 메타 테이블에 저장해야 할 듯 합니다.

이와 관련된 비슷한 예제는 최근에 OLAPForum 세미나에서 발표했었던 예제 패키지의 앞 부분에 있습니다. 참고하시기 바랍니다.

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

여기서 사용자의 엑셀 파일은 항상 동일한 형태이어야 한다는 제약이 있어야 할 것 같습니다. SSIS 패키지는 입력 및 출력, 변환에 대한 열 정보를 동적으로 설정할 수는 없습니다. 어떤 경우에는 엑셀 파일에서 두 열만 읽어오고(두 행이 아니고 두 열), 어떤 경우에는 세 열을 읽어오는 등과 같은 형태는 어렵다는 것이지요. (억지로 하라면..쩝..또 해야겠지만..)

이렇게 하다 보면 책 한 권 나올 수도 있을 것 같기에, 항상 동일한 형태로 작성한다는 제약 규칙이 있다고 생각합시다.^^

(2) 이걸 DB에서 해당 user의 IP를 읽어와서..

- 이 부분은 (1) 작업 전에 미리 수행되어야 할 부분입니다. SQL 실행 작업을 이용하여 user의 IP정보를 읽어와서 SSIS 변수에 저장토록 하는 것이 가장 간단한 방법일 것 같습니다.

(3) 그 해당 엑셀파일을 1주일에 한번씩 DB에 Import시켜줍니다.

- 파일이 있는 경우에 Import 시키는 작업을 수행하기 때문에, (1)번 작업인 신규 파일 리스트 추가 작업을 하고, 파일 목록을 읽어오는 SQL 실행 작업을 수행하고 그 결과를 변수에 저장한 후, 변수에 값이 있을 때에만 Import 하도록 하는 작업을 구현하도록 설정해야 합니다. 이는 간단히 선행 제약 조건에서 조건 식을 이용하여 구현하면 될 것 같습니다.

(2)번 작업 외에는 모두 위에서 말한 예제와 비슷한 형태인 것 같습니다. 예제 패키지를 참고해 보시기 바랍니다.

Import는 데이터 흐름 작업을 이용하면 되겠지요..^^

(4) 다른 PC의 유저파일을 DB 서버에 주기적으로 파일을 복사해서(덮어쓰기) 그 파일을 DB에 Import시켜주는 것도 가능한지요.

- 파일 목록을 읽어오는 부분까지는 동일할 것 같습니다. 이 후에 DB 테이블에 직접 Import할 것인지, 파일을 복사(덮어쓰기) 하도록 한 후에 해당 파일을 Import하도록 하는 형태인지의 차이 이겠지요.

간단히 플로우를 그리면 다음과 같을 듯 합니다.

(절대 이게 정답은 아닙니다..^^ 하나의 방법일 수 있다라는 것을 꼭 알아주시길..)

저작자표시

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

엑셀 파일과 테이블 데이터 동기화하기 (0)	2011.08.27
외부에서 패키지 값 속성 설정하기 (0)	2011.08.27
SQL Agent에서 프로세스 실행 작업 패키지 수행 (0)	2011.08.27
파일 이름 변경하기 (0)	2011.08.27
OnError 이벤트 처리기를 이용하여 에러 정보 남기기 (0)	2011.08.27

20070206_프로세스실행작업WorkingDirectory.zip

SQL Agent에서 프로세스 실행하기 작업 패키지 수행

한대성

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

다음과 같은 질문이 있어서 관련된 사항에 대한 내용에 대해 소개합니다.

Question : SQL Server Business Intelligence Development Studio 상에서 패키지 내에 프로세스 실행 작업을 수행하는데, SQL Server Business Intelligence Development Studio에서 직접 패키지 실행으로 작업을 수행하면 bat 파일이 수행되는 것이 확인 가능한데, 해당 패키지를 agent 작업으로 등록해서 수행하면 나머지 작업은 정상적인데, 프로세스 실행 작업 부분만 수행이 안됩니다.

뭔가 추가적으로 설정해 줘야 되는 값이 있는 건가요?

전에 한 번 이것 때문에 애를 먹은 적이 있어서 한 번 명확히 하기 위해 테스트를 수행하였습니다.

다음과 같은 실행 파일을 만듭니다.

D:\test.bat

copy /Y source.txt target.txt

test.bat라는 파일을 실행시키면, source.txt라는 파일을 target.txt라는 이름의 파일로 복사를 수행하게 됩니다.

/Y 옵션은 기존 파일이 있는 경우에 덮어 쓸 것인지를 묻지 않도록 하는 옵션입니다.

자, test.bat 파일을 실행시키면~획 하고 수행되면서 다음과 같이 target.txt 파일이 생성됩니다.

자, 본론으로 들어가겠습니다.

SSIS에서 빈 패키지에 프로세스 실행 작업 개체를 추가한 후, d:\test.bat 파일을 실행하도록 설정합니다.

정확한 확인을 위해 target.txt라는 파일을 지운 후, 패키지를 수행해 보면 정상적으로 수행되는 것을 확인할 수 있습니다.

또한, DTEXECUI.exe 또는 DTEXEC.exe를 이용한 명령어 파일로 실행을 해도 정상적으로 수행됩니다.

그런데, 그런데, 그런데… 문제는 SQL Sever 에이전트에 작업으로 등록하면, 에러가 발생합니다. 이것 참…

다음과 같은 에러가 발생합니다.

패키지에서 에러 정보를 남도록 한 후에 살펴보면 다음과 같이 애매모호한 메시지만 보여집니다.

PackageStart,DSHANNOTE,NT AUTHORITY\SYSTEM,Package,{103D9AE4-9704-400C-97D2-710E06CD6538},{5DA33BA8-8FC2-43B4-8290-8708331A2E77},2007-02-06 오후 11:19:20,2007-02-06 오후 11:19:20,0,0x,패키지 실행의 시작입니다.

OnError,DSHANNOTE,NT AUTHORITY\SYSTEM,프로세스 실행 작업,{62740483-F48F-4795-945A-0E654E523359},{5DA33BA8-8FC2-43B4-8290-8708331A2E77},2007-02-06 오후 11:19:20,2007-02-06 오후 11:19:20,-1073573551,0x,""에서 "D:\test.bat" ""을(를) 실행하면서 필요한 프로세스 종료 코드는 "0"이었으나 사용된 코드는 "1"입니다.

돌죠~ㅎㅎ 돌았었습니다..저는^^

에러 원인부터 말하자면..

『SQL Agent에서 패키지를 실행시킬 때에는 Working Directory가 지정되지 않은 경우 C:\Windows\System32에서 수행해 버립니다.』

진짜? 확인을 위해 test.bat 파일을 다음과 같이 변경합시다.

cd

copy /Y source.txt target.txt

cd는 지정 디렉터리를 여는 명령어이지만, 대상 디렉터리 없이 cd만 수행하면 현재의 디렉터리 정보를 출력합니다.

자, 그리고 SSIS의 프로세스 실행 작업에서 Argument 부분에 다음과 같이 실행 정보를 파일로 기록하는 Pipe를 지정합니다.

>>D:\execlog.txt

즉, d:\test.txt 명령어 파일이 실행된 후의 결과를 D:\execlog.txt로 저장되도록 한 것입니다.

BIDS에서 실행한 후, 로그 파일의 결과를 살펴 봅시다.

D:\에서 실행되었습니다.

SQL Agent에서 실행한 후의 결과를 확인해보면 다음과 같습니다.

C:\WINDOWS\system32\ 에서 실행이 되었습니다. (이론이론이론.. 뭔일이다요..)

정확히 말해서는 D:\test.bat라는 폴더에 있는 명령어를 C:\WINDOWS\system32\ 에서 실행을 한 것이지요.

따라서 source.txt는 system32 디렉터리에는 없는 것이기에 명령어가 제대로 수행이 안된 것입니다. 명령어가 제대로 수행이 안되었기 때문에 결과값은 성공을 의미하는 0이 아니고 1로 나타난 것이고, 따라서 위와 같은 애매 모호한 에러를 출력하였습니다.

그럼 우짜스까..

SSIS 프로세스 실행 작업 속성 창의 WorkingDirectory라는 속성에 실행 파일이 있는 위치를 지정합니다.

본 예제에서는 D:\ 이겠지요.

SQL Agent에서 실행해 봅시다.

크헉~~ㅎㅎ

뭔가 제대로 나타난 것 같네요.

정리하자면,

프로세스 실행 작업을 이용할 경우, 실행할 명령어 또는 프로그램에 맞도록WorkingDirectory 속성값을 명확히 지정해야 합니다.

입니다.

저작자표시

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

외부에서 패키지 값 속성 설정하기 (0)	2011.08.27
파일 처리 패키지 구현 (0)	2011.08.27
파일 이름 변경하기 (0)	2011.08.27
OnError 이벤트 처리기를 이용하여 에러 정보 남기기 (0)	2011.08.27
SQL Server 대상에서 Global\DTSQLIMPORT 에러 (0)	2011.08.27

20070110_파일명변경.dtsx

식을 이용하여 파일명 변경하기

한대성

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

이번 건 역시 Q&A에 올라온 내용에 대한 답변 차원에서 작성하는 내용입니다.

Question : 예를 들어 d:\test.txt라는 이름의 매일 생성되는 파일을 test_20070110.txt 와 같이 수행되는 날짜가 덧붙여진 파일 명으로 변경(or 복사)하는 작업을 구현하고자 합니다.

이와 같은 작업을 구현하는 예로 두 가지를 설명하겠습니다. 하나는 파일 시스템 작업을 이용하는 것이고, 다른 하나는 스크립트 작업을 이용하는 것입니다.

[파일 시스템을 이용하는 방법]

연결 관리자에서 두 개의 파일 연결을 정의합니다. 하나는 원본 파일(d:\test.txt)을 가리키며, 다른 하나는 대상 파일(d:\test_<년월일>.txt)를 가리키는 연결입니다.

이 때, 원본 파일은 현재 존재하기 때문에 다음과 같이 기존 파일로 정의합니다.

대상 파일 연결은 아직 파일이 없는 상태이기 때문에 다음과 같이 새 파일 생성으로 설정을 하고 파일 명은 임의로 지정합니다.

그러고선, 보기 좋게 연결 관리자의 각 연결의 이름을 변경해 줍니다. (실행에는 전혀 영향이 없으며 그냥 보기 좋~게 하기 위하여~^^)

자 그럼, 이제 도구 상자에서 파일 시스템 작업을 끌어다가 제어 흐름 영역에 추가한 후 속성 창을 엽니다.

위와 같이 파일 이름 바꾸기로 작업 유형을 지정하고, 원본 및 대상을 지정합니다.

여기까지가 SourceFile이라는 연결 정보가 가리키는 파일을 TargetFile이라는 연결 정보가 가리키는 파일로 변경해 주는 작업입니다.

자, 그럼 우리가 원하는 작업인 대상 파일명을 일자 형식으로 변경하는 것은 어디서 해 줘야 할까요.?

파일 시스템 작업?, 연결? 연결이겠지요.. 정확히는 TargetFile 이라는 대상 파일을 가리키는 연결입니다. 왜냐? 파일 시스템 작업은 단순히 파일명을 바꾸는 작업만을 하며, 입력되는 파일이 뭐고, 변경하는 파일이 뭔지에 대해서는 관여를 하지 않습니다. 그냥 주는~대로(=입력되는 대로) 수행만 하며, 주는 놈(입력을 시키는 놈)이 연결 관리자의 연결 들인 것이지요. (데이터 어댑터 라고도 하지요~^^)

자, 그럼 연결 관리자의 TargetFile을 선택한 후, 오른쪽의 속성 창을 봅시다.

ConnectionString이 D:\Test_yyyymmdd.txt 라고 되어 있네요.. 아..그럼 이 놈을 매일매일 변경되도록 하면 되겠네요.

어디서? Expressions라는 부분에서 합니다. 이 놈이 DTS에서는 동적 속성 작업과 같은 역할을 하는 놈입니다. SSIS에서는 별도의 작업으로 분리된 것이 아니라 이와 같이 모든 개체(작업, 연결, 변환 등)에 다 있는 놈입니다.

Expressions 옆의 … 버튼을 클릭하여 속성 식 편집기를 띄운 후, 원하는 속성(지정할 속성)을 선택합니다. 여기서는 ConnectionString을 지정할 것이기 때문에 ConnectionString을 선택하고 오른쪽의 식 부분에서 …을 클릭합니다.

식 작성기가 나타나며, 여기에다가 식을 지정합니다. 어떤 식으로?

여러 형태의 식이 될 수 있겠지만 저는 다음과 같이 작성했습니다. (D:\ 대신 D:\\ 인 것을 주의~!)

"D:\\Test_" + REPLACE(SUBSTRING((DT_STR, 50, 949)GETDATE(), 1, 10),"-","") + ".txt"

그러고선 식 계산(V)을 한 번 눌러보면

D:\Test_20070110.txt

이렇게 나오네요.. 오케~. 확인을 누르고 패키지를 실행시켜 봅니다.

<실행 전>

<실행 후>

[스크립트 작업을 이용하는 방법]

앞의 방법보다는 훨씬 간단합니다. VB.NET 스크립트를 이용하면..^^

하지만, 왠지 기분이 VB.NET을 쓰면 반칙인 것 같다는 느낌이.ㅎㅎㅎ

그래도 뭐.. 누가 모라겠습니까.. 간단히 & 단순히 & 쉽게 구현하는게 가장 최선이니깐..

도구상자에서 스크립트 작업을 추가한 후, 다음과 같은 스크립트를 이용합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim sourceFileName As String

Dim targetFileName As String

sourceFileName = "D:\test.txt" ' 변수에서 파일명을 받아와도 되겠지요?

targetFileName = "D:\test_" & Today.ToShortDateString.Replace("-", "") & ".txt"

If File.Exists(sourceFileName) Then '원본 파일이 있는지 확인

If File.Exists(targetFileName) Then '만약 대상 파일이 있다면?

File.Delete(targetFileName) '이미 존재하는 파일은 삭제

End If

File.Move(sourceFileName, targetFileName) ' 파일 이동이 결국은 파일명 변경

End If

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

그럼~^^

감기 조심하세요~~.

저작자표시

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

파일 처리 패키지 구현 (0)	2011.08.27
SQL Agent에서 프로세스 실행 작업 패키지 수행 (0)	2011.08.27
OnError 이벤트 처리기를 이용하여 에러 정보 남기기 (0)	2011.08.27
SQL Server 대상에서 Global\DTSQLIMPORT 에러 (0)	2011.08.27
SSIS Training을 위한 Lab 파일입니다. (0)	2011.08.27

20070104_에러정보남기기.dtsx

OnError 이벤트 처리기를 이용하여 에러 정보 남기기

한대성

Microsoft Premier Field Engineer

SSIS에서 하나의 패키지 내에 여러 개의 데이터 흐름 작업을 정의하여 실행 할 때, 여러 개 중에서 하나의 데이터 흐름 작업에서 에러가 발생하면 작업이 멈추게 되는데, 이 때 에러 이벤트를 DB에 저장하고 싶습니다.

이벤트처리기에서 ONERROR 이벤트를 어떻게 처리해야 DB에 해당 변수를 저장할 수 있는지에 대한 간단한 예제나 자료 있으시면 부탁 드리겠습니다. 기존에 올라와 있는 자료만 가지고는 잘 이해가 되지 않아서요...

이번에 A모 회사에서 SSIS 프로젝트를 하면서 생각했었던 여러 방법들이 있었습니다.

어떻게 하면 에러 로깅을 잘 & 정확히 & 효율적으로 & 간단히 할까 생각을 좀 했었는데 여러 방법들이 있을 것 같더라고요.

간단히 먼저 방법만 설명 드리면,

1. 에러가 발생했을 때의 이벤트 처리기인 OnError 이벤트 처리기에서 에러 정보를 테이블에 정보를 넣는 방법 - 위의 질문과 같은 사항이겠지요.

2. SSIS의 로깅을 이용해서 OnError 이벤트에 대해서만 정보를 저장하는 방법 – 가장 간단하긴 하지만, 작업 개체 명이라든지 변수 등과 같은 추가적인 정보를 저장시킬 수 없어서 뭔가 부족함.

3. 별도의 파일로 남기는 방법 – 이건 1번과 비슷할 수 있습니다. 테이블에 넣는 대신 파일로 떨구는(@.@) 방법으로 수행

잠시 삼천포로 빠지겠습니다. 혹시 스크립트 작업(데이터 흐름 변환 작업 내의 스크립트 변환 말고 제어 흐름 작업 내의 작업)에서 OLE DB나 ODBC, ADO.NET 등과 같은 DB 작업을 해 보신 분. 간단하게는 다음의 링크에 있는 [따라하기]처럼 하면 됩니다.

그런데… 가급적이면, SSIS에서 정의되어 있는 연결 정보를 그대로 이용하고 싶다면..

데이터 흐름의 스크립트 변환에서는 이러한 작업 형태를 쉽게 구현할 수 있지만… 제어 흐름 영역의 스크립트 작업에서는 안되더라고요~.

제가 몰라서일 수도 있지만.. 여러 날 시도해봐도, 그리고 찾아봐도 안되더라고요..

그래서 내린 결론은. 제어 흐름 영역에 있는 스크립트 작업에서는 SSIS 패키지의 연결 관리자에 정의되어 있는 연결을 이용할 수 없다. (없지 않을까? 없겠지모..) 입니다.

갑자기 OnError 이벤트 정보를 다루면서 왜 이런 소리를 할까.. 그냥 해 봤습니다..참고하시고..

처음에 구현하려고 했었던 방법은, OnError 이벤트 처리기 부분에 스크립트 작업을 하나 박아 넣고(^^), 스크립트 작업에서 에러 정보가 담긴 변수들을 좌르르 읽어 들여(DTS.Variable 를 쓰면 되겠지요~) 바로 로그 테이블에 Insert 하기.

안되더군요.. 그 이유가 바로 위에서 말한 것 때문입니다. 그렇다고 스크립트 작업 내에다가 DB 연결 문자열을 하드 코딩해서 넣기도 그렇고.

그래서 다른 무쉭한 방법을 생각했습니다. (물론 이것 때문에 또 새로운 기능을 생각할 수 있게 되어 얻은 것이 많았습니다.)

『테이블에 바로 못 넣는다면, 파일에 해당 정보를 떨어뜨리고, 그 파일을 데이터 흐름 작업을 이용해서 로그 테이블에 넣자. 』 라는 것이지요. (구현은 직접 해보세요~..)

그런데.. 이 때 주의할 것 두 가지.

1. 우선 패키지가 여러 개가 돌 수 있기 때문에 떨어뜨리는 로그 파일은 다 달라야 한다는 거.

그리고, 로그 파일이기 때문에 지저분하게 계속 발생시키면 안되겠지요.

2. 또 다른 고려 사항은, 만약 패키지가 다른 서버로 갔을 때 문제없이 돌려면?

예를 들어, A라는 서버에서 F:\SSISLog\ 라는 폴더에 로그를 남기도록 설정했는데, 이 패키지들을 테스트 서버 또는 다른 운영 서버로 옮긴다고 했을 때, 그 서버에 F:\ 드라이브가 없다면?? 에러 나겠지요~^^ 에러 안 나도록 파일 위치를 잡아야 하는 문제가 또 있겠지요.. 해법은? 여러 가지가 있을 수 있습니다..이것도 직접~

실컷 이런 식으로 구현해 보니깐 잘 돌아가긴 하다가 가끔씩 이상 에러가 발생하기도 합니다..(발생 안 하면 다행이고요~^^)

이제까지가 잡설이고요..(그래도 찬찬히 보시면 패키지 작성하실 때 참고될 부분들이 조금은 있지 않을까 합니다~^^)

갑자기..허무하게도 매우 간단한 방법이 있었습니다..

다음과 같이 에러 정보 기록용 테이블을 만듭니다. 여기서는 데모이기 때문에 아주 간단히 만들겠습니다.

본 예제에서는 tempdb 에다가 만들었습니다.

CREATE TABLE [dbo].[ErrorHistory](

[Seq] [int] IDENTITY(1,1) NOT NULL,

[PackageID] [varchar](45) COLLATE Korean_Wansung_CI_AS NOT NULL,

[PackageName] [varchar](200) COLLATE Korean_Wansung_CI_AS NULL,

[TaskID] [varchar](45) COLLATE Korean_Wansung_CI_AS NULL,

[TaskName] [varchar](200) COLLATE Korean_Wansung_CI_AS NULL,

[TaskDescription] [varchar](200) COLLATE Korean_Wansung_CI_AS NULL,

[ErrorCode] [int]NULL,

[ErrorDescription] [varchar](500) COLLATE Korean_Wansung_CI_AS NULL,

[ErrorTime] [datetime] NULL

CONSTRAINT [pk__ErrorHistory] PRIMARY KEY CLUSTERED

(

[Seq] ASC

)

GO

패키지를 만들고선, OnError 이벤트 처리기에 SQL 실행 작업을 하나 추가합니다.

SQL 실행 작업의 편집기에서 연결을 선택하고, 다음과 같은 쿼리를 SQLStatement에 입력합니다.

INSERT ErrorHistory

(PackageID, PackageName, TaskID, TaskName, ErrorCode, ErrorDescription, ErrorTime)

SELECT ?, ?, ?, ?, ?, ?, ?

그리고.. ByPassPrepare 옵션을 True로 변경합니다.(★★★★★)

매개 변수 매핑 탭에서 이제 다음과 같이 찬찬히 입력합니다.

여기서.. NVARCHAR, VARCHAR 등등.. 잘 입력하시기 바랍니다. 이 부분에 대해서는 별도로 언급하도록 하겠습니다..추후에

이제 테스트로 에러 발생해 봅시다.

에러가 발생했을 때, 이벤트 처리기 한 번 확인해 보면.. 잘 들어갔네요~^^

이와 같은 방법으로 여러 형태로 확장 해 보시기 바랍니다.

감사합니다...저도 위와 같은 방법으로 테스트를 진행해 보았었는데, 안되길래, 다른 문제가 남아있다고 생각하고 질문을 올린거였는데...역시 별표 다섯개짜리 'ByPassPrepare 옵션'을 'True로 변경'...요게 있었네요....테스트 한 번 해 보겠습니다....감사합니다....

간단하게 따라해 보았는데요...
오류: 0xC002F210(SQL 실행 작업, SQL 실행 작업: 다음 오류로 인해 쿼리 "insert into OnError(errDesc) select ?"을(를) 실행하지 못했습니다: "문이 종료되었습니다.". 가능한 실패 원인: 쿼리에 문제가 있거나 "ResultSet" 속성, 매개 변수 또는 연결을 올바르게 설정하지 않았을 수 있습니다.
이런 오류가 나오네요...
매개변수 매핑은 System::ErrorDescription 방향은 input 타입은 nvarchar 매개변수이름은 0 이렇게 했구요.
그런데 실행쿼리문에서 insert into ..... select ? 라고 했는데... 여기서 이 ?(물음표)가 매개변수 0 이라는것은 어떻게 알 수 있는거죠?
매개변수 0 이라고 하는것과 select 에서 사용한 ? 가 서로 동일한것을 가리키는것은 확실한가요?

자답이네요...
위의경우 문제는 컬럼에 varchar(64)로 정한게 문제였습니다. 데이터가 잘려서 문제가 된거였구요.. 255정도로 늘리니깐 이상없군요
그리고 매개변수매핑시 변수이름은 ?,?,? ... 물음표 순서대로 0,1,2 ... 주면 되는것 같네요.
하여튼 강의 잘 보고 있습니다. 올려주셔서 감사~

우선, 서비스팩은 설치하셨는지요..SP1 이상이 설치되어 있어야 매개변수를 사용하는 쿼리가 제대로 작동합니다

그리고, 매개 변수를 지정하는 방법은 Provider에 따라 다른데, 위의 예제와 같이 OLE DB Provider인 경우에는 차례대로 앞에서부터 0, 1, 2,.. 순으로 설정됩니다

개개변수매핑에서 방향을 INPUT으로 하잖아요. 반대로 OUTPUT은 어떻게 사용하나요?
즉, 쿼리에서 SET @VAR1 = (SELECT MAX(COL1) FROM TAB1) 이렇게 구한 @VAR1을
SSIS 사용자변수로 할당하는 방법이요....

결과 집합을 이용하셔서 설정하셔면 됩니다.
http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=342
글을 참고하시기 바랍니다.

저작자표시

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

SQL Agent에서 프로세스 실행 작업 패키지 수행 (0)	2011.08.27
파일 이름 변경하기 (0)	2011.08.27
SQL Server 대상에서 Global\DTSQLIMPORT 에러 (0)	2011.08.27
SSIS Training을 위한 Lab 파일입니다. (0)	2011.08.27
Integration Services 성능 튜닝 기술 (0)	2011.08.27

SQL Server 대상에서 Global\DTSQLIMPORT 에러

한대성

MS SQL Server MVP

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

“SSIS 파일 매핑 개체 'Global\DTSQLIMPORT’을(를) 열 수 없으므로 대량 로드할 수 없습니다.”

데이터 흐름 작업의 데이터 흐름 대상에는 SQL Server에 최적화 된 대상 개체인 SQL Server 대상이 있습니다.

대상 개체가 SQL Server인 경우에 OLE DB 대상보다 우수한 성능을 내는 대상 개체입니다.

하지만, SQL Server 대상 서버가 패키지가 실행되는 동일한 서버(로컬 서버)인 경우에는 정상적으로 처리되지만, 다른 서버(원격 서버)인 경우에는 에러가 발생합니다.

예를 들어 다음과 같은 경우에는 정상적으로 수행됩니다.

하지만, 만약 SQL Server 대상의 서버가 다른 서버인 경우에는 다음과 같이 에러가 발생합니다.

[SQL Server 대상[43]] 오류: SSIS 오류 코드 DTS_E_OLEDBERROR. OLE DB 오류가 발생했습니다. 오류 코드: 0x80040E14. OLE DB 레코드를 사용할 수 있습니다. 원본: "Microsoft SQL Native Client" Hresult: 0x80040E14 설명: "SSIS 파일 매핑 개체 'Global\DTSQLIMPORT'을(를) 열 수 없으므로 대량 로드할 수 없습니다. 운영 체제 오류 코드 2(지정된 파일을 찾을 수 없습니다.)입니다. Windows 보안을 통해 로컬 서버에 액세스하고 있는지 확인하십시오.".

이에 대해 온라인 도움말에는 다음과 같이 나와 있습니다.

해결 방법(or 처리 방법)

온라인 도움말에 언급되어 있는 것과 같이 “전역 개체 만들기” 권한을 부여하더라도 여전히 에러가 발생합니다.

정확한 원인에 대해서는 나와있지 않지만, SQL Server 대상을 이용할 경우 다음과 같은 형태의 쿼리가 실행됩니다.

BULK INSERT [dbo].[Destination]

FROM 'Global\DTSQLIMPORT 0000000000000c1c000000000096c5a8'

WITH (DATAFILETYPE = 'DTS_Buffers', CODEPAGE = 'RAW', CHECK_CONSTRAINTS, TABLOCK)

메모리에 Global\DTSQLIMPORT 라는 개체를 만들어서 이를 이용하는 방식인 듯 하지만 이에 대한 정확한 설명이나 자료를 못 찾겠네요.

현재까지 이리저리 찾고 테스트 한 후의 결론은

『SQL Server 대상 개체는 로컬 서버, 즉 패키지를 실행하는 서버와 동일한 데이터베이스 서버인 경우에만 사용하며 원격 서버일 경우에는 OLE DB 대상을 이용하시기 바랍니다. 』

※ 본 의견은 저의 개인적인 의견일 뿐이며 마이크로소프트의 공식적인 사항은 아닙니다. 혹시나 이를 해결하신 경험이 있거나 보충 설명 사항이 있으신 분들은 언제든지 댓글이나 메일, 게시글로 알려주시기 바랍니다.

좋은 글 감사합니다.
BOL을 찾아보니 아래와 같은 문구가...

SQL Server 대상은 로컬 SQL Server 데이터베이스에 연결하고 SQL Server 테이블 및 뷰로 데이터를 대량 로드합니다. 원격 서버의 SQL Server 데이터베이스에 액세스하는 패키지에서는 SQL Server 대상을 사용할 수 없습니다. 이러한 패키지에서는 대신 OLE DB 대상을 사용해야 합니다.

좋은 하루 되세요.!!

저작자표시

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

파일 이름 변경하기 (0)	2011.08.27
OnError 이벤트 처리기를 이용하여 에러 정보 남기기 (0)	2011.08.27
SSIS Training을 위한 Lab 파일입니다. (0)	2011.08.27
Integration Services 성능 튜닝 기술 (0)	2011.08.27
SQL Server 2000 DTS 패키지 예약 작업에서의 패키지 이름 찾는 방법 (0)	2011.08.27

SSIS_Training_Lab_Download.EXE

구성:

1. Microsoft Word lab Document

2. sample files

3. sample DB

저작자표시

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

OnError 이벤트 처리기를 이용하여 에러 정보 남기기 (0)	2011.08.27
SQL Server 대상에서 Global\DTSQLIMPORT 에러 (0)	2011.08.27
Integration Services 성능 튜닝 기술 (0)	2011.08.27
SQL Server 2000 DTS 패키지 예약 작업에서의 패키지 이름 찾는 방법 (0)	2011.08.27
DTS-스크립트를 이용한 데이터 변환 작업 (0)	2011.08.27

> Link : http://www.microsoft.com/korea/technet/prodtechnol/sql/2005/ssisperf.mspx

SSISPerfTuning.doc

SSISPerfTuning_Eng.doc

Integration Service 패키지 튜닝 & 모델 설정과 관련된 기술자료가 Technet에 등록되었네요.

원본은 2005년 11월에 작성되었던 것이지만, 2007년 1월 16일자로 한글 번역으로 나왔습니다.~

참고하시길 바랍니다.

한글 자료 : http://www.microsoft.com/korea/technet/prodtechnol/sql/2005/ssisperf.mspx

영문 자료 : http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

알려주신 박노철님께 감솨~~

저작자표시

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

SQL Server 대상에서 Global\DTSQLIMPORT 에러 (0)	2011.08.27
SSIS Training을 위한 Lab 파일입니다. (0)	2011.08.27
SQL Server 2000 DTS 패키지 예약 작업에서의 패키지 이름 찾는 방법 (0)	2011.08.27
DTS-스크립트를 이용한 데이터 변환 작업 (0)	2011.08.27
SQL 실행 작업(or Data Pump)에서 서브쿼리 사용하기 (0)	2011.08.27

SQL Server 2000 DTS 패키지 예약 작업에서의 패키지 이름 찾는 방법

Microsoft Premier Field Engineer
한대성

SQL Server 2000에서 DTS 패키지를 예약 설정하면 다음과 같이 dtsrun.exe 뒤에 패키지 이름이 암호화 되어 추가된다.

패키지를 마이그레이션 하거나 옮겨야 할 때 이 작업에 연결된 이름을 찾는 방법

1) SQL Agent 내에 정의된 이름을 복사한다.

2) 명령창(cmd)에 붙인 후, 마지막에 /!X /!C를 추가해서 실행한다.
/!X는 실제로 실행하지 말라는 설정이며, /!C는 실행 문을 클립보드로 복사하라는 옵션이다.

3) 메모장을 열고 붙여넣기(CTRL+V)를 하면 다음과 같이 패키지 명이 나타난다.

저작자표시

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

SSIS Training을 위한 Lab 파일입니다. (0)	2011.08.27
Integration Services 성능 튜닝 기술 (0)	2011.08.27
DTS-스크립트를 이용한 데이터 변환 작업 (0)	2011.08.27
SQL 실행 작업(or Data Pump)에서 서브쿼리 사용하기 (0)	2011.08.27
Updating SQL Server Connection Properties from the DTS Package INI File (0)	2011.08.27

[DTS]스크립트를 이용한 데이터 변환 작업

한대성

MS SQL Server MVP

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

SQL 2000 DTS의 데이터 변환 작업에서 스크립트를 이용한 처리 작업에 대해 간단히 설명하겠습니다.

SQL 2000의 데이터 가져오기/내보내기 마법사를 이용해서 테이블 간, 다른 이기종 DBMS간 데이터를 옮기는 작업에서 자주 사용하는 작업 형태가 데이터 변환 작업입니다.

다음과 같은 원본 테이블과 대상 테이블을 예로 설명하겠습니다.

--원본 테이블

CREATE TABLE SourceTable

(

SEQ INT IDENTITY,

DateCol char(20) NULL

)

GO

INSERT SourceTable VALUES ('2007-05-14')

INSERT SourceTable VALUES ('2007-05-15')

INSERT SourceTable VALUES (NULL)

INSERT SourceTable VALUES ('2007-05-17')

INSERT SourceTable VALUES ('2007051890')

INSERT SourceTable VALUES ('abcde')

INSERT SourceTable VALUES ('2007/05/20')

GO

--대상 테이블

CREATE TABLE TargetTable

(

SEQ INT,

DateCol datetime null

)

GO

SourceTable의 DateCol이라는 문자형 열에는 날짜 데이터들이 들어있지만, Null 또는 ‘abcde’와 같이 날짜 데이터가 아닌 값이 들어있을 수도 있습니다. 또한 날짜 형식이 ‘2007-05-14’와 같은 형식뿐만 아니라 ‘2007/05/20’과 같이 여러 형태로 되어 있는 경우도 있습니다. 이러한 데이터를 TargetTable의 DateCol이라는 날짜형 열에 입력할 것입니다.

단순히 SourceTable의 열을 TargeTable로 매핑하도록 구성한 후 패키지를 실행시키면 다음과 같은 에러가 발생합니다.

이러한 경우에 스크립트를 이용하여 간단히 처리할 수 있습니다.

위의 그림에서와 같이 변환 탭에서 우선 모두 삭제(A)를 클릭해서 자동으로 이름으로 매핑된 연결선을 제거합니다. 그런 다음, 모두 선택(S)을 클릭해서 원본 열과 대상 열이 모두 선택되도록 합니다.

중간에 있는 새로 만들기(W)를 클릭한 후, ActiveX Script를 선택합니다.

변환 옵션 창에서 속성(P)를 클릭하여 스크립트 변환 속성 편집기를 엽니다.

언어(L) 부분에서 VB Script Language로 선택되어 있는지를 확인합니다. 만약 다른 것으로 선택되어 있는 경우, VB Script Language로 변경한 후, 아래에 있는 자동 생성(A)를 클릭하면 기본적인 스크립트가 생성됩니다.

자, 이제 스크립트를 잠시 살펴봅시다.

DTSDestination("DateCol") = DTSSource("DateCol")

DTSSource는 원본이며, DTSDestination은 대상입니다. 즉, 원본 중 DateCol이라는 열의 데이터를 대상의 DateCol이라는 곳으로 넣는다는 아주 간단하고 단순한 대입식입니다.

이 식을 다음과 같이 약간 변형해 봅시다.

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

' Visual Basic Transformation Script

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

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

Function Main()

IF IsDate(DTSSource("DateCol")) = true THEN

DTSDestination("DateCol") = DTSSource("DateCol")

ELSE

DTSDestination("DateCol") = Null

END IF

DTSDestination("SEQ") = DTSSource("SEQ")

Main = DTSTransformStat_OK

End Function

죽 원본 DateCol열이 날짜 형식의 데이터이면 그대로 넣고, 그렇지 않은 경우이면(=날짜 형식의 데이터가 아니면) Null을 입력하도록 하였습니다.

이렇게 설정하고선 편집기를 닫으면 다음과 같은 모양으로 연결됩니다.

확인을 누른 후, 패키지를 실행하면 이전과는 달리 정상적으로 수행됩니다.

TargetData의 테이블도 다음과 같이 날짜 형식의 데이터인 경우에만 정상적으로 입력되었으며 날짜 형식의 데이터가 아닌 경우에는 Null이 입력되었습니다.

만약 DateCol의 데이터가 정상적인 날짜 형태의 데이터가 아닌 경우에 해당 행을 입력하지 않고 넘어가도록 하려면 다음과 같이 스크립트를 설정하면 됩니다.

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

' Visual Basic Transformation Script

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

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

Function Main()

IF IsDate(DTSSource("DateCol")) = true THEN

DTSDestination("DateCol") = DTSSource("DateCol")

ELSE

Main = DTSTransformStat_SkipRowInfo

Exit Function

END IF

DTSDestination("SEQ") = DTSSource("SEQ")

Main = DTSTransformStat_OK

End Function

이 경우의 출력 결과는 다음과 같습니다.

이와 같이 스크립트를 이용하여 다양한 변환이나 연산을 수행하도록 구성할 수 있습니다.

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

저작자표시

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

Integration Services 성능 튜닝 기술 (0)	2011.08.27
SQL Server 2000 DTS 패키지 예약 작업에서의 패키지 이름 찾는 방법 (0)	2011.08.27
SQL 실행 작업(or Data Pump)에서 서브쿼리 사용하기 (0)	2011.08.27
Updating SQL Server Connection Properties from the DTS Package INI File (0)	2011.08.27
AWDataWarehouseRefresh 예제 패키지 문제 (0)	2011.08.27

이번에는 DTS의 유용한 기능 하나를 소개하겠습니다. 매번 SSIS에 대해서만 글을 싣다 보니 DTS는 많이 모자란 놈으로만 소개되는데, DTS도 아주 많은 장점과 기능을 갖추고 있습니다.^^

이번에 소개할 내용은 SQL 실행 작업 또는 데이터 변환 작업(펌프 작업)에서 서브 쿼리 형태 이용하기 입니다.

숨겨진 기능은 아니고, 꽁수 입니다. 이미 아시는 분들도 많겠지만..^^

우선 예제 테이블부터 만들겠습니다.

단순히

select * into pubs..authors_test from pubs..authors

쿼리를 이용해서 테스트용 테이블 하나 만듭니다.

DTS 패키지에서 pubs DB를 지정하는 연결을 하나 추가하고, 다음과 같은 전역 변수를 지정합니다.

이제 SQL 실행 작업을 추가하고는 다음과 같은 단순 쿼리를 입력합니다.

UPDATE pubs..authors_test

SET phone = '111 111-1111'

WHERE state = ?

매개변수(A)를 눌러 다음과 같이 매개변수를 지정한 후, 실행합니다. 이것은 정상적인 단순 쿼리 형태이니까 잘 실행될 것입니다.

자, 그러면 이제 쿼리를 조금 바꿔 봅시다.

UPDATE pubs..authors_test

SET phone = '222 222-2222'

WHERE au_id in (

SELECT au_id

FROM pubs..authors_test

WHERE state = ?)

그런 후, 매개변수를 지정하려고 버튼을 클릭하면???

단지 쿼리 하나 바꿨을 뿐인데..ㅠ.ㅠ

이 외에도 조금만 복잡한 쿼리가 들어가도 위와 같은 형태의 배째라 식 메시지를 뿌리게 됩니다.

이럴 때 어떻게 할 것이냐.. 이것이 이번 글 주제입니다.

해당 쿼리를 저장 프로시저로 만들어서 사용할 수도 있고, ActiveX 스크립트 작업을 이용해서 실행되는 쿼리를 만들어주는 형태도 가능하겠지요.(복잡하지만~^^)

전 주로 후자를 많이 썼습니다만, 다음과 같은 방법도 있습니다.

우선 쿼리를 단순하게 입력합니다. 예를 들어

SELECT * FROM pubs..authors_test WHERE state = ?

와 같이, 실제 대입할 쿼리와 매개변수 형태는 동일하지만 단순한 쿼리를 입력합니다. 그런 후, 매개변수를 지정해 줍니다.

그러고선, SQL 실행 작업 속성 창을 닫기 전에, 쿼리 부분을 원래의 쿼리(서브 쿼리 또는 복잡한 형태의 쿼리)를 다시 넣고선 확인을 눌러 창을 닫습니다.

자..이제 실행하면?

실행 됩니다. 결과는?

잘 수행되었네요.^^.

즉, 매개변수를 지정할 때에만 서브 쿼리가 문제인 것이지 실제 수행할 때에는 별 문제없이 됩니다.

동일한 방법으로 SQL 실행 작업 외에도 데이터 변환 작업(Data Pump 작업)의 원본 쿼리로도 이용 가능하니깐, 한 번 해 보시기 바랍니다.

SSIS에서는?? 이와 같은 꽁수 안 통합니다. 왜?? 실행 전에 모든 작업에 대해 제차 유효성 검사를 하기 때문이지요.

그럼..이만..

댓글 좀 달아줘요~~ㅠ.ㅠ

저작자표시

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

SQL Server 2000 DTS 패키지 예약 작업에서의 패키지 이름 찾는 방법 (0)	2011.08.27
DTS-스크립트를 이용한 데이터 변환 작업 (0)	2011.08.27
Updating SQL Server Connection Properties from the DTS Package INI File (0)	2011.08.27
AWDataWarehouseRefresh 예제 패키지 문제 (0)	2011.08.27
SSIS에서의 ActiveX 스크립트 작업 처리 오류 (0)	2011.08.27

DTS 에서 동적속성작업의 설정을 통해서 config.ini 만 변경하면 한번에 connection, source file path, server name, table name, userid 등등 설정된 값을 바꿀수 있는 방법입니다...

아래 Step별로 쉽게 따라 하실수 있습니다....

한꺼번에 변경작업이 있을때 하나하나 일일이 변경하지 않고 손쉽게 바꿀수 있어서..

활용하면 좋을거 같습니다..

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

Process:

Step1: Create a table BCPTEXT in the pubs database on a server.

Create table BCPText (Id int, Name Char(20))

Step 2: Create a text file such as the one below and save it as c:\winnt\BCPText.txt. Copy this file to C:\

ID Name 1 A 2 B 3 C 4 D 5 E 6 F 7 G 8 H 9 I 10 J

Step 3: Create a transformation between the source file and the destination server

Step 4: Map columns between source and destination and test the transformation by running the DTS package.

So far, it is a normal DTS package, which transfers data from the hard coded source file path to a hard coded destination server.

Step 5: Create the INI file with the following parameters and save the file as C:\Config.ini

[Source Filepath] value=C:\BCPText.txt [Destination Server] value=WINSQL2K [Destination Database] value=Pubs [Destination Table] value=pubs.dbo.BcpText

Step 6: Add Dynamic Properties Task and name it "Read Config file"

Step 7: Process of mapping INI file parameters to the DTS package task properties.

Open 'Dynamic Properties Task' [Read config File]

Click Add, and then click on the Source connection [Text File Source] and double click on the DataSource.

Now Select the INI file that you created in C:\ drive and select the parameters as shown in the figure.

This will map the value of the Source text file path to the INI file's [Source Filepath].

Click 'Add' in the Dynamic Properties and select the database connection. Double click on the 'DataSource' and map the server name from the INI file.

In order to map the database name you have to select the 'Catalog' in the server connection. Double click on the Catalog and map the database name from the INI file.

For assigning the table name from INI file, you have to select the transformation and double click on 'Destination Object Name' property. Then map the table name from the INI file.

All these will map the parameter values specified in the INI file to the DTS package connections. Now connect the 'Dynamic Properties Task' [Read Config file] to the transformation by adding a Success Workflow.

When you run the DTS package, first the Dynamic Properties Task will assign all of the values from the INI file to the corresponding Source file path and database connection properties. Then the transformation occurs.

Conclusion:

By using the INI file and Dynamic Properties Task in the DTS package, it is extremely simple to move the DTS package from one environment to another environment. All we have to do is change the parameters in the INI file that will assign the value to all of the connections in the DTS package at run time.

출처 : http://www.databasejournal.com/features/mssql/article.php/3073161

저작자표시

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

DTS-스크립트를 이용한 데이터 변환 작업 (0)	2011.08.27
SQL 실행 작업(or Data Pump)에서 서브쿼리 사용하기 (0)	2011.08.27
AWDataWarehouseRefresh 예제 패키지 문제 (0)	2011.08.27
SSIS에서의 ActiveX 스크립트 작업 처리 오류 (0)	2011.08.27
SQL Agent에서 SSIS 패키지가 실행되지 않는 문제 (0)	2011.08.27

SQL Server 2005 예제에 포함된 Integration Services 예제 패키지 중 AWDataWarehouseRefresh패키지에 다음과 같은 문제점이 있습니다.

SQL Server 2005 예제 프로그램 설치와 관련해서는 다음글을 참고하시기 바랍니다.

AWDataWarehouseRefresh 예제 패키지는 다음 위치에 있습니다.

1) 해당 패키지 파일을 엽니다. 우선, 패키지를 실행시키기 위해서는 AdventureWorks 데이터베이스가 설치되어 있어야 합니다

만약 SQL Server 2005가 기본 인스턴스로 설치되어 있지 않다면 다음과 같이 연결 정보를 수정해 줍니다.

(제 PC에서는 ss2005라는 인스턴스로 SQL Server 2005가 설치되어 있기 때문에 다음과 같이 변경하였습니다.)

2) 파일 경로 정보는 기본으로 설치했기 때문에 특별히 변경할 필요는 없습니다. (만약 기본 위치에 예제를 설치하지 않았다면 변경하시기 바랍니다.)

3) 전체 패키지는 다음과 같습니다.

AdvWorks라는 이름의 예제 Database를 생성하고 해당 DB에 데이터를 로딩하는 작업이며, 예제 패키지들 중에서 가장 복잡한 형태입니다.

패키지의 속성에서 LocaleID가 영어(미국)으로 설정되어 있기는 하지만 이것은 별 문제는 아니겠지요..

(영문 버전을 먼저 만들었을 테이니깐..ㅡ.ㅡ)

실행을 하면 다음과 같습니다.

예제 패키지가 실패라니..쩝..

조금 크게 확대해서 보면 Bulk Insert 작업(=대량 삽입 작업)들에서만 실패하는 것을 볼 수 있습니다.

그러나, 영문 버전에서는 정상적으로 수행됩니다. (이론..)

원인은?

[대량 삽입 작업] 오류: 다음 오류 메시지와 함께 오류가 발생했습니다: "연결된 서버 "(null)"의 OLE DB 공급자 "BULK"에서 행을 인출할 수 없습니다. 연결된 서버 "(null)"의 OLE DB 공급자 "BULK"에 오류가 발생했습니다. 공급자에서 오류에 관한 정보를 주지 않았습니다.대량 로드: 데이터 파일에서 예기치 않은 파일 끝에 도달했습니다.".

무슨말이여~.. ㅡ,.ㅡ 애매합니다.

하지만, 실패의 원인은 다음과 같습니다.

ColumnDelimeter의 속성 중 Tab으로 지정된 것이 문제입니다.

즉, 한글 버전에는 SSIS의 대량 삽입 작업에서 『Tab』이라는 열 구분자(ColumnDelimeter)는 없습니다.

대신 『탭』이라는 한글 구분자를 써야 합니다.

모든 Bulk Insert 작업에서 Tab으로 지정되어 있는 열 구분자를 『탭』으로 변경해서 수행하면 정상적으로 에러가 발생되지 않고 수행됩니다.

복잡하게는 보이지만 참고할 사항들이 많은 패키지입니다. 참고하시길..^^

저작자표시

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

SQL 실행 작업(or Data Pump)에서 서브쿼리 사용하기 (0)	2011.08.27
Updating SQL Server Connection Properties from the DTS Package INI File (0)	2011.08.27
SSIS에서의 ActiveX 스크립트 작업 처리 오류 (0)	2011.08.27
SQL Agent에서 SSIS 패키지가 실행되지 않는 문제 (0)	2011.08.27
SSIS와 관련된 SS2005 SP1 변경 사항 (0)	2011.08.27

포럼에 올라온 내용인데요..

SSIS에 포함된 ActiveX 스크립트 작업에서 다음과 같은 버그가 있네요.

SSIS의 ActiveX 스크립트 작업은 기존 버전인 DTS 패키지와의 호환성을 위해 존재하는 작업으로 다음 버전에서는 없어질 것이지만, 그래도 종종 필요한 경우가 있습니다.

DTS에서는 ActiveX 스크립트 작업에서 만약 실패로 리턴값을 반환하면, 해당 작업은 실패로 처리됩니다.

SSIS에서 위의 코드를 그대로 실행한다면?

성공으로 처리됩니다.

이후 버전에서 수정되겠지요?

혹시나 SSIS 패키지에서 ActiveX 스크립트 작업을 사용할 때 주의하세요……

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

SQLLeader.com / ADConsulting / 한대성 (olaper@지메일.컴)

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

저작자표시

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

Updating SQL Server Connection Properties from the DTS Package INI File (0)	2011.08.27
AWDataWarehouseRefresh 예제 패키지 문제 (0)	2011.08.27
SQL Agent에서 SSIS 패키지가 실행되지 않는 문제 (0)	2011.08.27
SSIS와 관련된 SS2005 SP1 변경 사항 (0)	2011.08.27
0x80040155 에러... (0)	2011.08.27

SQL Agent에서 SSIS 패키지가 실행되지 않는 문제

2007.05.14 Updated Version : http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=1238

현상

로컬 PC 또는 서버에서는 패키지를 실행하면 제대로 수행이 되는데, 패키지 파일(*.dtsx)을 서버에 저장한 후 SQL Agent에 등록하여 실행하면 에러가 발생할 수 있습니다.

다음과 같이 개발 환경에서 직접 패키지를 실행하면 정상적으로 수행됩니다.

이 패키지를 SQL Agent에 다음과 같이 등록한 후 실행하면 에러가 발생합니다.

원인

SSIS 패키지의 기본 보안 수준인 ProtectionLevel은 EncryptSensitiveWithUserKey로 설정이 되어 있습니다.

이 값은 패키지의 제어 흐름 영역에서 빈 영역을 클릭한 후, 오른쪽에 있는 속성 창에서 확인할 수 있습니다.

EncryptSensitiveWithUserKey는 중요한 데이터(Sensitive Data)를 현재 패키지가 개발되는 사용자 프로필 기반의 키를 이용하여 암호화 한다는 의미입니다. 여기서 말하는 중요한 데이터란 DB의 연결 정보 또는 패스워드, XML의 노드 정보 등과 같은 값입니다. 따라서 윈도우 인증이 아닌 패스워드를 이용한 OLE DB 연결이나 XML 작업등이 포함된 경우, 중요한 데이터가 패키지에 포함된 상태이기 때문에 패키지가 암호화 됩니다.

수동으로 실행할 때에는 패키지가 암호화된 개인키와 실행 환경의 개인키가 동일하기 때문에 정상적으로 실행됩니다. 하지만, 서버로 파일을 복사한 후, SQL Agent에서 실행하거나, 현재의 개발 PC가 아닌 다른 곳에 패키지를 복사해서 수행하게 되면, 패키지가 암호화된 개인키와 실행 환경의 개인키가 다르기 때문에 오류가 발생합니다.

해결 방안

방법 1) 패키지의 ProtectionLevel을 EncryptSensitiveWithUserKey 대신, EncryptSensitiveWithPassword 또는 EncryptAllWithPassword로 설정한 후, PackagePassword 항목에 암호를 설정합니다.

이런 방식으로 저장하게 되면 패키지의 중요한 정보 또는 패키지 정보 전체가 개인키 대신 암호를 기반으로 암호화 시키게 됩니다.

이후, SQL Agent에서 이 패키지를 실행하기 위해 설정하는 단계에서는 다음과 같은 암호 입력 창이 나타납니다.

(일반 탭에서 실행할 패키지를 지정한 후, 다른 탭을 클릭할 경우 나타납니다.)

위와 같은 패키지 암호 설정 창에서 지정한 암호를 입력한 후, 패키지를 실행하면 정상적으로 수행됩니다.

방법 2) 패키지의 ProtectionLevel을 ServerStorage로 임시 변경한 후, 패키지를 실행할 SQL Server에 저장시킵니다.

ProtectionLevel을 ServerStorage로 설정하는 것은 패키지의 보안과 관련된 사항을 SQL Server에게 관리하도록 넘기는 것입니다. 이 때, 주의할 것은 이 설정으로 변경한 경우에는 패키지는 파일 형태로 저장할 수 없고 SQL Server로만 저장할 수 있습니다.

개발 화면의 상단 파일(F) 메뉴 중, 다른 이름으로 ….dtsx의 복사본 저장(C) 메뉴를 클릭합니다.

패키지 위치를 SQL Server로 지정한 후, 인증 유형을 설정합니다. Windows 인증이나 SQL Server 인증이나 상관없이 연결할 수 있는 적절한 권한(msdb의 db_dtsadmin 역할 또는 db_dtsltduser 역할)이 있는 계정으로 지정하면 됩니다. 패키지 경로(K) 부분에서는 저장할 패키지 명을 지정합니다. 옆의 ... 버튼을 눌러 폴더 형식과 같은 적절한 경로를 지정할 수도 있습니다.

단, 이 때 SQL Server에 저장시킨 후, 다시 로컬 파일로 저장할 경우 저장이 되지 않습니다. 이는 앞서 언급한 바와 같이 ProtectionLevel을 ServerStorage로 설정하였기 때문입니다. 따라서 패키지를 별도의 파일로 저장하기 위해서는 ProtectionLevel의 속성값을 ServerStorage 이외의 값으로 변경하셔야 합니다.

이후, SQL Agent에서 패키지를 실행하는 작업을 등록할 경우, 다음 그림과 같이 패키지 원본을 SQL Server로 지정한 후, 저장한 패키지를 선택하면 됩니다.

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

SQLLeader.com / ADConsulting / 한대성 (olaper@지메일.컴)

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

저작자표시

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

AWDataWarehouseRefresh 예제 패키지 문제 (0)	2011.08.27
SSIS에서의 ActiveX 스크립트 작업 처리 오류 (0)	2011.08.27
SSIS와 관련된 SS2005 SP1 변경 사항 (0)	2011.08.27
0x80040155 에러... (0)	2011.08.27
SQL Agent 또는 다른 PC에서 SSIS 패키지가 실행되지 않는 문제 (Updated) (0)	2009.06.29

SQL 2005 서비스 팩1이 발표되면서 많은 부분들이 개선되었습니다. 이들 중, SSIS와 관련된 변경 사항에 대한 것입니다.

• Usability of the Import/Export Wizard has been improved in multiple-table scenarios.

• The IDtsPipelineEnvironmentService service lets custom data flow components have programmatic access to the parent Data Flow task.

• Interoperability with Analysis Services has been improved. The DataReader source in SSIS supports the System.Object data type by converting columns that have this data type to the DT_NTEXT ssISnoversion data type. To change the data type to a type that is more appropriate for your data, you can add a Data Conversion transformation.

• Performance has been improved for many transformations, such as the Sort transformation.

• Designer usability has been improved. For example, you can now right-click Dataflow and then click Execute Task to execute only the Dataflow task. You do not have to switch to the control flow to execute only the Dataflow task.

• The Expression Builder dialog box now has a public and documented API. This increased access to expression-related objects will benefit task developers because expressions are important to the way that tasks are interrelated.

• We have added a registry-based policy for handling SSIS package signatures as a package is executed. For example, administrators can disable loading of unsigned packages and untrusted packages. Because these settings are in the registry, administrators can propagate these settings in the domain by using Microsoft Windows Group Policy policy settings.

• In the Advanced Editor dialog box, the Flat File source has the new property UseBinaryFormat. This property supports loading packed decimal data into the pipeline for processing by a script or by a custom transformation.

• In the Advanced Editor dialog box, the DataReader source has the new property CommandTimeout. You can use this property to modify the time-out period to allow for long-running operations.

• To create or to modify the property expressions of variables, you can now open the Expression Builder dialog box from the Properties window.

• You can now add annotations to precedence constraints.

아직 몇가지는 개선이 안된 점들도 있겠지만, 여러모로 조금 더 좋게 된 것 같습니다..

참고하시고, SSIS를 이용하시는 경우 꼭 SP1도 같이 설치하셔서 이용하세요~~^^

저작자표시

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

SSIS에서의 ActiveX 스크립트 작업 처리 오류 (0)	2011.08.27
SQL Agent에서 SSIS 패키지가 실행되지 않는 문제 (0)	2011.08.27
0x80040155 에러... (0)	2011.08.27
SQL Agent 또는 다른 PC에서 SSIS 패키지가 실행되지 않는 문제 (Updated) (0)	2009.06.29
SSIS가 뭡니까? - 처음 접하시는 분들을 위한 소개 (0)	2009.06.29

SSIS로 작업을 할려고 오랫만에 BIDS(SQL Server Business Intelligence Development Studio)를 열었더니, 다음과 같은 에러가 발생되었습니다..

한 며칠 SSIS를 안했더니만..이놈이 시위를 하는건지..(ㅡ,.ㅡ)

인터넷을 찾아보니, 다음과 같은 해법이 있네요....

This message has normally been seen on machines which have had beta versions of SQL Server 2005 or Visual Studio 2005 installed, and now the RTM version has been applied. When creating a new SSIS project, the project and package are saved automatically, but in some cases this error is seen instead. Since SSIS packages are now XML based, MSXML is a required component for SSIS to function, and problems with this can cause the error shown above. There may be other reasons for this error unrelated to beta installations, yet the the root cause is the loss of MSXML so resolution should always be the same.

To resolve this problem re-register the two MSXML component DLLs, msxml3.dll and msxml6.dll.

To register a DLL use the Microsoft Register Server program (regsvr32.exe). You can simply type the following commands into a command prompt session.

regsvr32 msxml3.dll

regsvr32 msxml6.dll

즉, msxml3.dll 과 msxml6.dll 을 재등록하믄 된다~~~ 라는 말씀..

혹시나 저와 같은 에러 발생하면 참고하세요~~~^^

저작자표시

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

SQL Agent에서 SSIS 패키지가 실행되지 않는 문제 (0)	2011.08.27
SSIS와 관련된 SS2005 SP1 변경 사항 (0)	2011.08.27
SQL Agent 또는 다른 PC에서 SSIS 패키지가 실행되지 않는 문제 (Updated) (0)	2009.06.29
SSIS가 뭡니까? - 처음 접하시는 분들을 위한 소개 (0)	2009.06.29
SSIS 예제 패키지 파일 이용하기 (0)	2009.06.29

SQL Agent 또는 다른 PC에서 SSIS 패키지가 실행되지 않는 문제 (Updated 2007.05.14)

한대성

MS SQL Server MVP

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

현상

로컬 PC 또는 서버에서는 패키지를 실행하면 제대로 수행이 되는데, 패키지 파일(*.dtsx)을 서버에 저장한 후 SQL Agent에 등록하여 실행하거나 패키지가 만들어진 PC나 서버가 아닌 다른 사용자의 환경에서 패키지를 실행할 경우 에러가 발생할 수 있습니다.

다음과 같이 패키지가 개발된 환경에서 직접 패키지를 실행하면 정상적으로 수행됩니다.

이 패키지를 SQL Agent에 다음과 같이 등록한 후 실행하면 에러가 발생합니다.

(Service Pack2 이전의 에러 메시지)

(Service Pack2 이후의 에러 메시지)

원인

SSIS 패키지의 기본 보안 수준인 ProtectionLevel은 EncryptSensitiveWithUserKey로 설정이 되어 있습니다.

이 값은 패키지의 제어 흐름 영역에서 빈 영역을 클릭한 후, 오른쪽에 있는 속성 창에서 확인할 수 있습니다.

EncryptSensitiveWithUserKey는 중요한 데이터(Sensitive Data)를 현재 패키지가 개발되는 사용자 프로필 기반의 키를 이용하여 암호화 한다는 의미입니다. 여기서 말하는 중요한 데이터란 DB의 연결 정보 중 패스워드, Web 서비스 연결의 패스워드와 같은 값입니다. 따라서 윈도우 인증이 아닌 패스워드를 이용한 OLE DB 연결이나 Web 서비스 작업 등이 포함된 경우, 중요한 데이터가 패키지에 포함된 상태이기 때문에 패키지가 암호화 됩니다.

수동으로 실행할 때에는 패키지가 암호화된 개인 키와 실행 환경의 개인키가 동일하기 때문에 정상적으로 실행됩니다. 하지만, 서버로 파일을 복사한 후, SQL Agent에서 실행하거나, 현재의 개발 PC가 아닌 다른 곳에 패키지를 복사해서 수행하게 되면, 패키지가 암호화된 개인키와 실행 환경의 개인키가 다르기 때문에 오류가 발생합니다.

해결 방안

[방법 1] 패키지의 ProtectionLevel을 EncryptSensitiveWithUserKey 대신, EncryptSensitiveWithPassword 또는 EncryptAllWithPassword로 설정한 후, PackagePassword 항목에 암호를 설정합니다.

이런 방식으로 저장하게 되면 패키지의 중요한 정보 또는 패키지 정보 전체가 개인키 대신 암호를 기반으로 암호화 시키게 됩니다.

이후, SQL Agent에서 이 패키지를 실행하기 위해 설정하는 단계에서는 다음과 같은 암호 입력 창이 나타납니다.

(일반 탭에서 실행할 패키지를 지정한 후, 다른 탭을 클릭할 경우 나타납니다.)

위와 같은 패키지 암호 설정 창에서 지정한 암호를 입력한 후, 패키지를 실행하면 정상적으로 수행됩니다.

[방법 2] 패키지의 ProtectionLevel을 ServerStorage로 임시 변경한 후, 패키지를 실행할 SQL Server에 저장시킵니다.

ProtectionLevel을 ServerStorage로 설정하는 것은 패키지의 보안과 관련된 사항을 SQL Server에게 관리하도록 넘기는 것입니다. 이 때, 주의할 것은 이 설정으로 변경한 경우에는 패키지는 파일 형태로 저장할 수 없고 SQL Server로만 저장할 수 있습니다.

개발 화면의 상단 파일(F) 메뉴 중, 다른 이름으로 ….dtsx의 복사본 저장(C) 메뉴를 클릭합니다.

패키지 위치를 SQL Server로 지정한 후, 인증 유형을 설정합니다. Windows 인증이나 SQL Server 인증이나 상관없이 연결할 수 있는 적절한 권한(msdb의 db_dtsadmin 역할 또는 db_dtsltduser 역할)이 있는 계정으로 지정하면 됩니다. 패키지 경로(K) 부분에서는 저장할 패키지 명을 지정합니다. 옆의 ... 버튼을 눌러 폴더 형식과 같은 적절한 경로를 지정할 수도 있습니다.

단, 이 때 SQL Server에 저장시킨 후, 다시 로컬 파일로 저장할 경우 저장이 되지 않습니다. 이는 앞서 언급한 바와 같이 ProtectionLevel을 ServerStorage로 설정하였기 때문입니다. 따라서 패키지를 별도의 파일로 저장하기 위해서는 ProtectionLevel의 속성값을 ServerStorage 이외의 값으로 변경하셔야 합니다.

이후, SQL Agent에서 패키지를 실행하는 작업을 등록할 경우, 다음 그림과 같이 패키지 원본을 SQL Server로 지정한 후, 저장한 패키지를 선택하면 됩니다.

[방법 3] SQL Agent의 작업 중, 데이터 원본 탭에서 DB 연결에 대한 Password를 지정합니다.

패키지를 실행하는 작업 단계 중, 데이터 원본 탭을 클릭하면 패키지에서 이용하는 데이터 원본이 표시됩니다. 이 때, SQL 인증을 사용하는 DB 연결을 선택한 후, 연결 문자열 부분에 Password=... 구문을 추가합니다.

예) Data Source=dshan\ss2005;User ID=sa;Initial Catalog=tempdb;Provider=SQLOLEDB.1;Auto Translate=False;Password=dbpassword

[방법 4] DB 정보를 포함하는 구성 파일을 만든 후, 이를 구성 탭에서 지정합니다.

패키지 내의 모든 개체에 대한 속성은 구성 이라는 기능으로 지정할 수 있습니다. 또한 구성 정보는 파일로 저장할 수도 있습니다.

(구성에 관한 자세한 내용은 다음 링크의 강좌를 참고하시기 바랍니다.

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

패키지에서 사용하는 DB 연결 정보를 별도의 구성 파일로 만든 후에 이 구성 파일을 지정하는 식으로 문제를 해결할 수 있습니다. 예를 들어 다음과 같은 구성 파일을 생성합니다.

예) D:\dbconnectionstring.dtsconfig 라는 이름으로 구성 파일 생성

<?xml version="1.0"?>

<ConfiguredValue>Data Source=dshan\ss2005;User ID=sa;Initial Catalog=tempdb;Provider=SQLOLEDB.1;Auto Translate=False;Password=dbpassword;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

이 구성 파일은 “.\ss2005.tempdb” 라는 연결에 대해 ConnectionString 정보를 가지고 있는 구성 파일입니다. 이 구성 파일을 SQL Agent의 작업 단계 설정 중 구성 부분에서 추가합니다.

이 방식은 프로젝트 단위의 여러 패키지가 존재하며, 각 패키지에서 같은 DB 정보를 이용할 때 일괄적으로 관리할 수 있는 장점이 있습니다. 또한, 직접 데이터 원본 부분에서 password를 지정해주는 대신 별도의 파일에 기록함으로써 DB Password의 노출을 방지할 수 있습니다. (파일에 대한 접근 권한은 SQL Agent 실행 계정 및 Administrator에게만 부여하도록 Windows 환경에서 설정하면 됩니다.)

[방법 5] 패키지의 ProtectionLevel을 DontSaveSensitive로 변경한 후, DB 연결 정보의 값은 식을 이용하여 지정하는 방식으로 저장합니다. 여기서 ProtectionLevel을 DontSaveSensitive로 설정한 것은 다른 사용자가 패키지를 열었을 때에도 보안 경고를 발생시키지 않도록 하기 위함입니다.

우선 패키지의 보안 수준을 DontSaveSensitive로 변경합니다.

연결 관리자에서 SQL 인증을 이용하는 연결을 선택한 후, 속성의 Expression 부분에서 ... 버튼을 클릭하여 해당 연결의 속성에 대한 식을 지정합니다.

속성 식 편집기에서 ConnectionString 속성을 선택하고 해당 연결에 대한 연결 문자열을 직접 지정합니다. 이 때, 연결 문자열에 Password를 포함해서 지정해야 합니다.

[참고] 직접 식으로 지정하는 대신, 연결 문자열을 포함하는 String형 변수를 지정하고, 속성 식에서 이 변수를 사용하도록 설정할 수도 있습니다.

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

강다혁

좋은 글 감사합니다.

2007/06/09

황재순

안녕하세요. 좋은자료로 많은 도움을 얻고 있습니다. 위의 방법을 다 쓰고도 문제가 풀리지 않아서 글을 남깁니다.

다른 컴퓨터에 있는 엑셀파일을 불러올때 서버에이전트가 작동하지않습니다. 수동으로 실행시키면 문제없이 다른컴에 있는 파일을 불러옵니다. 그러나 시간을 맞춰놓고 에이전트를 실행하면 실패로 끝납니다. 왜 서버에이전트가 자동으로 실행을 못시키는 것일까요?

감사합니다.

2008/03/03

한대성

파일 공유는 약간 또 다른 이슈 사항이 있습니다. 만약 제어판 => 서비스에서 SQL Agent 시작 계정을 변경할 수 있다면, 현재 로그인 하신 계정으로 변경하신 후에 실행해 보시기 바랍니다. 자세한 사항은 질문으로 올려주세요~^^(답변 드릴려고 했는데 지워졌더라구요..)

2008/03/03

황재순

한대성님 너무 감사합니다. Q&A에 질문 다시 올렸습니다. 부디 꼭 답변 바랍니다.
참고로 제 메일 주소를 남깁니다. nara21c@hotmail.com
감사합니다.

2008/03/03

김성진

좋은 글 감사드립니다~

2008/09/18

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

SSIS와 관련된 SS2005 SP1 변경 사항 (0)	2011.08.27
0x80040155 에러... (0)	2011.08.27
SSIS가 뭡니까? - 처음 접하시는 분들을 위한 소개 (0)	2009.06.29
SSIS 예제 패키지 파일 이용하기 (0)	2009.06.29
데이터 흐름 원본에서 임시 테이블 사용하기 (0)	2009.06.20

『SSIS, Integration Services, 데이터 통합 서비스.. 』

이것들이 무엇인지 모르는 분들을 위해 다음과 같이 간략한 Q&A 형태로 작성해 보았습니다. SSIS를 처음 접하시는 분, SSIS에 대해 궁금해 하시는 분들께 조금이나마 도움이 되었으면 합니다.

내용은 계속해서 추가할 예정입니다. - 에이디컨설팅 한대성-

Q) SSIS가 뭡니까?

SSIS는 SQL Server 2005 Integration Services의 약자로써, 간단하게는 데이터 가져오기/내보내기와 같은 데이터 이관 작업에서부터 이기종 데이터 통합 및 처리, 데이터베이스 관리 작업 등을 수행할 수 있는 서비스 입니다.

Q) SQL 2000의 DTS와 비슷하나요?

SSIS는 SQL 2000 DTS의 업그레이드 버전입니다. DTS(데이터 변환 서비스)가 SQL 2005에서는 SSIS(데이터 통합 서비스)로 이름이 변경되었으며, 많은 점들이 향상되었습니다.

Q) SSIS 패키지가 무엇인가요?

SQL Server의 저장 프로시저와 같이 사용자가 원하는 작업들을 정의해 놓은 실행 개체가 패키지 입니다. 하나의 패키지 내에는 테이블 생성, 데이터 전송, 메일 보내기 등과 같은 여러 작업들이 포함됩니다.

Q) SSIS를 사용하기 위해서는 SQL Server 2005가 설치되어야 하나요?

SSIS는 SQL Server 2005에 포함된 서비스이긴 하지만 SQL 엔진(SQL Server Database Services)이 반드시 설치되어야 할 필요는 없습니다. SQL Server 2005에는 SQL Server 엔진뿐만 아니라 Integration Services, Analysis Services, Reporting Services, Notification Services 등과 같은 여러 서비스들이 포함되어 있습니다.

SQL 2000 DTS는 반드시 SQL이 실행 중이어야만 사용 가능했지만, SSIS는 SQL이 실행 중일 필요는 없습니다. 따라서 단순히 패키지를 실행시키기 위해서는 Integration Services만 설치되어 있으면 됩니다. 패키지를 작성하려면 다음과 같이 고급(D)를 누르면 나타나는 기능 선택 창에서 Business Intelligence Development Studio가 설치되어야 합니다.

Q) SQL Server Database Services가 반드시 실행 중이어야 하나요?

SQL Server 2005에서는 Integration Services와 Database Services가 분리되었습니다. 즉, Database Services가 실행 중이지 않더라도 패키지를 실행하거나 관리할 수 있습니다.

Q) SSIS 패키지는 무엇으로 만드나요?

SQL 2000 DTS 패키지는 엔터프라이즈 관리자(Enterprise Manager)에서 만들었습니다. 즉, EM에서 패키지를 만들고 관리까지 모두 수행하였습니다. 하지만, SQL 2005 SSIS에서는 패키지를 만들기 위해서는 Visual Studio를 이용해야 합니다. 이러한 점은 SSIS 패키지뿐만 아니라 Analysis Services의 Cube 및 Dimension, Reporting Services의 보고서를 만들 때도 마찬가지입니다.

Q) 그러면 SQL Server 2005 외에도 추가로 Visual Studio를 사야 하나요?

별도로 구매할 필요는 없습니다. SQL Server 2005를 설치하는 과정에서 클라이언트 구성 요소 중 Business Intelligence Development Studio를 선택하게 되면 자동으로 Visual Studio가 설치됩니다. 이 때 설치되는 Visual Studio는 SQL 2005의 Integration Services 패키지, Analysis Services의 큐브 및 디멘젼, Reporting Services의 보고서를 만들 수 있는 기능만 포함되어 있습니다.

Q) 패키지를 만들기 위해서는 SQL 쿼리 외에 추가로 알아야 할 언어가 있습니까?

별도로 알아야 할 언어는 없습니다. 또한 SQL과 관련된 작업을 수행하지 않는다면 SQL 쿼리를 몰라도 됩니다. 기본적으로 제공되는 다양한 형태의 작업 개체 중 원하는 작업을 선택해서 끌어 놓은 후, 필요한 설정만 해주면 됩니다.

VB.NET을 잘 안다면 스크립트 작업을 이용해서 다양한 작업을 추가로 구현할 수 있기 때문에 편하긴 하지만 반드시 알아야 할 필요는 없습니다.

Q) MSSQL 데이터만 사용할 수 있나요?

MSSQL 뿐만 아니라 Oracle, Sybase, DB2 등과 같은 다양한 형태의 데이터를 직접 이용할 수도 있으며 텍스트 파일, XML 파일, 엑셀 파일 등과 같은 형태의 데이터도 모두 사용할 수 있으며, 이러한 형태로 저장도 가능합니다.

Q) SSIS 패키지는 어디에 저장됩니까?

SSIS 패키지는 기본적으로 파일 형태로 존재하며, 확장자는 dtsx 입니다.

이 파일은 XML 형태로 되어 있습니다. 사용자가 원하는 위치에 패키지 파일이 저장됩니다. 이렇게 만든 패키지 파일을 서버로 복사해서 수행하거나, 또는 SQL Server의 테이블로 바로 저장시킬 수 있습니다. (SQL Server 2005의 msdb.dbo.sysdtspackages90 테이블에 저장)

Q) SSIS 패키지는 어떻게 실행시키나요?

우선, 개발 툴인 Visual Studio에서 바로 실행시킬 수 있습니다. 단, 이 때에는 Integration Services가 실행 중일 필요는 없습니다.

또한 패키지 파일(.dtsx)를 더블 클릭하면 패키지를 실행시킬 수 있는 전용 유틸리티가 실행됩니다. (DTEXECUI.exe) 이 프로그램에서 연결 정보나 옵션 등을 설정하여 수행할 수 있습니다.

이 외에도 콘솔 모드(도스 창)에서 실행시킬 수 있는 dtexec.exe라는 유틸리티를 이용해서 패키지를 수행할 수 있습니다. 패키지를 예약해서 수행하기 위해서는 SQL Server 2005 에이전트에서 작업으로 등록해서 수행하게 됩니다. 작업으로 등록할 때 유형에서 SQL Server Integration Services 패키지를 선택한 후 실행시킬 패키지를 지정하면 됩니다. 만약 SQL Server 2005 Agent를 사용하지 않는다면, 윈도우 예약 작업에서 다음과 같은 형태의 명령으로 실행시킬 수도 있습니다.

예) dtexec /FILE "D:\SSIS\Sample.dtsx" /CHECKPOINTING OFF /REPORTING EWCDI

본 글에서 소개되는 내용은 작성자의 개인적인 의견이며, Microsoft사에서 제공하는 공식적인 사항은 아닙니다.

최종 수정일 : 2007.02.10

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

0x80040155 에러... (0)	2011.08.27
SQL Agent 또는 다른 PC에서 SSIS 패키지가 실행되지 않는 문제 (Updated) (0)	2009.06.29
SSIS 예제 패키지 파일 이용하기 (0)	2009.06.29
데이터 흐름 원본에서 임시 테이블 사용하기 (0)	2009.06.20
Checkpoint를 이용한 패키지 반복 실행 구성 (0)	2009.06.20

예제 패키지 파일 이용하기

안녕하세요. 한대성입니다.

대부분 다 잘 아시겠지만 익숙하지 않은 분들이 있을 것 같아 SSIS 관련 자료 중에 첨부한 예제 파일 사용법을 간략히 설명 드리겠습니다.

위의 예제를 이용하여 설명하겠습니다.

우선 예제 파일을 임의의 폴더에 다운 받습니다. (여기서는 C:\SSISPackageDown\ 이용)

다운 받은 dtsx 패키지 파일을 더블 클릭하면 자동으로 패키지 실행 유틸리티(DTEXECUI.exe)가 실행됩니다.

이 프로그램은 패키지를 실행하기 위한 것이며, 패키지를 수정하거나 내용을 볼 수 있는 기능은 아닙니다.

1. 편집 기능으로 열기

dtsx 패키지 파일을 더블 클릭하는 대신 마우스 오른쪽 버튼을 클릭하면 다음 그림과 같이 편집(E) 메뉴가 나타납니다.

이를 선택하면 해당 dtsx 패키지 파일을 Visual Studio의 프로젝트에 포함시키지 않고 바로 조회하거나 수정할 수 있습니다.

◦ 단순히 패키지의 내용을 조회할 때 이용합니다.

◦ 디버깅 단계 없이 패키지를 수정할 때 이용합니다.
= 패키지를 수정하고 저장할 수는 있지만, 개발 환경에서 실행시킬 수는 없습니다.

2. 프로젝트에 포함하여 열기

[시작]à[프로그램]à[SQL Server 2005]à[SQL Server Business Intelligence Development Studio]를 선택한 후, Integration Services 패키지를 선택하여 빈 패키지를 생성하거나 기존의 Integration Services 패키지를 엽니다.

솔루션 탐색기의 SSIS 패키지 부분을 선택한 후, 마우스 오른쪽 버튼을 클릭하여 나타나는 메뉴 중 기존 패키지 추가(X)를 선택한 후,

기존 패키지의 복사본 추가 창에서 패키지 위치(L)을 파일 시스템으로 변경하고 아래의 패키지 경로(K)에서 추가할 패키지를 지정하여 패키지를 추가합니다.

추가한 패키지를 더블 클릭하여 엽니다. 이 때, 패키지의 연결 정보가 잘못된 경우에는 유효성 검사 단계 진행 후 다음과 같이 유효성 경고가 나타납니다. 이것은 패키지가 잘못되어서 발생되는 에러는 아니며, 패키지의 연결 정보를 현재의 환경 정보에 맞게 수정하면 해결됩니다.

패키지의 연결 관리자에 지정된 연결 정보들을 확인한 후 현재 환경에 맞게 수정합니다.

예) “엑셀파일” 연결에서 지정된 파일 경로를 변경

연결 정보를 변경해준 후, 그대로 패키지를 실행시켜 보거나 또는 솔루션 탐색기에서 해당 패키지를 선택한 후, 오른쪽 마우스를 클릭해서 나타나는 메뉴 중 업그레이드로 다시 로드를 선택하면 현재 설정된 정보로 다시 로딩하며 패키지의 유효성 경고는 사라집니다.

◦ 기존 SSIS 프로젝트에 포함시키는 방식으로 패키지를 조회 및 편집합니다.

◦ 기존 패키지 추가 메뉴를 이용하여 추가하거나 직접 패키지 파일을 드레그(Drag)하여 솔루션 탐색기의 SSIS 패키지 메뉴 부분에 드롭(Drop)하는 방식으로도 추가시킬 수 있습니다.

◦ 패키지를 수정한 후 실행도 시킬 수 있습니다.

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

SQL Agent 또는 다른 PC에서 SSIS 패키지가 실행되지 않는 문제 (Updated) (0)	2009.06.29
SSIS가 뭡니까? - 처음 접하시는 분들을 위한 소개 (0)	2009.06.29
데이터 흐름 원본에서 임시 테이블 사용하기 (0)	2009.06.20
Checkpoint를 이용한 패키지 반복 실행 구성 (0)	2009.06.20
패키지에서 네트워크 연결이 오류가 날 때 (0)	2009.06.20

데이터 흐름 원본에서 임시 테이블 사용하기

Microsoft Premier Field Engineer

한대성

좀 까다로운 주제입니다.

예를 들어, 다음과 같은 쿼리를 생각해 봅시다.

CREATE TABLE #SeqNo

(

SeqNo int

)

INSERT #SeqNo VALUES(1)

INSERT #SeqNo VALUES(2)

SELECT * FROM #SeqNo

DROP TABLE #SeqNo

/*

SeqNo

------

1

2

*/

쿼리 분석기에서 실행시키면 위와 같이 정상적으로 실행됩니다. 그런데 이러한 형태의 쿼리를 데이터 흐름 작업에서 OLE DB 원본 또는 DataReader Source 등에서 사용하려고 하면 다음과 같은 문제가 발생합니다.

이와 같은 형태의 에러가 발생하는 이유는 다음과 같습니다.

SSIS에서 데이터 흐름 작업 내의 모든 개체는 출력될 열의 정보가 패키지를 만들 때부터(=디자인 단계에서) 정해져야 합니다. 쉽게 말해 출력되는 열의 정보가 명확히 존재해야 한다는 것이지요.

그럼 위와 같은 쿼리는 뭐가 문제일까요?

SSIS 패키지에서 SQL 쿼리가 원본으로 설정된 경우에는 쿼리를 실행시켜서 나오는 결과를 이용하여 열 정보를 얻어오는 것이 아니라, 다음과 같이 sp_prepare 라는 시스템 프로시저로 열 정보만 읽어오게 됩니다.

declare @p1 int

set @p1=NULL

exec sp_prepare @p1 output,NULL,N'CREATE TABLE #SeqNo

(

SeqNo int

)

INSERT #SeqNo VALUES(1)

INSERT #SeqNo VALUES(2)

SELECT * FROM #SeqNo

DROP TABLE #SeqNo',1

select @p1

그런데, sp_prepare문은 쿼리나 테이블일 경우에는 아래와 같이 열 정보를 읽어올 수 있지만, 임시 테이블 또는 Sub 쿼리 형태의 경우에는 열 정보를 읽어오지 못합니다.

열 정보를 읽어올 수 없기 때문에 데이터 흐름 작업에서 출력할 열을 설정할 수 없게 되고 이로 인해 데이터 흐름 작업 내에서 임시 테이블을 못 쓰는 형태가 되어버립니다.

그럼 어떻게 해야 하나?

1. 임시 테이블 대신 일반 테이블 사용하기 (#SeqNo 테이블 대신 tmpSeqNo 테이블로 사용)

임시 테이블을 사용하는 이유는 여러 가지가 있습니다.

첫 번째 굳이 삭제를 해 주지 않아도 세션이 종료되면 삭제가 되며, 동시에 수행하더라도 중복이 발생하지 않게 됩니다. 하지만, 이러한 장점을 포기할 수 있는 상황이라면 일반 테이블 형태로 구성합니다. 단순히 임시 테이블을 일반 테이블로만 바꾸는 것이 아니라,

테이블 생성 및 데이터 적재는 SQL 실행 작업에서 먼저 수행하고,

데이터 흐름에서는 이 테이블에서 데이터를 읽도록 구성하며,

또 다른 SQL 실행 작업에서 삭제 작업을 수행하는 것이지요.

단, 이 때 주의할 점은 데이터 흐름 작업 구성 전에 DB에 테이블을 만들어 놓은 상태에서 설정해야 한다는 것이며, 데이터 흐름 작업의 속성 중 DelayValidation 속성을 True로 변경해야 합니다. 이에 대해서는 다음 글을 참고하세요.

2. 난 그래도 꼭 임시 테이블을 사용하고 싶다고~! (XML 소스를 이용하여 테이블 이름 변경)

동시에 패키지가 수행되기 때문에 반드시 하나의 패키지가 수행될 때 다른 패키지가 영향을 받지 않도록 설정해야 한다면 임시 테이블을 사용할 수 밖에 없겠지요. 물론 tmpSeqNo__121212, tmpSeqNo_212313 와 같이 테이블 명 뒤에 난수를 발생시켜 중복이 안 생기도록 할 수는 있습니다만, 귀찮죠.

다음과 같이 임시 테이블을 사용하도록 할 수 있습니다. 1번 방법과 같이 우선 일반 테이블을 사용하도록 패키지를 구성하세요. 그런 다음, 연결 관리자에서 패키지에서 쓰는 OLE DB 연결을 선택한 후, 속성에서 RetainSameConnection 속성을 True로 변경합니다.

그러고선, 솔루션 탐색기에서 패키지 파일을 선택한 후, 코드 보기를 해서 XML 파일로 패키지를 엽니다.

찾아 바꾸기를 이용해서 tmpSeqNo 를 #tmpSeqNo로 변경합니다.

패키지를 저장합니다.

패키지 내의 작업 개체들을 열어보면 임시 테이블로 쿼리가 변경된 것을 확인하실 수 있으며, 실행 시켜도 정상적으로 실행됩니다.

물론, 이 경우에는 마지막 단계인 테이블 삭제는 없어도 되겠지요.

3. 조금 더 파 보자. 갈 때까지 가보자. (스크립트 변환 – 원본을 이용하여 구현)

또 다른 방법이 있습니다.

데이터 흐름 작업 내에 스크립트 변환 – 원본을 추가한 후, 다음과 같이 Input and Output 탭에서 출력될 열 이름과 유형을 설정하여 추가합니다. 본 예에서는 SeqNo라는 열을 DT_I4로 설정하였습니다.

이 때 한글 버전일 경우에는 Output 0 대신 출력 0 으로 나타날 것입니다.

세 번째 탭인 Connection Manager에서는 다음과 같이 설정합니다.

이제 두 번째 Script 탭으로 이동한 후, 스크립트 편집기를 열고선 다음과 같이 스크립트를 입력합니다.

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()

Dim s As String

s = "CREATE TABLE #SeqNo" & vbCrLf & _

"(" & vbCrLf & _

" SeqNo Int" & vbCrLf & _

")" & vbCrLf & _

" INSERT #SeqNo VALUES(1) " & vbCrLf & _

" INSERT #SeqNo VALUES(2) " & vbCrLf & _

"SELECT * FROM #SeqNo" & vbCrLf & _

"DROP TABLE #SeqNo"

Dim conn As New OleDb.OleDbConnection

Dim cmd As New OleDb.OleDbCommand

Dim dr As OleDb.OleDbDataReader

conn.ConnectionString = Me.Connections.Connection.ConnectionString

conn.Open()

cmd.Connection = conn

cmd.CommandText = s

dr = cmd.ExecuteReader

While dr.Read()

Output0Buffer.AddRow()

Output0Buffer.SeqNo = dr.GetInt32(0)

End While

dr.Close()

conn.Close()

End Sub

End Class

노란 색 부분이 달라질 수 있는 부분입니다. 특히 마지막 노란색 부분 Output0Buffer 부분은 위에서 말하 것과 같이 한글 버전인 경우, 출력0Buffer (공백 없음)로 설정하셔야 합니다.

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

더 좋은 방법이 있을까요? 고민해보고 있으면 더 추가하겠습니다.

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

SSIS가 뭡니까? - 처음 접하시는 분들을 위한 소개 (0)	2009.06.29
SSIS 예제 패키지 파일 이용하기 (0)	2009.06.29
Checkpoint를 이용한 패키지 반복 실행 구성 (0)	2009.06.20
패키지에서 네트워크 연결이 오류가 날 때 (0)	2009.06.20
SQL Server 2008 데이터 가져오기/내보내기 개선점 (0)	2009.06.20

Checkpoint를 이용한 패키지 반복 실행 구성

Microsoft Premier Field Engineer

한대성

SSIS 패키지를 운영하다가 에러가 발생했을 때, 다시 돌리는 방법에 대한 문의가 있어서 간단한 예제로 설명 드리겠습니다.

예제 설명을 위해 다음과 같이 테이블을 준비합니다.

use tempdb

go

CREATE TABLE InputTable

(

InputSource varchar(20),

InputData int Check (InputData<>3) -- 3은 입력 불가 제약조건

)

go

다음과 같은 패키지를 하나 만듭니다.

A. 변수 창에서 InputSource라는 String 변수를 추가합니다.

B. 연결관리자에서 테스트용 DB인 tempdb에 대한 OLE DB 연결을 추가하고, 다음과 같이 네 개의 SQL 실행 작업 태스크를 추가합니다.

C. 각 SQL 실행 태스크에는 다음과 같은 형태로 구성합니다.

Ÿ SQL Statement

INSERT InputTable values( ?, 1) -- 첫 번째 SQL 태스크

INSERT InputTable values( ?, 2) -- 두 번째 SQL 태스크

INSERT InputTable values( ?, 3) -- 세 번째 SQL 태스크

INSERT InputTable values( ?, 4) -- 네 번째 SQL 태스크

Ÿ 입력 매개변수 탭(Parameter Mapping)에서 다음과 같이 입력

D. 이제, 외부 패키지(부모 패키지)에서 현재 패키지(자식 패키지)에게 InputSource 변수를 넘겨주는 기능을 구현해 봅시다.

Ÿ 상단의 SSIS à 구성(Configuration) 메뉴를 선택합니다.

Ÿ 패키지 구성 사용 체크 박스를 선택한 후, 추가 버튼을 눌러 다음과 같이 구성 설정 창이 나오면, 부모 패키지 변수(parent package variable)을 선택하고, Parent Variable 부분에 InputSource이라고 입력합니다.

Ÿ 다음 버튼을 눌러 입력된 변수가 매핑 될 속성을 지정합니다. 다음과 같이 InputSource 변수의 value 속성에 매핑되도록 설정합니다.

Ÿ 구성 설정이 완료된 화면

E. 이제 테스트를 수행해 봅시다. InputSource라는 변수에 FromChild라는 값을 입력하고선 패키지를 실행해 봅시다.

InputData라는 열에 3을 입력하지 못하도록 제약 사항으로 설정해 놨기 때문에 위와 같이 세 번째 SQL 실행 작업이 실패하게 됩니다.

일단 여기까지 테스트 용 자식 패키지를 완성했습니다.

이제 새로운 패키지를 하나 생성합니다. 이 패키지는 부모 패키지로 앞에서 만든 자식 패키지를 반복해서 호출하는 역할을 수행할 것입니다.

A. 우선 앞에서와 같이 InputSource라는 String형 변수를 추가합니다.

B. Foreach 루프 컨테이너를 추가한 후, 속성 창을 열어 Collection 탭에서 Enumator를 Foreach Item Enumator로 설정하고, 다음과 같이 값을 입력합니다.

C. Variable Mappings 탭에서 다음과 같이 각 값들이 A단계에서 추가한 변수(InputSource)에 대입되도록 설정합니다.

D. Foreach 루프 컨테이너 내에 패키지 실행 작업을 추가한 후, 앞에서 생성한 패키지를 가리키도록 설정합니다.

E. 이제 패키지를 실행시켜 봅니다. 역시나 자식 패키지의 세 번째 작업 때문에 실패가 났습니다. 하지만, 세 번째 작업을 제외한 나머지 작업들은 성공해서 데이터가 입력되었습니다.

자, 만약에 이 상태에서 또 패키지가 실행이 된다면 1,2,4의 값은 또 들어가고 3은 여전히 실패하겠지요. 반복해서 실행될 때 또는 자식 패키지만 다시 수행하더라도 패키지가 다시 실행될 때 성공한 작업을 제외한 실패한 작업만 수행하도록 구현하는 것이 본 글에서 설명하는 기능입니다.

다시 자식 패키지로 돌아가서, 패키지의 제어 흐름 영역 아무 곳을 선택한 후, 속성 창에서 다음과 같은 속성 값을 설정합니다.

그런 다음, 모든 SQL 작업들을 선택한 후, FailPackageOnFailure 속성을 True로 변경합니다.

이제 패키지를 저장하고 부모 패키지를 실행시켜 봅시다.

마찬가지로 에러가 발생했지만, CheckpointFileName 속성에서 지정한 위치에 ChildPackageCheckFile.chk라는 파일이 생성되었습니다.

현재 상황을 잠깐 정리하자면, 부모 패키지에서 FromParentLoop1이라는 값을 자식 패키지에게 넘겨주면서 실행을 시켰는데, 자식 패키지의 세 번째 SQL 실행 작업에서는 에러가 발생하였습니다.

Q1. 이 상태에서 다시 부모 패키지를 실행시키면?

Ans) CheckFile이 있기 때문에 Checkfile의 내용을 읽어서 실패한 작업 단계인 세 번째 작업만 수행하게 됩니다. 1,2,4의 값이 중복으로 입력되지 않습니다.

Q2. 에러가 발생한 부분을 수정하였습니다. 그런데 이 상태에서 자식 패키지만 다시 실행시키면? 즉, 에러가 발생했을 때에는 부모 패키지로부터 InputSource라는 변수의 값에 FromParentLoop1이라는 값을 받아왔었는데, 자식 패키지만 따로 실행시키면 이 값이 없어지지 않나요?

Ans) 자식 패키지만 따로 실행시키더라도 패키지가 에러가 났을 때의 변수 설정이 그대로 유지됩니다.

에러가 나는 원인인 제약 조건을 해제하고 자식 패키지만 따로 실행시켜 봅시다.

sp_helpconstraint InputTable

GO

ALTER TABLE InputTable DROP CONSTRAINT CK__InputTabl__Input__7F60ED59

GO

패키지가 성공적으로 처리되면서 Check 파일은 삭제됩니다.

자, 그럼 이걸 어떻게 응용하면 될까요? (이 부분은 설명만 대충 하겠습니다.)

문의하신 고객의 상황은 네트워크 상태가 좋지 않아 간혹 끊어지면서 오류가 발생한다고 합니다. 그래서 다시 패키지를 돌리면 대부분 성공합니다.

이를 해결하는 방법은 여러 가지가 있겠지만, 부모 패키지에서 Foreach 루프 컨테이너의 자식 패키지를 호출하는 부분에 Check 파일이 생성되었는지를 확인하는 부분을 구성해서 만들면 될 것 같습니다.

위와 같이 CHK 파일을 검사하는 스크립트 작업을 추가합니다. 간단히. File.Exists(…) 형태의 구문으로 작성 가능하며, CHK 파일이 존재할 때에는 변수에 CHKFileYN값을 True로 설정하고, 파일이 없는 경우에는 False로 설정하는 것이지요. 이 변수의 기본 값은 True입니다.

For 루프 컨테이너에서는 CHKFileYN이 True일 때에만 수행하도록 설정하면 됩니다. 추가로 반복 횟수를 지정할 수도 있겠지요. (예 : 10번 만 Retry 해라)

패키지가 에러가 없이 성공적으로 종료되면 For 루프 컨테이너는 종료되고, Foreach 루프 컨테이너에서 다른 입력 값이 설정되어 패키지가 수행됩니다.

복잡하게 보일지 모르겠지만, 구현할 만한 기능입니다.^^

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

SSIS 예제 패키지 파일 이용하기 (0)	2009.06.29
데이터 흐름 원본에서 임시 테이블 사용하기 (0)	2009.06.20
패키지에서 네트워크 연결이 오류가 날 때 (0)	2009.06.20
SQL Server 2008 데이터 가져오기/내보내기 개선점 (0)	2009.06.20
SSIS의 트랜잭션에 대해서 (0)	2009.06.20

연구개발/DTS & SSIS

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Process:

Conclusion:

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

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

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

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

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

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

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

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

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

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

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

티스토리툴바