Catálogo SSIS

Aplica-se a:SQL Server SSIS Integration Runtime em Azure Data Factory

O catálogo SSISDB é o ponto central para trabalhar com projetos do Integration Services (SSIS) que você implantou no servidor do Integration Services. Por exemplo, você define parâmetros de projeto e pacote, configura ambientes para especificar valores de tempo de execução para pacotes, executa e soluciona problemas de pacotes e gerencia operações de servidor do Integration Services.

Os objetos armazenados no catálogo SSISDB incluem projetos, pacotes, parâmetros, ambientes e histórico operacional.

Você inspeciona objetos, configurações e dados operacionais armazenados no catálogo do SSISDB , consultando as exibições no banco de dados do SSISDB . Você gerencia os objetos chamando procedimentos armazenados no banco de dados SSISDB ou usando a interface do usuário do catálogo SSISDB . Em muitos casos, a mesma tarefa pode ser executada na interface do usuário ou chamando um procedimento armazenado.

Para manter o banco de dados SSISDB , é recomendável aplicar políticas empresariais padrão para gerenciar bancos de dados de usuários. Para obter informações sobre como criar planos de manutenção, consulte Planos de manutenção.

O catálogo SSISDB e o banco de dados SSISDB oferecem suporte ao Windows PowerShell. Para obter mais informações sobre como usar o SQL Server com o Windows PowerShell, consulte SQL Server PowerShell. Para obter exemplos de como usar o Windows PowerShell para concluir tarefas como implantar um projeto, consulte a entrada de blog, SSIS e PowerShell no SQL Server 2012, em blogs.msdn.com.

Para obter mais informações sobre como exibir dados de operações, consulte Monitorar pacote em execução e outras operações.

Você acede ao catálogo SSISDB no SQL Server Management Studio conectando-se ao Mecanismo de Base de Dados do SQL Server e expandindo o nó Catálogos do Integration Services no Explorador de Objetos. Você acessa o banco de dados SSISDB no SQL Server Management Studio expandindo o nó Bancos de dados no Pesquisador de Objetos.

Funcionalidades e capacidades

• Identificadores de objeto de catálogo
• Configuração do catálogo
• Permissões
• Pastas
• Projetos e Pacotes
• Parâmetros
• Ambientes de servidor, variáveis de servidor e referências de ambiente de servidor
• Execuções e validações

Identificadores de objeto de catálogo

Ao criar um novo objeto no catálogo, atribua um nome ao objeto. O nome do objeto é um identificador. O SQL Server define regras para as quais os caracteres podem ser usados em um identificador. Os nomes dos objetos a seguir devem seguir regras de identificador.

• Pasta de Arquivos

• Projeto

• Meio Ambiente

• Parâmetro

• Variável de Ambiente

Pasta, Projeto, Ambiente

Considere as seguintes regras ao renomear uma pasta, projeto ou ambiente.

• Os caracteres inválidos incluem caracteres ASCII/Unicode de 1 a 31, aspas ("), menores que (<), maiores que (>), pipe (|), backspace (\b), null (\0) e tab (\t).

• O nome não deve conter espaços à esquerda ou à direita.

• @ não é permitido como o primeiro caractere, mas os caracteres subsequentes podem usar @.

• O comprimento do nome deve ser maior que 0 e menor ou igual a 128.

Parâmetro

Considere as seguintes regras ao nomear um parâmetro.

• O primeiro caractere do nome deve ser uma letra, conforme definido no Unicode Standard 2.0, ou um sublinhado (_).

• Os caracteres subsequentes podem ser letras ou números, conforme definido no Unicode Standard 2.0, ou um sublinhado (_).

Variável de Ambiente

Considere as seguintes regras ao nomear uma variável de ambiente.

• Os caracteres inválidos incluem caracteres ASCII/Unicode de 1 a 31, aspas ("), menores que (<), maiores que (>), pipe (|), backspace (\b), null (\0) e tab (\t).

• O nome não deve conter espaços à esquerda ou à direita.

• @ não é permitido como o primeiro caractere, mas os caracteres subsequentes podem usar @.

• O comprimento do nome deve ser maior que 0 e menor ou igual a 128.

• O primeiro caractere do nome deve ser uma letra, conforme definido no Unicode Standard 2.0, ou um sublinhado (_).

• Os caracteres subsequentes podem ser letras ou números, conforme definido no Unicode Standard 2.0, ou um sublinhado (_).

Configuração do catálogo

Você ajusta como o catálogo se comporta ajustando as propriedades do catálogo. As propriedades do catálogo definem como os dados confidenciais são criptografados e como as operações e os dados de controle de versão do projeto são mantidos. Para definir as propriedades do catálogo, use a caixa de diálogo Propriedades do catálogo ou chame o procedimento armazenado catalog.configure_catalog (Banco de Dados SSISDB ). Para exibir as propriedades, use a caixa de diálogo ou consulte catalog.catalog_properties (Base de Dados SSISDB). Para acessar a caixa de diálogo, clique com o botão direito do mouse em SSISDB no Pesquisador de Objetos.

Limpeza de versões de operações e projetos

Os dados de status de muitas das operações no catálogo são armazenados em tabelas internas do banco de dados. Por exemplo, o catálogo controla o status de execuções de pacotes e implantações de projetos. Para manter o tamanho dos dados de operações, o Trabalho de Manutenção do Servidor SSIS no SQL Server Management Studio é usado para remover dados antigos. Esse trabalho do SQL Server Agent é criado quando o Integration Services é instalado.

Você pode atualizar ou reimplantar um projeto do Integration Services implantando-o com o mesmo nome na mesma pasta do catálogo. Por padrão, cada vez que você reimplanta um projeto, o catálogo SSISDB mantém a versão anterior do projeto. Para manter o tamanho dos dados de operações, o Trabalho de Manutenção do Servidor SSIS é usado para remover versões antigas de projetos.

Para executar o Trabalho de Manutenção do Servidor SSIS, o SSIS cria o logon do SQL Server ##MS_SSISServerCleanupJobLogin##. Este login é apenas para uso interno do SSIS.

As seguintes propriedades do catálogo SSISDB definem como esse trabalho do SQL Server Agent se comporta. Você pode exibir e modificar as propriedades usando a caixa de diálogo Propriedades do catálogo ou usando catalog.catalog_properties (Banco de Dados SSISDB) e catalog.configure_catalog (Banco de Dados SSISDB).

Limpar os registos periodicamente
A etapa de trabalho para limpeza de operações é executada quando essa propriedade é definida como True.

Período de retenção (dias)
Define a idade máxima dos dados de operações permitidos (em dias). Os dados mais antigos são removidos.

O valor mínimo é de um dia. O valor máximo é limitado apenas pelo valor máximo dos dados int do SQL Server. Para obter informações sobre esse tipo de dados, consulte int, bigint, smallint e tinyint (Transact-SQL).

Remover periodicamente versões antigas
A etapa de trabalho para limpeza da versão do projeto é executada quando essa propriedade é definida como True.

Número máximo de versões por projeto
Define quantas versões de um projeto são armazenadas no catálogo. Versões mais antigas de projetos são removidas.

Algoritmo de encriptação

A propriedade Encryption Algorithm especifica o tipo de criptografia usado para criptografar valores de parâmetros confidenciais. Pode escolher entre os seguintes tipos de encriptação.

• AES_256 (padrão)

• AES_192

• AES_128

• DESX

• TRIPLE_DES_3KEY

• TRIPLE_DES

• DES

Quando você implanta um projeto do Integration Services no servidor do Integration Services, o catálogo criptografa automaticamente os dados do pacote e os valores confidenciais. O catálogo também descriptografa automaticamente os dados quando você os recupera. O catálogo SSISDB usa o nível de proteção "ServerStorage". Para obter mais informações, consulte Controle de Acesso para Dados Confidenciais em Pacotes.

Alterar o algoritmo de encriptação é uma operação demorada. Primeiro, o servidor tem que usar o algoritmo especificado anteriormente para descriptografar todos os valores de configuração. Em seguida, o servidor tem que usar o novo algoritmo para criptografar novamente os valores. Durante esse tempo, não pode haver outras operações do Integration Services no servidor. Assim, para permitir que as operações do Integration Services continuem ininterruptas, o algoritmo de criptografia é um valor somente leitura na caixa de diálogo do Management Studio.

Para alterar a configuração da propriedade Algoritmo de Criptografia , defina o banco de dados SSISDB para o modo de usuário único e chame o procedimento armazenado catalog.configure_catalog. Utilize ENCRYPTION_ALGORITHM para o argumento property_name. Para obter os valores de propriedade suportados, consulte catalog.catalog_properties (Banco de Dados SSISDB). Para obter mais informações sobre o procedimento armazenado, consulte catalog.configure_catalog (Banco de Dados SSISDB).

Para obter mais informações sobre o modo de usuário único, consulte Definir um banco de dados para o modo de usuário único. Para obter informações sobre criptografia e algoritmos de criptografia no SQL Server, consulte os tópicos na seção Criptografia do SQL Server.

Uma chave mestra de banco de dados é usada para a criptografia. A chave é criada quando você cria o catálogo.

A tabela a seguir lista os nomes de propriedades mostrados na caixa de diálogo Propriedades do catálogo e as propriedades correspondentes na exibição do banco de dados.

Permissões

Projetos, ambientes e pacotes estão contidos em pastas que são objetos protegíveis. Você pode conceder permissões a uma pasta, incluindo a permissão MANAGE_OBJECT_PERMISSIONS. MANAGE_OBJECT_PERMISSIONS permite delegar a administração do conteúdo da pasta a um usuário sem precisar conceder a associação de usuário à função ssis_admin. Você também pode conceder permissões para projetos, ambientes e operações. As operações incluem a inicialização do Integration Services, a implantação de projetos, a criação e o início de execuções, a validação de projetos e pacotes e a configuração do catálogo SSISDB .

Para obter mais informações sobre funções de banco de dados, consulte Database-Level funções.

O catálogo SSISDB usa um gatilho DDL, ddl_cleanup_object_permissions, para impor a integridade das informações de permissões para protegíveis SSIS. O gatilho é acionado quando uma entidade de banco de dados, como um usuário de banco de dados, uma função de banco de dados ou uma função de aplicativo de banco de dados, é removida do banco de dados SSISDB.

Se a entidade de segurança tiver concedido ou negado permissões a outras entidades de segurança, revogue as permissões dadas pelo concedente, antes que a entidade de segurança possa ser removida. Caso contrário, uma mensagem de erro é devolvida quando o sistema tenta remover o principal. O gatilho remove todos os registros de permissão em que a entidade de banco de dados é um beneficiário.

É recomendável que o gatilho não seja desabilitado porque garante que não haja registros de permissão órfãos depois que uma entidade de banco de dados for descartada do banco de dados SSISDB .

Gerenciando permissões

Você pode gerenciar permissões usando a interface do usuário do SQL Server Management Studio, os procedimentos armazenados e o Microsoft.SqlServer.Management.IntegrationServices namespace.

Para gerenciar permissões usando a interface do usuário do SQL Server Management Studio, use as seguintes caixas de diálogo:

• Para uma pasta, use a página Permissões da caixa de diálogo Propriedades da pasta.

• Para um projeto, use a página Permissões na caixa de diálogo Propriedades do projeto.

Para gerenciar permissões usando Transact-SQL, chame catalog.grant_permission (Banco de Dados SSISDB),catalog.deny_permission (Banco de Dados SSISDB) e catalog.revoke_permission (Banco de Dados SSISDB). Para visualizar permissões efetivas para o principal atual em todos os objetos, consultar catalog.effective_object_permissions (Banco de Dados SSISDB). Este tópico fornece descrições dos diferentes tipos de permissões. Para exibir permissões que foram atribuídas explicitamente ao usuário, consulte catalog.explicit_object_permissions (Banco de Dados SSISDB).

Pastas de Arquivos

Uma pasta contém um ou mais projetos e ambientes no catálogo do SSISDB . Você pode usar a exibição catalog.folders (Banco de Dados SSISDB) para acessar informações sobre pastas no catálogo. Você pode usar os seguintes procedimentos armazenados para gerenciar pastas:

• catalog.create_folder (Banco de Dados SSISDB)

• catalog.delete_folder (Banco de Dados SSISDB)

• catalog.rename_folder (Banco de Dados SSISDB)

• catalog.set_folder_description (Banco de Dados SSISDB)

Projetos e Pacotes

Cada projeto pode conter vários pacotes. Tanto os projetos quanto os pacotes podem conter parâmetros e referências a ambientes. Você pode acessar os parâmetros e referências de ambiente usando a caixa de diálogo Configurar.

Você pode executar outras tarefas do projeto chamando os seguintes procedimentos armazenados:

• catalog.delete_project (Banco de Dados SSISDB)

• catalog.deploy_project (Banco de Dados SSISDB)

• catalog.get_project (Banco de Dados SSISDB)

• catalog.move_project ((Banco de Dados SSISDB)

• catalog.restore_project (Banco de Dados SSISDB)

Essas exibições fornecem detalhes sobre pacotes, projetos e versões de projetos.

• catalog.projects (Banco de Dados SSISDB)

• catalog.packages (Banco de Dados SSISDB)

• catalog.object_versions (Banco de Dados SSISDB)

Parâmetros

Você usa parâmetros para atribuir valores às propriedades do pacote no momento da execução do pacote. Para definir o valor de um parâmetro de pacote ou projeto e limpar o valor, chame catalog.set_object_parameter_value (Banco de Dados SSISDB) e catalog.clear_object_parameter_value (Banco de Dados SSISDB). Para definir o valor de um parâmetro para uma instância de execução, chame catalog.set_execution_parameter_value (Banco de Dados SSISDB). Você pode recuperar valores de parâmetros padrão chamando catalog.get_parameter_values (Banco de Dados SSISDB).

Essas exibições mostram os parâmetros para todos os pacotes e projetos, e os valores de parâmetro que são usados para uma instância de execução.

• catalog.object_parameters (Banco de Dados SSISDB)

• catalog.execution_parameter_values (Banco de Dados SSISDB)

Ambientes de servidor, variáveis de servidor e referências de ambiente de servidor

Os ambientes de servidor contêm variáveis de servidor. Os valores das variáveis podem ser usados quando um pacote é executado ou validado no servidor do Integration Services.

Os procedimentos armazenados a seguir permitem que você execute muitas outras tarefas de gerenciamento para ambientes e variáveis.

• catalog.create_environment (Banco de Dados SSISDB)

• catalog.delete_environment (Banco de Dados SSISDB)

• catalog.move_environment (Banco de Dados SSISDB)

• catalog.rename_environment (Banco de Dados SSISDB)

• catalog.set_environment_property (Banco de Dados SSISDB)

• catalog.create_environment_variable (Banco de Dados SSISDB)

• catalog.delete_environment_variable (Banco de Dados SSISDB)

• catalog.set_environment_variable_property (Banco de Dados SSISDB)

• catalog.set_environment_variable_value (Banco de Dados SSISDB)

Chamando o procedimento armazenado catalog.set_environment_variable_protection (Banco de Dados SSISDB), você pode definir o bit de sensibilidade para uma variável.

Para usar o valor de uma variável de servidor, especifique a referência entre o projeto e o ambiente de servidor. Você pode usar os procedimentos armazenados a seguir para criar e excluir referências. Você também pode indicar se o ambiente pode estar localizado na mesma pasta que o projeto ou em uma pasta diferente.

• catalog.create_environment_reference (Banco de Dados SSISDB)

• catalog.delete_environment_reference (Banco de Dados SSISDB)

• catalog.set_environment_reference_type (Banco de Dados SSISDB)

Para obter mais detalhes sobre ambientes e variáveis, consulte essas exibições.

• catalog.environments (Banco de Dados SSISDB)

• catalog.environment_variables (Banco de Dados SSISDB)

• catalog.environment_references (Banco de Dados SSISDB)

Execuções e validações

Uma execução é uma instância de uma execução de pacote. Chame catalog.create_execution (Banco de Dados SSISDB) e catalog.start_execution (Banco de Dados SSISDB) para criar e iniciar uma execução. Para interromper uma execução ou uma validação de pacote/projeto, chame catalog.stop_operation (Banco de Dados SSISDB).

Para fazer com que um pacote em execução pause e crie um arquivo de despejo, chame o procedimento armazenado catalog.create_execution_dump. Um arquivo de despejo fornece informações sobre a execução de um pacote que pode ajudá-lo a solucionar problemas de execução. Para obter mais informações sobre como gerar e configurar arquivos de despejo, consulte Gerando arquivos de despejo para execução de pacote.

Para obter detalhes sobre execuções, validações, mensagens que são registradas durante as operações e informações contextuais relacionadas a erros, consulte essas exibições.

• catalog.executions (Banco de Dados SSISDB)

• catalog.operations (banco de dados SSISDB)

• catalog.operation_messages (Banco de Dados SSISDB)

• catalog.extended_operation_info (Banco de Dados SSISDB)

• catalog.event_messages

• catálogo.contexto_da_mensagem_do_evento

Você pode validar projetos e pacotes chamando as procedures armazenadas catalog.validate_project (base de dados SSISDB) e catalog.validate_package (base de dados SSISDB). A exibição catalog.validations (Banco de Dados SSISDB) fornece detalhes sobre validações, como as referências de ambiente de servidor consideradas na validação, se é uma validação de dependência ou uma validação completa, e se o tempo de execução de 32 bits ou o tempo de execução de 64 bits é usado para executar o pacote.

Criar o catálogo SSIS

Depois de criar e testar pacotes no SQL Server Data Tools, você pode implantar os projetos que contêm os pacotes em um servidor Integration Services. Antes de implantar os projetos no servidor do Integration Services, o servidor deve conter o catálogo do SSISDB . O programa de instalação do SQL Server 2012 (11.x) não cria automaticamente o catálogo; Você precisa criar manualmente o catálogo usando as instruções a seguir.

Você pode criar o catálogo SSISDB no SQL Server Management Studio. Você também cria o catálogo programaticamente usando o Windows PowerShell.

Para criar o catálogo SSISDB no SQL Server Management Studio

1. Abra o SQL Server Management Studio.

2. Conecte-se ao Mecanismo de Banco de Dados do SQL Server.

3. No Explorador de Objetos, expanda o nó do servidor, clique com o botão direito do mouse no nó Catálogos do Integration Services e clique em Criar Catálogo.

4. Clique em Ativar integração CLR.

   O catálogo usa procedimentos armazenados CLR.

5. Clique em Habilitar a execução automática do procedimento armazenado do Integration Services na inicialização do SQL Server para permitir que o procedimento armazenado catalog.startup seja executado sempre que a instância do servidor SSIS for reiniciada.

   O procedimento armazenado realiza a manutenção do estado das operações no âmbito do catálogo SSISDB. Ele corrige o status de todos os pacotes que estavam em execução se a instância do servidor SSIS ficar inativa.

6. Introduza uma palavra-passe e, em seguida, clique em Ok.

   A senha protege a chave mestra do banco de dados usada para criptografar os dados do catálogo. Guarde a palavra-passe num local seguro. É recomendável que você também faça backup da chave mestra do banco de dados. Para obter mais informações, consulte Fazer backup de uma chave mestra de banco de dados.

Para criar o catálogo SSISDB programaticamente

1. Execute o seguinte script do PowerShell:

   # Load the IntegrationServices Assembly  
   [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")  
   
   # Store the IntegrationServices Assembly namespace to avoid typing it every time  
   $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"  
   
   Write-Host "Connecting to server ..."  
   
   # Create a connection to the server  
   $sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"  
   $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString  
   
   # Create the Integration Services object  
   $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection  
   
   # Provision a new SSIS Catalog  
   $catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")  
   $catalog.Create()  
   
   

   Para obter mais exemplos de como usar o Windows PowerShell e o Microsoft.SqlServer.Management.IntegrationServices namespace, consulte a entrada de blog, SSIS e PowerShell no SQL Server 2012, em blogs.msdn.com. Para obter uma visão geral do namespace e exemplos de código, consulte a entrada de blog, A Glimpse of the SSIS Catalog Managed Object Model, em blogs.msdn.com.

Caixa de diálogo Propriedades do catálogo

Use a caixa de diálogo Propriedades do catálogo para configurar o catálogo SSISDB. As propriedades do catálogo definem como os dados confidenciais são criptografados, como as operações e os dados de versionamento de projetos são retidos e quando as operações de validação atingem o tempo limite. O catálogo SSISDB é um ponto central de armazenamento e administração para projetos, pacotes, parâmetros e ambientes do Integration Services.

Você também pode ver as propriedades do catálogo na catalog.catalog_properties visão e definir as propriedades usando o procedimento armazenado catalog.configure_catalog. Para obter mais informações, consulte catalog.catalog_properties (Banco de Dados SSISDB) e catalog.configure_catalog (Banco de Dados SSISDB).

O que queres fazer?

• Abrir a caixa de diálogo Propriedades do catálogo

• Configurar as opções

Abrir a caixa de diálogo Propriedades do catálogo

1. Abra o SQL Server Management Studio.

2. Conecte o Mecanismo de Banco de Dados do Microsoft SQL Server.

3. No Pesquisador de Objetos, expanda o nó Integration Services , clique com o botão direito do mouse em SSISDB e clique em Propriedades.

Configurar as opções

Opções

A tabela a seguir descreve certas propriedades na caixa de diálogo e as propriedades correspondentes na vista catalog.catalog_properties.

Fazer backup, restaurar e mover o catálogo do SSIS

Aplica-se ao: SQL Server 2016 (13.x) e versões posteriores

O SQL Server 2019 Integration Services (SSIS) inclui o banco de dados SSISDB. Você consulta exibições no banco de dados SSISDB para inspecionar objetos, configurações e dados operacionais armazenados no catálogo SSISDB . Este tópico fornece instruções para fazer backup e restaurar o banco de dados.

O catálogo do SSISDB armazena os pacotes que você implantou no servidor do Integration Services. Para obter mais informações sobre o catálogo, consulte Catálogo SSIS.

Para fazer backup do banco de dados SSIS

1. Abra o SQL Server Management Studio e conecte-se a uma instância do SQL Server.

2. Faça backup da chave mestra para o banco de dados SSISDB usando a instrução BACKUP MASTER KEY Transact-SQL. A chave é armazenada em um arquivo que você especificar. Use uma senha para criptografar a chave mestra no arquivo.

   Para obter mais informações sobre a declaração, veja BACKUP MASTER KEY (Transact-SQL).

   No exemplo a seguir, a chave mestra é exportada para o c:\temp directory\RCTestInstKey arquivo. A LS2Setup! senha é usada para criptografar a chave mestra.

   backup master key to file = 'c:\temp\RCTestInstKey'  
          encryption by password = 'LS2Setup!'  
   
   

3. Faça backup do banco de dados SSISDB usando a caixa de diálogo Banco de Dados de Backup no SQL Server Management Studio. Para obter mais informações, consulte Como fazer backup de um banco de dados (SQL Server Management Studio).

4. Gere o script CREATE LOGIN para ##MS_SSISServerCleanupJobLogin##, fazendo as seguintes coisas. Para obter mais informações, consulte CREATE LOGIN (Transact-SQL).

   1. No Explorador de Objetos no SQL Server Management Studio, expanda o nó Segurança e, em seguida, expanda o nó Logins.

   2. Clique com o botão direito do rato em ##MS_SSISServerCleanupJobLogin## e, em seguida, clique em Login de Script como>CREATE To>New Query Editor Window.

5. Se você estiver restaurando o banco de dados SSISDB para uma instância do SQL Server onde o catálogo SSISDB nunca foi criado, gere o script CREATE PROCEDURE para sp_ssis_startup, fazendo o seguinte. Para obter mais informações, consulte CREATE PROCEDURE (Transact-SQL).

   1. No Pesquisador de Objetos, expanda o nó Bancos de Dados e, em seguida, expanda o nó mestre>Procedimentos Armazenados>.

   2. Clique com o botão direito do rato em dbo.sp_ssis_startup e, em seguida, clique em Procedimento armazenado de script como>CREATE para>nova janela do Editor de Consultas.

6. Confirme se o SQL Server Agent foi iniciado

7. Se você estiver restaurando o banco de dados SSISDB para uma instância do SQL Server onde o catálogo SSISDB nunca foi criado, gere um script para o Trabalho de Manutenção do Servidor SSIS fazendo as seguintes coisas. O script é criado no SQL Server Agent automaticamente quando o catálogo SSISDB é criado. O trabalho ajuda a limpar logs de operação de limpeza fora da janela de retenção e remover versões mais antigas de projetos.

   1. No Pesquisador de Objetos, expanda o nó SQL Server Agent e, em seguida, expanda o nó Trabalhos .

   2. Clique com o botão direito do mouse em Trabalho de Manutenção do Servidor SSIS e clique em Trabalho de Script como>CRIAR para>Nova Janela do Editor de Consultas.

Para restaurar o banco de dados SSIS

1. Se você estiver restaurando o banco de dados SSISDB para uma instância do SQL Server onde o catálogo SSISDB nunca foi criado, habilite o common language runtime (clr) executando o sp_configure procedimento armazenado. Para obter mais informações, consulte sp_configure (Transact-SQL) e clr enabled Option.

   use master   
          sp_configure 'clr enabled', 1  
          reconfigure  
   
   

2. Se você estiver restaurando o banco de dados SSISDB para uma instância do SQL Server onde o catálogo SSISDB nunca foi criado, crie a chave assimétrica e o logon a partir da chave assimétrica e conceda permissão UNSAFE para o logon.

   Create Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey  
          FROM Executable File = 'C:\Program Files\Microsoft SQL Server\YourSQLServerDefaultCompatibilityLevel\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'  
   

   Você pode encontrar o valor para YourSQLServerDefaultCompatibilityLevel em uma lista de níveis de compatibilidade padrão do SQL Server.

   Os procedimentos armazenados CLR do Integration Services exigem que permissões UNSAFE sejam concedidas ao logon porque o logon requer acesso adicional a recursos restritos, como a API do Microsoft Win32. Para obter mais informações sobre a permissão de código UNSAFE, consulte Criando um assembly.

   Create Login ##MS_SQLEnableSystemAssemblyLoadingUser## FROM Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey   
   Grant Unsafe Assembly to ##MS_SQLEnableSystemAssemblyLoadingUser##    
   

3. Restaure o banco de dados SSISDB a partir do backup usando a caixa de diálogo Restaurar Banco de Dados no SQL Server Management Studio. Para obter mais informações, consulte os seguintes tópicos:

   • Restaurar banco de dados (página Geral)

   • Restaurar banco de dados (página Arquivos)

   • Restaurar banco de dados (página Opções)

4. Execute os scripts que criou em Para fazer backup do banco de dados SSIS para ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup e Trabalho de Manutenção do Servidor SSIS. Confirme se o SQL Server Agent foi iniciado.

5. Execute a instrução a seguir para definir o procedimento sp_ssis_startup para autoexecução. Para obter mais informações, consulte sp_procoption (Transact-SQL).

   EXEC sp_procoption N'sp_ssis_startup','startup','on'  
   

6. Mapeie o usuário do SSISDB ##MS_SSISServerCleanupJobUser## (banco de dados SSISDB) para ##MS_SSISServerCleanupJobLogin##, usando a caixa de diálogo Propriedades de Logon no SQL Server Management Studio.

7. Restaure a chave mestra usando um dos seguintes métodos. Para obter mais informações sobre criptografia, consulte Hierarquia de criptografia.

   • Método 1

     Use esse método se você já tiver realizado um backup da chave mestra do banco de dados e tiver a senha usada para criptografar a chave mestra.

            Restore master key from file = 'c:\temp\RCTestInstKey'  
            Decryption by password = 'LS2Setup!' -- 'Password used to encrypt the master key during SSISDB backup'  
            Encryption by password = 'LS3Setup!' -- 'New Password'  
            Force  
     
     

     Observação

     Confirme se a conta de serviço do SQL Server tem permissões para ler o arquivo de chave de backup.

     Observação

     Você verá a seguinte mensagem de aviso exibida no SQL Server Management Studio se a chave mestra do banco de dados ainda não tiver sido criptografada pela chave mestra de serviço. Ignore a mensagem de aviso.

     A chave mestra atual não pode ser desencriptada. O erro foi ignorado porque a opção FORCE foi especificada.

     O argumento FORCE especifica que o processo de restauração deve continuar mesmo se a chave mestra do banco de dados atual não estiver aberta. Para o catálogo SSISDB, como a chave mestra do banco de dados não foi aberta na instância em que você está restaurando o banco de dados, você verá esta mensagem.

   • Método 2

     Use esse método se você tiver a senha original que foi usada para criar o SSISDB.

     open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'  
            Alter Master Key Add encryption by Service Master Key  
     

8. Determine se o esquema de catálogo SSISDB e os binários do Integration Services (ISServerExec e assembly SQLCLR) são compatíveis, executando catalog.check_schema_version.

9. Para confirmar se o banco de dados SSISDB foi restaurado com êxito, execute operações no catálogo SSISDB, como executar pacotes que foram implantados no servidor Integration Services. Para obter mais informações, consulte Executar pacotes do Integration Services (SSIS).

Para mover o banco de dados SSIS

• Siga as instruções para mover bancos de dados de usuários. Para obter mais informações, consulte Mover bancos de dados de usuários.

  Certifique-se de fazer backup da chave mestra para o banco de dados SSISDB e proteger o arquivo de backup. Para obter mais informações, consulte Para fazer backup do banco de dados SSIS.

  Verifique se os objetos relevantes do Integration Services (SSIS) são criados na nova instância do SQL Server em que o catálogo do SSISDB ainda não foi criado.

Atualizar o catálogo SSIS (SSISDB)

Execute o Assistente de Atualização do SSISDB para atualizar o banco de dados do Catálogo SSIS, SSISDB, quando o banco de dados for mais antigo que a versão atual da instância do SQL Server. O banco de dados pode ser mais antigo quando uma das seguintes condições for verdadeira.

• Você restaurou o banco de dados de uma versão mais antiga do SQL Server.

• Você não removeu o banco de dados de um grupo de disponibilidade Always On antes de atualizar a instância do SQL Server. Essa condição impede a atualização automática do banco de dados. Para obter mais informações, consulte Atualizando o SSISDB em um grupo de disponibilidade.

O assistente só pode atualizar o banco de dados em uma instância do servidor local.

Atualize o Catálogo SSIS (SSISDB) executando o Assistente de Atualização do SSISDB

1. Faça backup do banco de dados do Catálogo SSIS, SSISDB.

2. No SQL Server Management Studio, expanda o servidor local e, em seguida, expanda os Catálogos do Integration Services.

3. Clique com o botão direito do mouse em SSISDB e selecione Atualização de Banco de Dados para iniciar o Assistente de Atualização do SSISDB. Ou inicie o Assistente de Atualização do SSISDB executando C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe com permissões elevadas no servidor local.

   

4. Na página Selecionar Instância , selecione uma instância do SQL Server no servidor local.

   Importante

   O assistente só pode atualizar o banco de dados em uma instância do servidor local.

   Marque a caixa de seleção para indicar que você fez backup do banco de dados SSISDB antes de executar o assistente.

   

5. Selecione Atualizar para atualizar o banco de dados do Catálogo SSIS.

6. Na página Resultado , revise os resultados.

   

Sempre ativo no Catálogo SSIS (SSISDB)

O recurso "Grupos de Disponibilidade Always On" é uma solução de alta disponibilidade e recuperação de desastres que fornece uma alternativa ao nível empresarial ao espelhamento de banco de dados. Um grupo de disponibilidade oferece suporte a um ambiente de failover para um conjunto discreto de bancos de dados de usuários, conhecidos como bancos de dados de disponibilidade, que fazem failover juntos. Para obter mais informações, consulte Grupos de disponibilidade Always On.

Para fornecer a alta disponibilidade para o catálogo SSIS (SSISDB) e seu conteúdo (projetos, pacotes, logs de execução, etc.), você pode adicionar o banco de dados SSISDB (exatamente o mesmo que qualquer outro banco de dados de usuário) a um Grupo de Disponibilidade Always On. Quando ocorre um failover, um dos nós secundários se torna automaticamente o novo nó primário.

Nesta secção:

1. Pré-requisitos

2. Configurar o suporte SSIS para Always On

3. Atualização do SSISDB num grupo de disponibilidade

Pré-requisitos

Execute as seguintes etapas de pré-requisito antes de habilitar o suporte Always On para o banco de dados SSISDB.

1. Configure um cluster de tolerância a falhas do Windows. Consulte a postagem do blog Instalando o recurso de cluster de failover e as ferramentas do Windows Server 2012 para obter instruções. Instale o recurso e as ferramentas em todos os nós do cluster.

2. Instale o SQL Server 2016 com o recurso Integration Services (SSIS) em cada nó do cluster.

3. Habilite os Grupos de Disponibilidade Always On para cada instância do SQL Server. Consulte Ativar grupos de disponibilidade Always On para obter detalhes.

Configurar o suporte SSIS para Always On

• Etapa 1: Criar catálogo do Integration Services

• Etapa 2: Adicionar SSISDB a um grupo de disponibilidade Always On

• Etapa 3: Habilitar o suporte SSIS para Always On

Etapa 1: Criar catálogo do Integration Services

1. Inicie o SQL Server Management Studio e conecte-se a uma instância do SQL Server no cluster que você deseja definir como o nó primário do grupo de alta disponibilidade Always On para SSISDB.

2. No Explorador de Objetos, expanda o nó do servidor, clique com o botão direito do mouse no nó Catálogos do Integration Services e clique em Criar Catálogo.

3. Clique em Ativar integração CLR. O catálogo usa procedimentos armazenados CLR.

4. Clique em Habilitar a execução automática do procedimento armazenado do Integration Services na inicialização do SQL Server para permitir que o procedimento armazenado catalog.startup seja executado sempre que a instância do servidor SSIS for reiniciada. O procedimento armazenado realiza a manutenção do estado das operações no âmbito do catálogo SSISDB. Ele corrige o status de todos os pacotes que estavam em execução se e quando a instância do servidor SSIS ficar inativa.

5. Introduza uma palavra-passe e, em seguida, clique em Ok. A senha protege a chave mestra do banco de dados usada para criptografar os dados do catálogo. Guarde a palavra-passe num local seguro. É recomendável que você também faça backup da chave mestra do banco de dados. Para obter mais informações, consulte Fazer backup de uma chave mestra de banco de dados.

Etapa 2: Adicionar SSISDB a um grupo de disponibilidade Always On

Adicionar o banco de dados SSISDB a um Grupo de Disponibilidade Always On é quase o mesmo que adicionar qualquer outro banco de dados de usuário a um grupo de disponibilidade. Consulte Usar o Assistente de Grupo de Disponibilidade.

Insira a palavra-passe que especificou ao criar o Catálogo SSIS na página Selecionar Bancos de Dados do assistente para Novo Grupo de Disponibilidade .

Etapa 3: Habilitar o suporte SSIS para Always On

Depois de criar o Integration Service Catalog, clique com o botão direito do rato no nó Integration Service Catalogs e clique em Enable Always On Support. Você deverá ver a seguinte caixa de diálogo Enable Support for Always On. Se este item de menu estiver desativado, confirme se você tem todos os pré-requisitos instalados e clique em Atualizar.

As réplicas secundárias recém-adicionadas do grupo de disponibilidade Always On são mostradas na tabela. Clique no botão Conectar... para cada réplica na lista e insira as credenciais de autenticação para se conectar à réplica. A conta de usuário deve ser membro do grupo sysadmin em cada réplica para habilitar o suporte SSIS para Always On. Depois de se conectar com êxito a cada réplica, clique em OK para habilitar o suporte SSIS para Always On.

Se a opção Ativar suporte Always On no menu de contexto parecer estar desativada depois de concluir os outros pré-requisitos, tente o seguinte:

1. Atualize o menu de contexto clicando na opção Atualizar .
2. Verifique se você está se conectando ao nó principal. Você precisa ativar o suporte "Always On" no nó principal.
3. Verifique se a versão do SQL Server é 13.0 ou superior. O SSIS oferece suporte a Always On somente no SQL Server 2016 e versões posteriores.

Atualizando o SSISDB em um grupo de disponibilidade

Se você estiver atualizando o SQL Server de uma versão anterior e o SSISDB estiver em um grupo de disponibilidade Always On, sua atualização poderá ser bloqueada pela regra "SSISDB na verificação do Grupo de Disponibilidade Always On". Esse bloqueio ocorre porque a atualização é executada no modo de usuário único, enquanto um banco de dados de disponibilidade deve ser um banco de dados multiusuário. Portanto, durante a atualização ou aplicação de patches, todos os bancos de dados de disponibilidade, incluindo o SSISDB, são colocados offline e não são atualizados ou corrigidos. Para permitir que a atualização continue, primeiro remova o SSISDB do grupo de disponibilidade, atualize ou corrija cada nó e adicione o SSISDB de volta ao grupo de disponibilidade.

Se você estiver bloqueado pela regra "SSISDB na verificação do grupo de disponibilidade Always On", siga estas etapas para atualizar o SQL Server.

1. Remova o banco de dados SSISDB do grupo de disponibilidade. Para obter mais informações, consulte Remover um banco de dados secundário de um grupo de disponibilidade (SQL Server) e Remover um banco de dados primário de um grupo de disponibilidade (SQL Server).

2. Clique em Executar novamente no assistente de atualização. A regra "verificação do SSISDB no grupo de disponibilidade Always On" é aprovada.

3. Clique em Avançar para continuar a atualização.

4. Depois de ter atualizado todos os nós, adicione novamente o banco de dados SSISDB ao grupo de disponibilidade Always On. Para obter mais informações, consulte Adicionar um banco de dados a um grupo de disponibilidade (SQL Server).

Se você não estiver bloqueado quando atualizar o SQL Server e o SSISDB estiver em um grupo de disponibilidade Always On, atualize o SSISDB separadamente depois de atualizar o mecanismo de banco de dados do SQL Server. Use o Assistente de Atualização do SSIS para atualizar o SSISDB conforme descrito no procedimento a seguir.

1. Mova o banco de dados SSISDB para fora do grupo de disponibilidade ou exclua o grupo de disponibilidade se o SSISDB for o único banco de dados no grupo de disponibilidade. Inicie o SQL Server Management Studio no nó primário do grupo de disponibilidade para executar essa tarefa.

2. Remova o banco de dados SSISDB de todos os nós de réplica.

3. Atualize o banco de dados SSISDB no nó primário. No Explorador de Objetos no SQL Server Management Studio, expanda Catálogos de Serviços de Integração, clique com o botão direito em SSISDB e, em seguida, selecione Atualização de Base de Dados. Siga as instruções no Assistente de Atualização do SSISDB para atualizar o banco de dados. Inicie o Assistente de Atualização SSIDB localmente no nó primário.

4. Siga as instruções na Etapa 2: Adicionar o SSISDB a um Grupo de Disponibilidade Always On para adicionar o SSISDB de volta a um grupo de disponibilidade.

5. Siga as instruções na Etapa 3: Habilitar o suporte SSIS para Always On.

Catálogo SSISDB e delegação em cenários de salto duplo

Por padrão, a invocação remota de pacotes SSIS armazenados no catálogo SSISDB não oferece suporte à delegação de credenciais, às vezes chamada de salto duplo.

Imagine um cenário em que um usuário efetua logon na máquina cliente A e inicia o SQL Server Management Studio (SSMS). De dentro do SSMS, o usuário se conecta a um SQL Server hospedado na máquina B, que tem o catálogo do SSISDB. O pacote SSIS é armazenado neste catálogo SSISDB e o pacote, por sua vez, se conecta a um serviço do SQL Server que está sendo executado na máquina C (o pacote também pode estar acessando quaisquer outros serviços). Quando o usuário invoca a execução do pacote SSIS da máquina A, o SSMS primeiro passa com êxito as credenciais do usuário da máquina A para a máquina B (onde o processo de tempo de execução do SSIS está executando o pacote). O processo de execução em runtime do SSIS (ISServerExec.exe) agora precisa delegar as credenciais de utilizador da máquina B para a máquina C para que a execução seja concluída com êxito. No entanto, a delegação de credenciais não está habilitada por padrão.

Um utilizador pode habilitar a delegação de credenciais concedendo a permissão Confiar neste utilizador para delegação a qualquer serviço (somente Kerberos) à conta de serviço do SQL Server (na máquina B), iniciando ISServerExec.exe como processo filho. Esse processo é conhecido como configurar a delegação sem restrições ou a delegação aberta para uma conta de serviço do SQL Server. Antes de conceder esse direito, considere se ele atende aos requisitos de segurança da sua organização.

O SSISDB não oferece suporte à delegação restrita. Em um ambiente de salto duplo, se a conta de serviço do SQL Server que hospeda o catálogo SSISDB (máquina B em nosso exemplo) estiver configurada para delegação restrita, ISServerExec.exe não poderá delegar as credenciais à terceira máquina (máquina C). Isso é aplicável a cenários nos quais o Windows Credential Guard está habilitado, o que exige obrigatoriamente a configuração de delegação restrita.

Conteúdo relacionado

• Entrada de blog, SSIS e PowerShell no SQL Server 2012, em blogs.msdn.com.

• Entrada de blog, Dicas de controle de acesso do catálogo SSIS, no blogs.msdn.com.

• Entrada de blog, A Glimpse of the SSIS Catalog Managed Object Model, em blogs.msdn.com.