Partilhar via

Implantar e executar pacotes SSIS usando procedimentos armazenados

  • Artigo

Quando configura um projeto do Integration Services para usar o modelo de implantação de projeto, você pode usar procedimentos armazenados no catálogo do SSIS para implantar o projeto e executar os pacotes. Para obter mais informações sobre o modelo de implantação de projeto, consulte Implantação de projetos e pacotes.

Você também pode usar o SQL Server Management Studio ou o SSDT (SQL Server Data Tools) para implantar o projeto e executar os pacotes. Para obter mais informações, consulte os tópicos na seção Consulte também.

DicaDica

Você pode facilmente gerar as instruções Transact-SQL para os procedimentos armazenados listados no procedimento abaixo, com exceção de catalog.deploy_project, fazendo o seguinte:

  1. No SQL Server Management Studio, expanda o nó Catálogos do Integration Services no Pesquisador de Objetos e navegue até o pacote que deseja executar.

  2. Clique com o botão direito do mouse no pacote e clique em Executar.

  3. Conforme necessário, defina valores de parâmetros, propriedades do gerenciador de conexões e opções na guia Avançado, como nível de log.

    Para obter mais informações sobre níveis de log, consulte Habilitar o log para a execução do pacote no servidor SSIS.

  4. Antes de clicar em OK para executar o pacote, clique em Script. O Transact-SQL é exibido em uma janela do Editor de Consultas no SQL Server Management Studio.

Para implantar e executar um pacote usando procedimentos armazenados

  1. Chame catalog.deploy_project (Banco de Dados SSISDB) para implantar o projeto do Integration Services que contém o pacote no servidor do Integration Services .

    Para recuperar o conteúdo binário do arquivo de implantação do projeto do Integration Services, para o parâmetro @project\_stream, use uma instrução SELECT com a função OPENROWSET e o provedor de conjunto de linhas BULK. O conjuntos de linhas BULK permite a você ler dados de um arquivo. O argumento SINGLE_BLOB do provedor de conjuntos de linhas BULK retorna o conteúdo do arquivo de dados como uma única linha, um conjunto de linhas de coluna única do tipo varbinary(max). Para obter mais informações, consulte OPENROWSET (Transact-SQL).

    No exemplo a seguir, o projeto SSISPackages_ProjectDeployment é implantado na pasta Pacotes SSIS no servidor do Integration Services . Os dados binários são lidos no arquivo do projeto (SSISPackage_ProjectDeployment.ispac) e armazenados no parâmetro @ProjectBinary do tipo varbinary(max). O valor do parâmetro @ProjectBinary é atribuído ao parâmetro @project\_stream.

    DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)

Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

  2. Chame catalog.create_execution (Banco de dados SSISDB) para criar uma instância de execução de pacote e, opcionalmente, chame catalog.set_execution_parameter_value (Banco de Dados SSISDB) para definir valores de parâmetro de tempo de execução.

    No exemplo a seguir, catalog.create_execution cria uma instância de execução para package.dtsx que está contida no projeto SSISPackage_ProjectDeployment. O projeto está localizado na pasta Pacotes SSIS. A execution_id retornada pelo procedimento armazenado é usado na chamada para catalog.set_execution_parameter_value. Esse segundo procedimento armazenado define o parâmetro LOGGING_LEVEL como 3 (log detalhado) e define um parâmetro de pacote denominado Parameter1 com um valor de 1.

    Para parâmetros como LOGGING_LEVEL, o valor de object_type é 50. Para parâmetros de pacote, o valor de object_type é 30.

    Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SSISPackage_ProjectDeployment', @use32bitruntime=False, @reference_id=1

Select @execution_id
DECLARE @var0 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

DECLARE @var1 int = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var1

GO

  3. Chame catalog.start_execution (Banco de dados SSISDB) para executar o pacote.

    No exemplo a seguir, uma chamada a catalog.start_execution é adicionada ao Transact-SQL para iniciar a execução do pacote. A execution_id retornada pelo procedimento armazenado catalog.create_execution é usada.

    Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SSISPackage_ProjectDeployment', @use32bitruntime=False, @reference_id=1

Select @execution_id
DECLARE @var0 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

DECLARE @var1 int = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Para implantar um projeto de servidor para servidor usando procedimentos armazenados

Você pode implantar um projeto de servidor para servidor usando os procedimentos armazenados catalog.get_project (Banco de Dados SSISDB) e catalog.deploy_project (Banco de Dados SSISDB).

Você precisa fazer o seguinte antes de executar os procedimentos armazenados:

  • Crie um objeto de servidor vinculado. Para obter mais informações, consulte Criar servidores vinculados (Mecanismo de Banco de Dados do SQL Server).

    Na página Opções do Servidor da caixa de diálogo Propriedades do Servidor Vinculado , defina RPC e RPC Out como True. Além disso, defina Habilitar Promoção de Transações Distribuídas para RPC como False.

  • Habilite parâmetros dinâmicos para o provedor selecionado para o servidor vinculado expandindo o nó Provedores sob Servidores Vinculados no Pesquisador de Objetos, clicando com o botão direito do mouse no provedor e clicando em Propriedades. Selecione Habilitar ao lado de Parâmetro dinâmico.

  • Confirme se o DTC (Distributed Transaction Coordinator) foi iniciado em ambos os servidores.

Chame catalog.get_project para retornar o binário do projeto e chame catalog.deploy_project. O valor retornado por catalog.get_project é inserido em uma variável de tabela do tipo varbinary(max). O servidor vinculado não pode retornar os resultados que são varbinary(max).

No exemplo a seguir, catalog.get_project retorna um binário para o projeto SSISPackages no servidor vinculado. O catalog.deploy_project implanta o projeto no servidor local, na pasta chamada DestFolder.

declare @resultsTableVar table (
project_binary varbinary(max)
)
 
INSERT @resultsTableVar (project_binary)
EXECUTE [MyLinkedServer].[SSISDB].[catalog].[get_project] 'Packages', 'SSISPackages'

declare @project_binary varbinary(max)
select @project_binary = project_binary from @resultsTableVar

exec [SSISDB].[CATALOG].[deploy_project] 'DestFolder', 'SSISPackages', @project_binary

Consulte também

Tarefas

Implantar projetos no Servidor do Integration Services

Executar um pacote nas Ferramentas de Dados do SQL Server

Executar um pacote no servidor SSIS usando o SQL Server Management Studio