Compartilhar via

O pacote do SSIS não é executado quando chamado de uma etapa de trabalho do SQL Server Agent

  • Artigo

Este artigo ajuda você a resolver o problema que ocorre quando você chama um pacote SSIS de uma etapa de trabalho do SQL Server Agent.

Versão original do produto: SQL Server
Número original do KB: 918760

Sintomas

Quando você chama um pacote do Microsoft SQL Server Integration Services (SSIS) de uma etapa de trabalho do SQL Server Agent, o pacote do SSIS não é executado. No entanto, se você não modificar o pacote SSIS, ele será executado com êxito fora do SQL Server Agent.

Solução

Para resolver esse problema, use um dos métodos a seguir. O método mais apropriado depende do ambiente e do motivo pelo qual o pacote falhou. Os motivos pelos quais o pacote pode ter falhado são os seguintes:

  • A conta de usuário usada para executar o pacote no SQL Server Agent é diferente do autor do pacote original.
  • A conta de usuário não tem as permissões necessárias para fazer conexões ou acessar recursos fora do pacote SSIS.

O pacote pode não ser executado nos seguintes cenários:

  • O usuário atual não pode descriptografar segredos do pacote. Esse cenário pode ocorrer se a conta atual ou a conta de execução for diferente do autor do pacote original e a configuração da propriedade ProtectionLevel do pacote não permitir que o usuário atual descriptografe segredos no pacote.
  • Uma conexão do SQL Server que usa segurança integrada falha porque o usuário atual não tem as permissões necessárias.
  • O acesso ao arquivo falha porque o usuário atual não tem as permissões necessárias para gravar no compartilhamento de arquivos que o gerenciador de conexões acessa. Por exemplo, esse cenário pode ocorrer com provedores de log de texto que não usam um logon e uma senha. Esse cenário também pode ocorrer com qualquer tarefa que dependa do gerenciador de conexões de arquivos, como uma tarefa do sistema de arquivos SSIS.
  • Uma configuração de pacote SSIS baseada no Registro usa as chaves do HKEY_CURRENT_USER Registro. As HKEY_CURRENT_USER chaves do Registro são específicas do usuário.
  • Uma tarefa ou um gerenciador de conexões requer que a conta de usuário atual tenha permissões corretas.

Para resolver o problema, use os seguintes métodos:

  • Método 1: use uma conta proxy do SQL Server Agent. Crie uma conta proxy do SQL Server Agent. Essa conta proxy deve usar uma credencial que permita que o SQL Server Agent execute o trabalho como a conta que criou o pacote ou como uma conta que tenha as permissões necessárias.

    Esse método funciona para descriptografar segredos e satisfaz os principais requisitos do usuário. No entanto, esse método pode ter sucesso limitado porque as chaves de usuário do pacote SSIS envolvem o usuário atual e o computador atual. Portanto, se você mover o pacote para outro computador, esse método ainda poderá falhar, mesmo que a etapa de trabalho use a conta proxy correta.

  • Método 2: Defina a propriedade Pacote ProtectionLevel SSIS como ServerStorage. Altere a propriedade ProtectionLevel do Pacote SSIS para ServerStorage. Essa configuração armazena o pacote em um banco de dados do SQL Server e permite o controle de acesso por meio de funções de banco de dados do SQL Server.

  • Método 3: Defina a propriedade Pacote ProtectionLevel SSIS como EncryptSensitiveWithPassword. Altere a propriedade Pacote ProtectionLevel SSIS para EncryptSensitiveWithPassword. Essa configuração usa uma senha para criptografia. Em seguida, você pode modificar a linha de comando da etapa de trabalho do SQL Server Agent para incluir essa senha.

  • Método 4: use os arquivos de configuração do pacote SSIS. Use os arquivos de configuração do pacote SSIS para armazenar informações confidenciais e, em seguida, armazene esses arquivos de configuração em uma pasta protegida. Em seguida, você pode alterar a ProtectionLevel propriedade para DontSaveSensitive que o pacote não seja criptografado e não tente salvar segredos no pacote. Quando você executa o pacote SSIS, as informações necessárias são carregadas do arquivo de configuração. Certifique-se de que os arquivos de configuração estejam adequadamente protegidos se contiverem informações confidenciais.

  • Método 5: Crie um modelo de pacote. Para uma resolução de longo prazo, crie um modelo de pacote que use um nível de proteção diferente da configuração padrão. Esse problema não ocorrerá em pacotes futuros.

Etapas para reproduzir o problema

  1. Faça login como um usuário que não faz parte do grupo SQLServerSQLAgentUser. Por exemplo, você pode criar um usuário local.
  2. Crie um pacote SSIS e, em seguida, adicione uma tarefa ExecuteSQL. Use um gerenciador de conexões OLE DB para o arquivo msdb local usando a seguinte cadeia de caracteres: 'Windows Authentication' -SQLSourceType: "Direct Input" -SQLStatement: "sp_who".
  3. Execute o pacote para garantir que ele seja executado com êxito.
  4. A propriedade ProtectionLevel está definida como EncryptSensitiveWithPassword.
  5. Crie um trabalho do SQL Server Agent e uma etapa de trabalho. Na lista Executar como, clique em Serviço do SQL Server Agent para executar a etapa de trabalho. O texto no Histórico de Trabalhos do SQL Server Agent exibe informações semelhantes às seguintes:

Descriptografar segredos do pacote

A configuração padrão para a propriedade do pacote ProtectionLevel SSIS é EncryptSensitiveWithUserKey. Quando o pacote é salvo, o SSIS criptografa apenas as partes do pacote que contêm propriedades marcadas sensitivecomo senhas, nomes de usuário e cadeias de conexão. Portanto, quando o pacote é recarregado, o usuário atual deve atender aos requisitos de criptografia para que as sensitive propriedades sejam descriptografadas. No entanto, o usuário atual não precisa atender aos requisitos de criptografia para carregar o pacote. Quando você executa o pacote por meio de uma etapa de trabalho do SQL Server Agent, a conta padrão é a conta de Serviço do SQL Server Agent. Essa conta padrão provavelmente é um usuário diferente do autor do pacote. Portanto, a etapa de trabalho do SQL Server Agent pode ser carregada e começar a executar a etapa de trabalho, mas o pacote falha porque não pode concluir uma conexão. Por exemplo, o pacote não pode concluir uma conexão OLE DB ou uma conexão FTP. O pacote falha porque não pode descriptografar as credenciais que ele deve ter para se conectar.

Importante

Considere o processo de desenvolvimento e o ambiente para determinar quais contas são necessárias e usadas em cada computador. A configuração EncryptSensitiveWithUserKey da ProtectionLevel propriedade é uma configuração poderosa. Essa configuração não deve ser descartada porque causa complicações de implantação no início. Você pode criptografar os pacotes quando estiver conectado à conta apropriada. Você também pode usar o utilitário de prompt de comando do Dtutil.exe SSIS para alterar os níveis de proteção usando um arquivo .cmd e o subsistema de comando do SQL Server Agent. Por exemplo, siga estas etapas. Como você pode usar o utilitário Dtutil.exe em arquivos em lote e loops, você pode seguir estas etapas para vários pacotes ao mesmo tempo.

  1. Modifique o pacote que você deseja criptografar usando uma senha.

  2. Use o utilitário Dtutil.exe por meio de uma etapa de trabalho do SQL Server Agent do Sistema Operacional (cmd Exec) para alterar a ProtectionLevel propriedade para EncryptSensitiveWithUserKey. Esse processo envolve descriptografar o pacote usando a senha e, em seguida, criptografar novamente o pacote. A chave de usuário usada para criptografar o pacote é a configuração da etapa de trabalho do SQL Server Agent na lista Executar como .

    Observação

    Como a chave inclui o nome de usuário e o nome do computador, o efeito de mover os pacotes para outro computador pode ser limitado.

Verifique se você tem informações detalhadas sobre o erro de falha do pacote SSIS

Em vez de depender dos detalhes limitados no Histórico de Trabalhos do SQL Server Agent, você pode usar o log do SSIS para garantir que tenha informações de erro sobre a falha do pacote SSIS. Você também pode executar o pacote usando o comando exec subsystem em vez do comando SSIS subsystem.

Sobre o log do SSIS

Os provedores de log e log do SSIS permitem capturar detalhes sobre a execução e as falhas do pacote. Por padrão, o pacote não registra informações. Você deve configurar o pacote para registrar informações. Quando você configura o pacote para registrar informações, são exibidas informações detalhadas semelhantes às seguintes. Nesse caso, você saberá que é um problema de permissões:

OnError,DOMAINNAME,DOMAINNAME\USERNAME,FTP Task,{C73DE41C-D0A6-450A-BB94-DF6D913797A1},{2F0AF5AF-2FFD-4928-88EE-1B58EB431D74},4/28/2006 1:51:59 PM,4/28/2006 1:51:59 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

OnError,DOMAINNAME,DOMAINNAME\USERNAME,Execute SQL Task,{C6C7286D-57D4-4490-B12D-AC9867AE5762},{F5761A49-F2F9-4575-9E2B-B3D381D6E1F3},4/28/2006 4:07:00 PM,4/28/2006 4:07:00 PM,-1073573396,0x,Failed to acquire connection "user01.msdb". Connection may not be configured correctly or you may not have the right permissions on this connection.

Sobre o comando exec subsystem e as informações de saída

Usando a abordagem de comando do subsistema exec, você adiciona opções de log de console detalhadas à linha de comando do SSIS para chamar o arquivo executável de linha de comando do SSIS Dtexec.exe. Além disso, você usa o recurso de trabalho avançado do arquivo de saída. Você também pode usar a opção Incluir Saída da Etapa no histórico para redirecionar as informações de log para um arquivo ou para o Histórico de Trabalhos do SQL Server Agent.

Veja a seguir um exemplo de uma linha de comando:

 dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT

O log do console retorna detalhes semelhantes aos seguintes:

Error: 2006-04-27 18:13:34.76 Code: 0xC0202009 Source: AgentTesting Connection manager "(local).msdb" Description: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'DOMAINNAME\username'.". End Error

Error: 2006-04-28 13:51:59.19 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error

Log: Name: OnError Computer: COMPUTERNAME Operator: DOMAINNAME\username Source Name: Execute SQL Task Source GUID: {C6C7286D-57D4-4490-B12D-AC9867AE5762} Execution GUID: {7AFE3D9E-5F73-42F0-86FE-5EFE264119C8} Message: Failed to acquire connection "(local).msdb". Connection may not be configured correctly or you may not have the right permissions on this connection. Start Time: 2006-04-27 18:13:34 End Time: 2006-04-27 18:13:34 End Log

Referências

Infelizmente, os usuários não estão cientes de que as configurações padrão da etapa de trabalho do agente os colocam nesse estado. Para obter mais informações sobre proxies do SQL Server Agent e SSIS, consulte os seguintes tópicos nos Manuais Online do SQL Server 2005:

  • Agendando a execução do pacote no SQL Server Agent
  • Criando proxies do SQL Server Agent