SQL 실행 태스크

  • 아티클

적용 대상: Azure Data Factory의 SQL Server SSIS Integration Runtime

SQL 실행 태스크는 패키지에서 SQL 문 또는 저장 프로시저를 실행합니다. 이 작업에는 순차적으로 실행되는 단일 SQL 문 또는 여러 SQL 문이 포함될 수 있습니다. SQL 실행 태스크는 다음과 같은 용도로 사용할 수 있습니다.

  • 데이터를 삽입하기 위해 테이블 또는 뷰를 자릅니다.

  • 테이블 및 뷰와 같은 데이터베이스 개체를 만들고, 변경하고, 삭제합니다.

  • 데이터를 로드하기 전에 팩트 및 차원 테이블을 다시 만듭니다.

  • 저장 프로시저를 실행합니다. SQL 문이 임시 테이블의 결과를 반환하는 저장 프로시저를 호출하는 경우 WITH RESULT SETS 옵션을 사용하여 결과 집합에 대한 메타데이터를 정의합니다.

  • 쿼리에서 반환된 행 집합을 변수에 저장합니다.

SQL 실행 태스크를 Foreach 루프 및 For 루프 컨테이너와 함께 사용하여 여러 SQL 문을 실행할 수 있습니다. 두 컨테이너는 패키지의 반복 제어 흐름을 구현하며 SQL 실행 태스크를 반복해서 실행할 수 있습니다. 예를 들어 Foreach 루프 컨테이너를 사용하여 패키지는 폴더의 파일을 열거하고 SQL 실행 태스크를 반복적으로 실행하여 각 파일에 저장된 SQL 문을 실행할 수 있습니다.

데이터 원본에 연결

SQL 실행 태스크는 다양한 유형의 연결 관리자를 사용하여 SQL 문 또는 저장 프로시저를 실행하는 데이터 원본에 연결할 수 있습니다. 작업은 다음 표에 나열된 연결 형식을 사용할 수 있습니다.

Connection type ODBC 대상 편집기
EXCEL Excel 연결 관리자
OLE DB OLE DB 연결 관리자
ODBC ODBC 연결 관리자
ADO ADO 연결 관리자
ADO.NET ADO.NET 연결 관리자
Sqlmobile SQL Server Compact Edition 연결 관리자

SQL 문 만들기

이 태스크에서 사용하는 SQL 문의 원본은 문을 포함하는 작업 속성, 하나 또는 여러 문이 포함된 파일에 대한 연결 또는 문이 포함된 변수의 이름일 수 있습니다. SQL 문은 DBMS(원본 데이터베이스 관리 시스템)의 방언으로 작성되어야 합니다. 자세한 내용은 Integration Services(SSIS) 쿼리를 참조하세요.

SQL 문이 파일에 저장되는 경우 태스크는 파일 연결 관리자를 사용하여 파일에 연결합니다. 자세한 내용은 File Connection Manager를 참조하세요.

SSIS 디자이너에서 SQL 작업 편집기 실행 대화 상자를 사용하여 SQL 문을 입력하거나 SQL 쿼리를 만들기 위한 그래픽 사용자 인터페이스인 쿼리 작성기를 사용할 수 있습니다.

참고 항목

SQL 실행 태스크 외부에서 작성된 유효한 SQL 문은 SQL 실행 태스크에 의해 성공적으로 구문 분석되지 않을 수 있습니다.

참고 항목

SQL 실행 태스크는 RecognizeAll ParseMode 열거형 값을 사용합니다. 자세한 내용은 ManagedBatchParser 네임스페이스를 참조하십시오.

일괄 처리로 여러 문 보내기

SQL 실행 태스크에 여러 개의 문이 포함된 경우 문을 그룹화하여 일괄 처리로 실행할 수 있습니다. 일괄 처리의 마지막을 알리려면 GO 명령을 사용합니다. 두 GO 명령 간의 모든 SQL 문은 실행할 OLE DB 공급자에 일괄 처리로 전송됩니다. SQL 명령은 GO 명령으로 구분된 여러 개의 일괄 처리를 포함할 수 있습니다.

일괄 처리로 그룹화할 수 있는 SQL 문의 종류에는 제한이 있습니다. 자세한 내용은 문 일괄 처리를 참조 하세요.

SQL 실행 태스크에서 SQL 문의 일괄 처리를 실행하는 경우 다음 규칙이 일괄 처리에 적용됩니다.

  • 하나의 문만 결과 집합을 반환할 수 있으며 일괄 처리의 첫 번째 문이어야 합니다.

  • 결과 집합에서 결과 바인딩을 사용하는 경우 쿼리는 동일한 수의 열을 반환해야 합니다. 쿼리에서 반환된 열 수가 서로 다르면 태스크가 실패합니다. 그러나 작업이 실패하더라도 DELETE 또는 INSERT 쿼리와 같이 실행되는 쿼리는 성공할 수 있습니다.

  • 결과 바인딩에서 열 이름을 사용하는 경우 쿼리는 작업에 사용되는 결과 집합 이름과 이름이 같은 열을 반환해야 합니다. 열이 없으면 작업이 실패합니다.

  • 태스크에서 매개 변수 바인딩을 사용하는 경우 일괄 처리의 모든 쿼리에는 동일한 수와 형식의 매개 변수가 있어야 합니다.

매개 변수가 있는 SQL 명령 실행

SQL 문 및 저장 프로시저는 입력 매개 변수, 출력 매개 변수 및 반환 코드를 자주 사용합니다. SQL 실행 태스크는 입력, 출력 및 ReturnValue 매개 변수 형식을 지원합니다. 입력 매개 변수의 경우 입력 형식, 출력 매개 변수의 경우 출력 , 반환 코드의 경우 ReturnValue 를 사용합니다.

참고 항목

데이터 공급자가 지원하는 경우에만 SQL 실행 태스크에서 매개 변수를 사용할 수 있습니다.

결과 집합 유형 지정

SQL 명령 유형에 따라 SQL 실행 태스크에서 결과 집합이 반환될 수도 있고 반환되지 않을 수도 있습니다. 예를 들어 SELECT 문은 일반적으로 결과 집합을 반환하지만 INSERT 문은 결과 집합을 반환하지 않습니다. SELECT 문의 결과 집합에는 행 0개, 행 1개 또는 여러 행이 포함될 수 있습니다. 저장 프로시저도 반환 코드라고 하는 정수 값을 반환하여 프로시저의 실행 상태를 표시할 수 있습니다. 이 경우에는 결과 집합이 단일 행으로 구성됩니다.

SQL 실행 태스크 구성

SQL 실행 태스크는 다음과 같은 방법으로 구성할 수 있습니다.

  • 데이터베이스에 연결하는 데 사용할 연결 관리자 유형을 지정합니다.

  • SQL 문이 반환하는 결과 집합의 형식을 지정합니다.

  • SQL 문에 대한 제한 시간을 지정합니다.

  • SQL 문의 원본을 지정합니다.

  • 태스크가 SQL 문의 준비 단계를 건너뛸지 여부를 나타냅니다.

  • ADO 연결 형식을 사용하는 경우 SQL 문이 저장 프로시저인지 여부를 나타내야 합니다. 다른 연결 형식에서 이 속성은 읽기 전용이며 항상 false값을 갖습니다.

프로그래밍 방식으로 또는 SSIS 디자이너를 통해 속성을 설정할 수 있습니다.

일반 페이지 - SQL 작업 편집기 실행

SQL 실행 태스크 편집기 대화 상자의 일반 페이지를 사용하여 SQL 실행 태스크를 구성하고 태스크가 실행되는 SQL 문을 제공합니다.

Transact-SQL 쿼리 언어에 대한 자세한 내용은 Transact-SQL 참조(데이터베이스 엔진)를 참조하세요.

정적 옵션

입력
워크플로에서 SQL 실행 태스크의 고유한 이름을 제공합니다. 제공된 이름은 SSIS 디자이너 내에 표시됩니다.

Description
SQL 실행 태스크를 설명합니다. 패키지를 자체 문서화하고 유지 관리하기 쉽게 만드는 것이 가장 좋습니다. 해당 용도로 작업을 설명합니다.

TimeOut
시간 초과 전에 태스크가 실행될 최대 시간(초)을 지정합니다. 값 0은 무한 시간을 나타냅니다. 기본값은 0입니다.

참고 항목

저장 프로시저는 연결이 만들어질 시간과 TimeOut에서 지정한 시간(초)보다 큰 트랜잭션이 완료될 시간을 제공하여 절전 모드 기능을 에뮬레이트하는 경우 시간 초과되지 않습니다. 그러나 쿼리를 실행하는 저장 프로시저에는 항상 TimeOut에서 지정한 시간 제한이 적용됩니다.

CodePage
변수에서 유니코드 값을 변환할 때 사용할 코드 페이지를 지정합니다. 기본값은 로컬 컴퓨터의 코드 페이지입니다.

참고 항목

SQL 실행 태스크에서 ADO 또는 ODBC 연결 관리자를 사용하는 경우 CodePage 속성을 사용할 수 없습니다. 솔루션에서 코드 페이지를 사용해야 하는 경우 SQL 실행 태스크와 함께 OLE DB 또는 ADO.NET 연결 관리자를 사용합니다.

TypeConversionMode
이 속성을 허용으로 설정하면 SQL 실행 태스크는 출력 매개 변수 및 쿼리 결과를 결과가 할당된 변수의 데이터 형식으로 변환하려고 시도합니다. 단일 행 결과 집합 형식에 적용됩니다.

ResultSet
실행 중인 SQL 문에 필요한 결과 형식을 지정합니다. 단일 행, 전체 결과 집합, XML 또는 없음 중에서 선택합니다.

ConnectionType
데이터 원본에 연결하는 데 사용할 연결 관리자 유형을 선택합니다. 사용 가능한 연결 유형에는 OLE DB, ODBC, ADO, ADO.NET 및 SQLMOBILE이 포함됩니다.

관련 항목:OLE DB 연결 관리자, ODBC 연결 관리자, ADO 연결 관리자, ADO.NET 연결 관리자, SQL Server Compact Edition 연결 관리자

연결
정의된 연결 관리자 목록에서 연결을 선택합니다. 새 연결을 만들려면 <<새 연결...>을 선택합니다.

SQLSourceType
태스크가 실행되는 SQL 문의 원본 유형을 선택합니다.

SQL 실행 태스크에서 사용하는 연결 관리자 유형에 따라 매개 변수가 있는 SQL 문에서 특정 매개 변수 표식을 사용해야 합니다.

이 속성에는 다음 표에 나열된 옵션이 있습니다.

값 Description
직접 입력 원본을 Transact-SQL 문으로 설정합니다. 이 값을 선택하면 동적 옵션 SQLStatement가 표시됩니다.
파일 연결 Transact-SQL 문이 포함된 파일을 선택합니다. 이 옵션을 설정하면 동적 옵션인 FileConnection이 표시됩니다.
변수 원본을 Transact-SQL 문을 정의하는 변수로 설정합니다. 이 값을 선택하면 동적 옵션인 SourceVariable이 표시됩니다.

QueryIsStoredProcedure
실행하도록 지정한 SQL 문이 저장 프로시저인지 여부를 표시합니다. 이 속성은 작업에서 ADO 연결 관리자를 사용하는 경우에만 읽기/쓰기가 가능합니다. 그렇지 않으면 속성이 읽기 전용이며 해당 값은 false입니다.

BypassPrepare
SQL 문이 준비되었는지 여부를 나타냅니다. true 이면 준비를 건너뛰고 false 이면 SQL 문을 실행하기 전에 SQL 문을 준비합니다. 이 옵션은 준비를 지원하는 OLE DB 연결에만 사용할 수 있습니다.

관련 항목:준비된 실행

찾아보기
열기 대화 상자를 사용하여 SQL 문이 포함된 파일을 찾습니다. SQLStatement 속성에 SQL 문으로 파일의 내용을 복사할 파일을 선택합니다.

쿼리 작성
쿼리를 만드는 데 사용되는 그래픽 도구인 쿼리 작성기 대화 상자를 사용하여 SQL 문을 만듭니다. 이 옵션은 SQLSourceType 옵션을 직접 입력으로 설정한 경우에만 사용할 수 있습니다.

쿼리 구문 분석
SQL 문의 구문의 유효성을 검사합니다.

SQLSourceType 동적 옵션

SQLSourceType = 직접 입력

Sqlstatement
옵션 상자에서 실행할 SQL 문을 입력하거나 찾아보기 단추(...)를 클릭하여 SQL 쿼리 입력 대화 상자에 SQL 문을 입력하거나 쿼리 작성기 대화 상자를 사용하여 쿼리 작성을 클릭합니다.

관련 항목:쿼리 작성기

SQLSourceType = 파일 연결

FileConnection
기존 파일 연결 관리자를 선택하거나 <새 연결...>을 클릭하여 새 연결 관리자를 만듭니다.

관련 항목:File Connection Manager, File Connection Manager Editor

SQLSourceType = 변수

SourceVariable
기존 변수를 선택하거나 <새 변수...> 를 클릭하여 새 변수를 만듭니다.

관련 항목: Integration Services(SSIS) 변수, 변수 추가

매개 변수 매핑 페이지 - SQL 작업 편집기 실행

SQL 실행 태스크 편집기 대화 상자의 매개 변수 매핑 페이지를 사용하여 변수를 SQL 문의 매개 변수에 매핑할 수 있습니다.

옵션

변수 이름
추가를 클릭하여 매개 변수 매핑을 추가했으면 변수 추가 대화 상자를 사용하여 목록에서 시스템 또는 사용자 정의 변수를 선택하거나 <새 변수...>를 클릭하여 새 변수를 추가합니다.

관련 항목: Integration Services(SSIS) 변수

방향
매개 변수의 방향을 선택합니다. 각 변수를 입력 매개 변수, 출력 매개 변수 또는 반환 코드에 매핑합니다.

데이터 형식
매개 변수의 데이터 형식을 선택합니다. 사용 가능한 데이터 형식 목록은 태스크에서 사용하는 연결 관리자에서 선택한 공급자에 따라 다릅니다.

매개 변수 이름
매개 변수 이름을 입력합니다.

태스크에서 사용하는 연결 관리자 유형에 따라 숫자 또는 매개 변수 이름을 사용해야 합니다. 일부 연결 관리자 유형에서는 매개 변수 이름의 첫 문자가 @ 기호, @Param1 같은 특정 이름 또는 열 이름을 매개 변수 이름으로 지정해야 합니다.

매개 변수 크기
문자열 및 이진 필드와 같은 가변 길이를 가지는 매개 변수의 크기를 제공합니다.

이 설정은 공급자가 가변 길이 매개 변수 값에 충분한 공간을 할당하도록 합니다.

추가
매개 변수 매핑을 추가하려면 클릭합니다.

제거
목록에서 매개 변수 매핑을 선택한 다음 제거를 클릭합니다.

결과 집합 페이지 - SQL 작업 편집기 실행

SQL 실행 태스크 편집기 대화 상자의 결과 집합 페이지를 사용하여 SQL 문의 결과를 새 변수 또는 기존 변수에 매핑합니다. 일반 페이지의 ResultSet이 없음으로 설정된 경우 이 대화 상자의 옵션을 사용할 수 없습니다.

옵션

결과 이름
추가를 클릭하여 결과 집합 매핑 집합을 추가한 후 결과의 이름을 입력합니다. 결과 집합 유형에 따라 특정 결과 이름을 사용해야 합니다.

결과 집합 유형이 단일 행이면 쿼리에서 반환한 열 이름이나 쿼리에서 반환한 열의 열 목록에서 열 위치를 나타내는 숫자 중 하나를 사용할 수 있습니다.

결과 집합 형식이 전체 결과 집합 또는 XML인 경우 0을 결과 집합 이름으로 사용해야 합니다.

변수 이름
변수를 선택하여 결과 집합을 변수로 매핑하거나 <새 변수...>를 클릭하여 변수 추가 대화 상자를 사용하여 새 변수를 추가합니다.

추가
결과 집합 매핑을 추가하려면 클릭합니다.

제거
목록에서 결과 집합 매핑을 선택한 다음 제거를 클릭합니다.

SQL 실행 태스크의 매개 변수

SQL 문 및 저장 프로시저는 입력 매개 변수, 출력 매개 변수 및 반환 코드를 자주 사용합니다. Integration Services에서 SQL 실행 태스크는 입력, 출력 및 ReturnValue 매개 변수 형식을 지원합니다. 입력 매개 변수의 경우 입력 형식, 출력 매개 변수의 경우 출력 , 반환 코드의 경우 ReturnValue 를 사용합니다.

참고 항목

데이터 공급자가 지원하는 경우에만 SQL 실행 태스크에서 매개 변수를 사용할 수 있습니다.

쿼리와 저장 프로시저를 포함하여 SQL 명령의 매개 변수는 SQL 실행 태스크의 범위, 부모 컨테이너 또는 패키지 범위 내에서 생성된 사용자 정의 변수에 매핑됩니다. 변수 값은 디자인 타임에 설정하거나 런타임에 동적으로 채울 수 있습니다. 매개 변수를 시스템 변수에 매핑할 수도 있습니다. 자세한 내용은 Integration Services(SSIS) 변수 및 시스템 변수를 참조하세요.

그러나 SQL 실행 태스크에서 매개 변수 및 반환 코드를 사용하는 것은 태스크가 지원하는 매개 변수 형식과 이러한 매개 변수를 매핑하는 방법을 아는 것 이상입니다. SQL 실행 태스크에서 매개 변수를 성공적으로 사용하고 코드를 반환하기 위한 추가 사용 요구 사항 및 지침이 있습니다. 이 항목의 나머지 부분에서는 이러한 사용 요구 사항 및 지침을 다룹니다.

매개 변수 이름 및 표식

SQL 실행 태스크가 사용하는 연결 형식에 따라 SQL 명령 구문이 사용하는 매개 변수 표식이 달라집니다. 예를 들어 ADO.NET 연결 관리자 유형을 사용하려면 SQL 명령에서 @varParameter 형식의 매개 변수 표식을 사용해야 하지만 OLE DB 연결 형식에는 물음표(?) 매개 변수 표식을 사용해야 합니다.

변수와 매개 변수 간의 매핑에서 매개 변수 이름으로 사용할 수 있는 이름도 연결 관리자 유형에 따라 다릅니다. 예를 들어 ADO.NET 연결 관리자 유형에는 @ 접두사가 있는 사용자 정의 이름을 사용하지만 OLE DB 연결 관리자 유형에는 0부터 시작하는 서수의 숫자 값을 매개 변수 이름으로 사용해야 합니다.

다음 표에서는 SQL 실행 태스크에서 사용할 수 있는 연결 관리자 유형에 대한 SQL 명령에 대한 요구 사항을 요약합니다.

Connection type 매개 변수 표식 매개 변수 이름 예제 SQL 명령
ADO ? Param1, Param2, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET @<parameter name> @<parameter name> SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC ? 1, 2, 3, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL 및 OLE DB ? 0, 1, 2, 3, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

ADO.NET 및 ADO 연결 관리자에서 매개 변수 사용

ADO.NET 및 ADO 연결 관리자에는 매개 변수를 사용하는 SQL 명령에 대한 특정 요구 사항이 있습니다.

  • ADO.NET 연결 관리자는 SQL 명령에서 매개 변수 이름을 매개 변수 표식으로 사용해야 합니다. 즉, 변수를 매개 변수에 직접 매핑할 수 있습니다. 예를 들어 변수 @varName 는 명명 @parName 된 매개 변수에 매핑되고 매개 변수 @parName에 값을 제공합니다.

  • ADO 연결 관리자는 SQL 명령에서 물음표(?)를 매개 변수 표식으로 사용해야 합니다. 그러나 정수 값을 제외한 모든 사용자 정의 이름을 매개 변수 이름으로 사용할 수 있습니다.

매개 변수에 값을 제공하기 위해 변수는 매개 변수 이름에 매핑됩니다. 그런 다음 SQL 실행 태스크는 매개 변수 목록에서 매개 변수 이름의 서수 값을 사용하여 변수에서 매개 변수로 값을 로드합니다.

EXCEL, ODBC 및 OLE DB 연결 관리자의 매개 변수 사용

EXCEL, ODBC 및 OLE DB 연결 관리자는 SQL 명령에서 물음표(?)를 매개 변수 표식으로 사용하고 0부터 시작하는 숫자 또는 1부터 시작하는 숫자 값을 매개 변수 이름으로 사용해야 합니다. SQL 실행 태스크에서 ODBC 연결 관리자를 사용하는 경우 쿼리의 첫 번째 매개 변수에 매핑되는 매개 변수 이름은 1입니다. 그렇지 않으면 매개 변수의 이름은 0입니다. 후속 매개 변수의 경우 매개 변수 이름의 숫자 값은 매개 변수 이름이 매핑되는 SQL 명령의 매개 변수를 나타냅니다. 예를 들어 이름이 3인 매개 변수는 세 번째 매개 변수에 매핑되며 SQL 명령에서 세 번째 물음표(?)로 표현됩니다.

매개 변수에 값을 제공하기 위해 변수는 매개 변수 이름에 매핑되고 SQL 실행 태스크는 매개 변수 이름의 서수 값을 사용하여 변수에서 매개 변수로 값을 로드합니다.

연결 관리자가 사용하는 공급자에 따라 일부 OLE DB 데이터 형식이 지원되지 않을 수 있습니다. 예를 들어 Excel 드라이버는 제한된 데이터 형식 집합만 인식합니다. Excel 드라이버를 사용하는 Jet 공급자의 동작에 대한 자세한 내용은 Excel 원본을 참조하세요.

OLE DB 연결 관리자의 매개 변수 사용

SQL 실행 태스크에서 OLE DB 연결 관리자를 사용하는 경우 작업의 BypassPrepare 속성을 사용할 수 있습니다. SQL 실행 태스크에서 매개 변수와 함께 SQL 문을 사용하는 경우 이 속성을 true 로 설정해야 합니다.

OLE DB 연결 관리자를 사용하는 경우 SQL 실행 태스크가 OLE DB 공급자를 통해 매개 변수 정보를 파생시킬 수 없으므로 매개 변수가 있는 하위 쿼리를 사용할 수 없습니다. 그러나 식을 사용하여 매개 변수 값을 쿼리 문자열에 연결하고 작업의 SqlStatementSource 속성을 설정할 수 있습니다.

날짜 및 시간 데이터 형식과 함께 매개 변수 사용

ADO.NET 및 ADO 연결 관리자에서 날짜 및 시간 매개 변수 사용

SQL Server 유형, 시간 및 datetimeoffset의 데이터를 읽을 때 ADO.NET 또는 ADO 연결 관리자를 사용하는 SQL 실행 태스크에는 다음과 같은 추가 요구 사항이 있습니다.

  • 시간 데이터의 경우 ADO.NET 연결 관리자는 매개 변수 형식이 Input 또는 Output이고 데이터 형식이 문자열인 매개 변수에 이 데이터를 저장해야 합니다.

  • datetimeoffset 데이터의 경우 ADO.NET 연결 관리자에서 이 데이터를 다음 매개 변수 중 하나에 저장해야 합니다.

    • 매개 변수 형식이 Input이고 데이터 형식이 문자열인 매개 변수입니다.

    • 매개 변수 형식이 Output 또는 ReturnValue이고 데이터 형식이 datetimeoffset, string 또는 datetime2인 매개 변수입니다. 데이터 형식이 string 또는 datetime2인 매개 변수를 선택하면 Integration Services는 데이터를 문자열 또는 datetime2로 변환합니다.

  • ADO 연결 관리자는 매개 변수 형식이 Input 또는 Output이고 데이터 형식이 adVarWchar인 매개 변수에 시간 또는 datetimeoffset 데이터를 저장해야 합니다.

SQL Server 데이터 형식 및 Integration Services 데이터 형식에 매핑하는 방법에 대한 자세한 내용은 데이터 형식(Transact-SQL) 및 Integration Services 데이터 형식을 참조하세요.

OLE DB 연결 관리자에서 날짜 및 시간 매개 변수 사용

OLE DB 연결 관리자를 사용하는 경우 SQL 실행 태스크에는 SQL Server 데이터 형식, 날짜, 시간, 날짜/시간, datetime2 및 datetimeoffset의 데이터에 대한 특정 스토리지 요구 사항이 있습니다. 다음 매개 변수 형식 중 하나에 이 데이터를 저장해야 합니다.

  • NVARCHAR 데이터 형식의 입력 매개 변수입니다.

  • 다음 표에 나열된 대로 적절한 데이터 형식의 출력 매개 변수입니다.

    Output 매개 변수 유형 날짜 데이터 형식
    DBDATE date
    DBTIME2 time
    DBTIMESTAMP datetime, datetime2
    DBTIMESTAMPOFFSET datetimeoffset

데이터가 적절한 입력 또는 출력 매개 변수에 저장되지 않으면 패키지가 실패합니다.

ODBC 연결 관리자의 날짜 및 시간 매개 변수 사용

ODBC 연결 관리자를 사용하는 경우 SQL 실행 태스크에는 SQL Server 데이터 형식, 날짜, 시간, 날짜/시간, datetime2 또는 datetimeoffset 중 하나가 있는 데이터에 대한 특정 스토리지 요구 사항이 있습니다. 다음 매개 변수 형식 중 하나에 이 데이터를 저장해야 합니다.

  • SQL_WVARCHAR 데이터 형식의 입력 매개 변수

  • 다음 표에 나열된 대로 적절한 데이터 형식의 출력 매개 변수입니다.

    Output 매개 변수 유형 날짜 데이터 형식
    SQL_DATE date
    SQL_SS_TIME2 time
    SQL_TYPE_TIMESTAMP

    -또는-

    SQL_TIMESTAMP    		 datetime, datetime2
    SQL_SS_TIMESTAMPOFFSET datetimeoffset

데이터가 적절한 입력 또는 출력 매개 변수에 저장되지 않으면 패키지가 실패합니다.

WHERE 절에서 매개 변수 사용

원본 테이블의 각 행이 SQL 명령에 적합하도록 만들기 위해 만족시켜야 할 조건을 정의하는 필터를 지정하기 위해 SELECT, INSERT, UPDATE 및 DELETE 명령에 WHERE 절을 포함하는 경우가 많습니다. 매개 변수는 WHERE 절에 필터 값을 제공합니다.

매개 변수 표식을 사용하여 매개 변수 값을 동적으로 제공할 수 있습니다. SQL 문에서 매개 변수 표식 및 매개 변수 이름을 사용할 수 있는 규칙은 EXECUTE SQL에서 사용하는 연결 관리자 유형에 따라 달라집니다.

다음 표에서는 연결 관리자 유형별 SELECT 명령의 예를 나열합니다. INSERT, UPDATE 및 DELETE 문은 비슷합니다. 이 예제에서는 SELECT를 사용하여 ProductID가 두 매개 변수로 AdventureWorks2022 지정된 값보다 크고 작은 Product 테이블에서 제품을 반환합니다.

Connection type SELECT 구문
EXCEL, ODBC 및 OLEDB SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

이 예제에는 다음 이름을 가진 매개 변수가 필요합니다.

  • EXCEL 및 OLED DB 연결 관리자는 매개 변수 이름 0과 1을 사용합니다. ODBC 연결 형식은 1과 2를 사용합니다.

  • ADO 연결 형식은 Param1 및 Param2와 같은 두 개의 매개 변수 이름을 사용할 수 있지만 매개 변수 목록의 서수 위치에 따라 매개 변수를 매핑해야 합니다.

  • ADO.NET 연결 형식에서는 매개 변수 이름 @parmMinProductID 및 @parmMaxProductID를 사용합니다.

저장 프로시저와 함께 매개 변수 사용

저장 프로시저를 실행하는 SQL 명령은 매개 변수 매핑을 사용할 수도 있습니다. 매개 변수 표식 및 매개 변수 이름을 사용하는 방법에 대한 규칙은 매개 변수가 있는 쿼리에 대한 규칙과 마찬가지로 SQL 실행에서 사용하는 연결 관리자 유형에 따라 달라집니다.

다음 표에서는 연결 관리자 유형별 EXEC 명령의 예를 나열합니다. 이 예에서는 의 uspGetBillOfMaterials AdventureWorks2022저장 프로시저를 실행합니다. 저장 프로시저는 입력 매개 변수와 @CheckDate입력 매개 변수를 @StartProductID 사용합니다.

Connection type EXEC 구문
EXCEL 및 OLEDB EXEC uspGetBillOfMaterials ?, ?
ODBC {call uspGetBillOfMaterials(?, ?)}

ODBC 호출 구문에 대한 자세한 내용은 MSDN Library의 ODBC 프로그래머 참조에서 프로시저 매개 변수 항목을 참조하세요.
ADO IsQueryStoredProcedure가 False로 설정된 경우EXEC uspGetBillOfMaterials ?, ?

IsQueryStoredProcedure가 True로 설정된 경우uspGetBillOfMaterials
ADO.NET IsQueryStoredProcedure가 False로 설정된 경우EXEC uspGetBillOfMaterials @StartProductID, @CheckDate

IsQueryStoredProcedure가 True로 설정된 경우uspGetBillOfMaterials

출력 매개 변수를 사용하려면 구문에서 각 매개 변수 표식 다음에 OUTPUT 키워드가 와야 합니다. 예를 들어 다음과 같은 출력 매개 변수 구문이 올바릅니다 EXEC myStoredProcedure ? OUTPUT.

Transact-SQL 저장 프로시저에서 입력 및 출력 매개 변수 사용에 대한 자세한 내용은 EXECUTE(Transact-SQL)를 참조하세요.

변수에 매개 변수 매핑

이 섹션에서는 SQL 실행 태스크에서 매개 변수가 있는 SQL 문을 사용하는 방법과 SQL 문의 변수와 매개 변수 간 매핑을 만드는 방법에 대해 설명합니다.

  1. SSDT(SQL Server Data Tools)에서 작업하려는 Integration Services 패키지를 엽니다.

  2. 솔루션 탐색기에서 패키지를 두 번 클릭하여 엽니다.

  3. 제어 흐름 탭을 클릭합니다.

  4. 패키지에 SQL 실행 태스크가 아직 포함되어 있지 않은 경우 패키지의 제어 흐름에 하나를 추가합니다. 자세한 내용은 제어 흐름에서 태스크 또는 컨테이너 추가 또는 삭제를 참조하세요.

  5. SQL 실행 태스크를 두 번 클릭합니다.

  6. 다음 방법 중 하나로 매개 변수가 있는 SQL 명령을 제공합니다.

    • 직접 입력을 사용하고 SQLStatement 속성에 SQL 명령을 입력합니다.

    • 직접 입력을 사용하여 쿼리 작성을 클릭한 후 쿼리 작성기에서 제공하는 그래픽 도구를 사용하여 SQL 명령을 만듭니다.

    • 파일 연결을 사용한 후 SQL 명령이 포함된 파일을 참조합니다.

    • 변수를 사용한 다음 SQL 명령이 포함된 변수를 참조합니다.

    매개 변수가 있는 SQL 문에 사용하는 매개 변수 표식은 SQL 실행 태스크에서 사용하는 연결 형식에 따라 달라집니다.

    Connection type 매개 변수 표식
    ADO ?
    ADO.NET 및 SQLMOBILE @<parameter name>
    ODBC ?
    EXCEL 및 OLE DB ?

    다음 표에서는 연결 관리자 유형별 SELECT 명령의 예를 나열합니다. 매개 변수는 WHERE 절에 필터 값을 제공합니다. 이 예제에서는 SELECT를 사용하여 ProductID가 두 매개 변수로 AdventureWorks2022 지정된 값보다 크고 작은 Product 테이블에서 제품을 반환합니다.

    Connection type SELECT 구문
    EXCEL, ODBC 및 OLEDB SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
    ADO SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
    ADO.NET SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

  7. 매개 변수 매핑을 클릭합니다.

  8. 매개 변수 매핑을 추가하려면 추가를 클릭합니다.

  9. 매개 변수 이름 상자에 이름을 입력합니다.

    사용하는 매개 변수 이름은 SQL 실행 태스크에서 사용하는 연결 형식에 따라 달라집니다.

    Connection type 매개 변수 이름
    ADO Param1, Param2, ...
    ADO.NET 및 SQLMOBILE @<parameter name>
    ODBC 1, 2, 3, ...
    EXCEL 및 OLE DB 0, 1, 2, 3, ...

  10. 변수 이름 목록에서 변수를 선택합니다. 자세한 내용은 패키지에서 사용자 정의 변수의 추가, 삭제, 변경 범위를 참조하세요.

  11. 방향 목록에서 매개 변수가 입력, 출력 또는 반환 값인지 여부를 지정합니다.

  12. 데이터 형식 목록에서 매개 변수의 데이터 형식을 설정합니다.

    Important

    매개 변수의 데이터 형식은 변수의 데이터 형식과 호환되어야 합니다.

  13. SQL 문의 각 매개 변수에 대해 8~11단계를 반복합니다.

    Important

    매개 변수 매핑 순서는 매개 변수가 SQL 문에 표시되는 순서와 동일해야 합니다.

  14. 확인을 클릭합니다.

반환 코드의 값 가져오기

저장 프로시저는 반환 코드라고 하는 정수 값을 반환하여 프로시저의 실행 상태를 나타낼 수 있습니다. SQL 실행 태스크에서 반환 코드를 구현하려면 ReturnValue 형식의 매개 변수를 사용합니다.

다음 표에서는 반환 코드를 구현하는 EXEC 명령의 몇 가지 예제를 연결 유형별로 나열합니다. 모든 예에서는 input 매개 변수를 사용합니다. 매개 변수 표식 및 매개 변수 이름을 사용하는 방법에 대한 규칙은 입력, 출력 및 ReturnValue와 같은 모든 매개 변수 형식에 대해 동일합니다.

일부 구문은 매개 변수 리터럴을 지원하지 않습니다. 이 경우 변수를 사용하여 매개 변수 값을 제공해야 합니다.

Connection type EXEC 구문
EXCEL 및 OLEDB EXEC ? = myStoredProcedure 1
ODBC {? = call myStoredProcedure(1)}

ODBC 호출 구문에 대한 자세한 내용은 MSDN Library의 ODBC 프로그래머 참조에서 프로시저 매개 변수 항목을 참조하세요.
ADO IsQueryStoreProcedure가 False로 설정된 경우EXEC ? = myStoredProcedure 1

IsQueryStoreProcedure가 True로 설정된 경우myStoredProcedure
ADO.NET IsQueryStoreProcedure를 True로 설정합니다.

myStoredProcedure

이전 표에 표시된 구문에서 SQL 실행 태스크는 직접 입력 소스 형식을 사용하여 저장 프로시저를 실행합니다. SQL 실행 태스크는 파일 연결 원본 형식을 사용하여 저장 프로시저를 실행할 수도 있습니다. SQL 실행 태스크에서 직접 입력 또는 파일 연결 원본 형식을 사용하는지 여부에 관계없이 ReturnValue 형식의 매개 변수를 사용하여 반환 코드를 구현합니다.

Transact-SQL 저장 프로시저에서 반환 코드 사용에 대한 자세한 내용은 RETURN(Transact-SQL)을 참조하세요.

SQL 실행 태스크의 결과 집합

Integration Services 패키지에서 결과 집합이 SQL 실행 태스크로 반환되는지 여부는 태스크에서 사용하는 SQL 명령의 유형에 따라 달라집니다. 예를 들어 SELECT 문은 일반적으로 결과 집합을 반환하지만 INSERT 문은 결과 집합을 반환하지 않습니다.

결과 집합의 내용도 SQL 명령에 따라 다릅니다. 예를 들어 SELECT 문의 결과 집합에는 행 0개, 행 1개 또는 여러 행이 포함될 수 있습니다. 그러나 개수 또는 합계를 반환하는 SELECT 문의 결과 집합에는 단일 행만 포함됩니다.

SQL 실행 태스크에서 결과 집합을 사용하려면 SQL 명령에서 결과 집합을 반환하는지 여부와 결과 집합의 내용을 아는 것만으로는 충분하지 않습니다. SQL 실행 태스크에서 결과 집합을 성공적으로 사용하기 위한 추가 사용 요구 사항 및 지침이 있습니다. 이 항목의 나머지 부분에서는 이러한 사용 요구 사항 및 지침을 다룹니다.

결과 집합 유형 지정

SQL 실행 태스크는 다음과 같은 유형의 결과 집합을 지원합니다.

  • 없음 결과 집합은 쿼리가 결과를 반환하지 않을 때 사용됩니다. 예를 들어 이 결과 집합은 테이블의 레코드를 추가, 변경 및 삭제하는 쿼리에 사용됩니다.

  • 단일 행 결과 집합은 쿼리가 하나의 행만 반환할 때 사용됩니다. 예를 들어 이 결과 집합은 개수 또는 합계를 반환하는 SELECT 문에 사용됩니다.

  • 전체 결과 집합 결과 집합은 쿼리가 여러 행을 반환할 때 사용됩니다. 예를 들어 이 결과 집합은 테이블의 모든 행을 검색하는 SELECT 문에 사용됩니다.

  • XML 결과 집합은 쿼리가 XML 형식으로 결과 집합을 반환할 때 사용됩니다. 예를 들어 이 결과 집합은 FOR XML 절을 포함하는 SELECT 문에 사용됩니다.

SQL 실행 태스크에서 전체 결과 집합 결과 집합을 사용하고 쿼리가 여러 행 집합을 반환하는 경우 태스크는 첫 번째 행 집합만 반환합니다. 이 행 집합에서 오류를 생성하면 태스크에서 오류를 보고합니다. 다른 행 집합이 오류를 생성하는 경우 태스크는 오류를 보고하지 않습니다.

결과 집합으로 변수 채우기

결과 집합 형식이 단일 행, 행 집합 또는 XML인 경우 쿼리가 반환하는 결과 집합을 사용자 정의 변수에 바인딩할 수 있습니다.

결과 집합 형식이 단일 행인 경우 열 이름을 결과 집합 이름으로 사용하여 반환 결과의 열을 변수에 바인딩하거나 열 목록에서 열의 서수 위치를 결과 집합 이름으로 사용할 수 있습니다. 예를 들어 쿼리 SELECT Color FROM Production.Product WHERE ProductID = ? 의 결과 집합 이름은 색 또는 0일 수 있습니다. 쿼리가 여러 열을 반환하고 모든 열의 값에 액세스하려는 경우 각 열을 다른 변수에 바인딩해야 합니다. 숫자를 결과 집합 이름으로 사용하여 변수에 열을 매핑하는 경우 열이 쿼리의 열 목록에 표시되는 순서가 숫자에 반영됩니다. 예를 들어 쿼리SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ?에서 Color 열에는 0, ListPrice 열에는 1을 사용합니다. 열 이름을 결과 집합의 이름으로 사용하는 기능은 태스크가 사용하도록 구성된 공급자에 따라 달라집니다. 일부 공급자만 열 이름을 사용할 수 있습니다.

단일 값을 반환하는 일부 쿼리에는 열 이름이 포함되지 않을 수 있습니다. 예를 들어 이 문 SELECT COUNT (*) FROM Production.Product 은 열 이름을 반환하지 않습니다. 결과 이름으로 서수 위치 0을 사용하여 반환 결과에 액세스할 수 있습니다. 열 이름별로 반환 결과에 액세스하려면 쿼리에 AS <alias name> 절을 포함하여 열 이름을 제공해야 합니다. 이 문SELECT COUNT (*)AS CountOfProduct FROM Production.Product은 CountOfProduct 열을 제공합니다. 그런 다음 CountOfProduct 열 이름 또는 서수 위치 0을 사용하여 반환 결과 열에 액세스할 수 있습니다.

결과 집합 형식이 전체 결과 집합 또는 XML인 경우 0을 결과 집합 이름으로 사용해야 합니다.

변수를 단일 행 결과 집합 유형이 있는 결과 집합에 매핑하면 변수에는 결과 집합이 포함하는 열의 데이터 형식과 호환되는 데이터 형식이 있어야 합니다. 예를 들어 문자열 데이터 형식의 열이 포함된 결과 집합은 숫자 데이터 형식의 변수에 매핑할 수 없습니다. TypeConversionMode 속성을 Allowed로 설정하면 SQL 실행 태스크는 출력 매개 변수와 쿼리 결과를 결과가 할당되는 변수의 데이터 형식으로 변환합니다.

XML 결과 집합은 문자열 또는 개체 데이터 형식이 있는 변수에만 매핑할 수 있습니다. 변수에 문자열 데이터 형식이 있는 경우 SQL 실행 태스크는 문자열을 반환하고 XML 원본은 XML 데이터를 사용할 수 있습니다. 변수에 개체 데이터 형식이 있는 경우 SQL 실행 태스크는 DOM(문서 개체 모델) 개체를 반환합니다.

전체 결과 집합은 개체 데이터 형식의 변수에 매핑되어야 합니다. 반환 결과는 행 집합 개체입니다. Foreach 루프 컨테이너를 사용하여 개체 변수에 저장된 테이블 행 값을 패키지 변수로 추출한 다음 스크립트 태스크를 사용하여 패키지 변수에 저장된 데이터를 파일에 쓸 수 있습니다. Foreach 루프 컨테이너 및 스크립트 태스크를 사용하여 이 작업을 수행하는 방법에 대한 데모입니다.

다음 표에는 결과 집합에 매핑할 수 있는 변수의 데이터 형식이 요약되어 있습니다.

결과 집합 형식 변수의 데이터 형식 개체 형식
단일 행 결과 집합의 형식 열과 호환되는 모든 형식입니다. 해당 없음
전체 결과 집합 Object 작업에서 ADO, OLE DB, Excel 및 ODBC 연결 관리자를 비롯한 네이티브 연결 관리자를 사용하는 경우 반환된 개체는 ADO Recordset입니다.

태스크에서 ADO.NET 연결 관리자와 같은 관리되는 연결 관리자를 사용하는 경우 반환된 개체는 System.Data.DataSet입니다.

다음 예제와 같이 스크립트 작업을 사용하여 System.Data.DataSet 개체에 액세스할 수 있습니다.

Dim dt As Data.DataTable

Dim ds As Data.DataSet = CType(Dts.Variables("Recordset").Value, DataSet) dt = ds.Tables(0)
XML 문자열 문자열
XML Object 작업에서 ADO, OLE DB, Excel 및 ODBC 연결 관리자를 비롯한 네이티브 연결 관리자를 사용하는 경우 반환된 개체는 MSXML6입니다. IXMLDOMDocument.

태스크에서 ADO.NET 연결 관리자와 같은 관리되는 연결 관리자를 사용하는 경우 반환된 개체는 System.Xml.XmlDocument입니다.

변수는 SQL 실행 태스크 또는 패키지의 범위에서 정의할 수 있습니다. 변수에 패키지 범위가 있는 경우 결과 집합은 패키지 내의 다른 태스크 및 컨테이너에서 사용할 수 있으며 패키지 실행 또는 DTS 2000 패키지 실행 태스크에서 실행하는 모든 패키지에서 사용할 수 있습니다.

변수를 단일 행 결과 집합에 매핑하는 경우 SQL 문이 반환하는 문자열이 아닌 값은 다음 조건이 충족될 때 문자열로 변환됩니다.

  • TypeConversionMode 속성이 True로 설정된 경우. 속성 창에서 또는 SQL 실행 태스크 편집기를 사용하여 속성 값을 설정합니다.

  • 변환에서 데이터 잘림이 발생하지 않은 경우

SQL 실행 태스크의 변수에 결과 집합 매핑

이 섹션에서는 SQL 실행 태스크에서 결과 집합과 변수 간에 매핑을 만드는 방법을 설명합니다. 결과 집합을 변수에 매핑하면 패키지의 다른 요소에서 결과 집합을 사용할 수 있습니다. 예를 들어 스크립트 태스크의 스크립트는 변수를 읽은 다음 결과 집합의 값을 사용할 수 있으며 XML 원본은 변수에 저장된 결과 집합을 사용할 수 있습니다. 부모 패키지에서 결과 집합을 생성하는 경우 결과 집합을 부모 패키지의 변수에 매핑한 다음 부모 변수 값을 저장할 자식 패키지의 부모 패키지 변수 구성을 만들어 패키지 실행 태스크로 호출하는 자식 패키지에서 결과 집합을 사용할 수 있습니다.

  1. SSDT(SQL Server Data Tools)에서 원하는 패키지가 들어 있는 Integration Services 프로젝트를 엽니다.

  2. 솔루션 탐색기에서 패키지를 두 번 클릭하여 엽니다.

  3. 제어 흐름 탭을 클릭합니다.

  4. 패키지에 SQL 실행 태스크가 아직 포함되어 있지 않은 경우 패키지의 제어 흐름에 하나를 추가합니다. 자세한 내용은 제어 흐름에서 태스크 또는 컨테이너 추가 또는 삭제를 참조하세요.

  5. SQL 실행 태스크를 두 번 클릭합니다.

  6. SQL 실행 태스크 편집기 대화 상자의 일반 페이지에서 단일 행, 전체 결과 집합 또는 XML 결과 집합 유형을 선택합니다.

  7. 결과 집합을 클릭합니다.

  8. 결과 집합 매핑을 추가하려면 추가를 클릭합니다.

  9. 변수 이름 목록에서 변수를 선택하거나 새 변수를 만듭니다. 자세한 내용은 패키지에서 사용자 정의 변수의 추가, 삭제, 변경 범위를 참조하세요.

  10. 결과 이름 목록에서 선택적으로 결과 집합의 이름을 수정합니다.

    일반적으로 열 이름을 결과 집합 이름으로 사용하거나 열 목록의 열 서수 위치를 결과 집합으로 사용할 수 있습니다. 열 이름을 결과 집합 이름으로 사용하는 기능은 태스크가 사용하도록 구성된 공급자에 따라 달라집니다. 일부 공급자만 열 이름을 사용할 수 있습니다.

  11. 확인을 클릭합니다.

SQL 실행 태스크 문제 해결

SQL 실행 태스크가 외부 데이터 공급자에 대해 수행하는 호출을 기록할 수 있습니다. 이 로깅 기능을 사용하여 SQL 실행 태스크가 실행하는 SQL 명령의 문제를 해결할 수 있습니다. SQL 실행 태스크가 외부 데이터 공급자에 대해 수행하는 호출을 기록하려면 패키지 로깅을 사용하도록 설정하고 패키지 수준에서 Diagnostic 이벤트를 선택합니다. 자세한 내용은 패키지 실행을 위한 문제 해결 도구를 참조하세요.

SQL 명령 또는 저장 프로시저가 여러 결과 집합을 반환하는 경우도 있습니다. 이러한 결과 집합에는 SELECT 쿼리의 결과인 행 집합뿐만 아니라 RAISERROR 또는 PRINT 문의 오류로 인한 단일 값도 포함됩니다. 첫 번째 결과 집합 이후에 발생하는 결과 집합에서 태스크가 오류를 무시하는지 여부는 사용되는 연결 관리자 유형에 따라 달라집니다.

  • OLE DB 및 ADO 연결 관리자를 사용하는 경우 작업은 첫 번째 결과 집합 이후에 발생하는 결과 집합을 무시합니다. 따라서 이러한 연결 관리자를 사용하는 경우에는 오류가 첫 번째 결과 집합의 일부가 아니면 태스크에서 SQL 명령 또는 저장 프로시저가 반환하는 오류를 무시합니다.

  • ODBC 및 ADO.NET 연결 관리자를 사용하는 경우 작업은 첫 번째 결과 집합 이후에 발생하는 결과 집합을 무시하지 않습니다. 이러한 연결 관리자를 사용하면 첫 번째 결과 집합 이외의 결과 집합에 오류가 포함된 경우 오류가 발생하여 작업이 실패합니다.

사용자 지정 로그 항목

다음 표에서는 SQL 실행 태스크에 대한 사용자 지정 로그 항목을 설명합니다. 자세한 내용은 SSIS(Integration Services) 로깅을 참조하세요.

로그 항목 설명
ExecuteSQLExecutingQuery SQL 문의 실행 단계에 대한 정보를 제공합니다. 로그 항목은 태스크가 데이터베이스에 대한 연결을 획득할 때, 태스크가 SQL 문을 준비하기 시작할 때 및 SQL 문의 실행이 완료된 후에 기록됩니다. 준비 단계에 대한 로그 항목에는 태스크에서 사용하는 SQL 문이 포함됩니다.