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

  • Artigo

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

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

Sintomas

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

Resolução

Para resolver esse problema, use um dos métodos a seguir. O método mais apropriado depende do ambiente e da razão pela 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 em SQL Server Agent difere 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 poderá 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 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 arquivo, como uma tarefa do sistema de arquivos SSIS.
  • Uma configuração de pacote SSIS baseada em 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 exige que a conta de usuário atual tenha permissões corretas.

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

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

    Esse método funciona para descriptografar segredos e atende aos principais requisitos pelo usuário. No entanto, esse método pode ter êxito 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 SSIS Package ProtectionLevel para ServerStorage. Essa configuração armazena o pacote em um banco de dados SQL Server e permite o controle de acesso por meio de SQL Server funções de banco de dados.

  • 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 SQL Server Agent etapa de trabalho para incluir essa senha.

  • Método 4: usar arquivos de configuração do pacote SSIS. Use 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 que DontSaveSensitive 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. Verifique se os arquivos de configuração estão adequadamente protegidos se contiverem informações confidenciais.

  • Método 5: criar 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 logon 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 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 SQL Server Agent e uma etapa de trabalho. Na lista Executar como, clique em SQL Server Agent Serviço para executar a etapa de trabalho. O texto no SQL Server Agent Histórico de Trabalho exibe informações que se assemelham ao seguinte:

Segredos do pacote de descriptografar

A configuração padrão da propriedade de pacote ProtectionLevel SSIS é EncryptSensitiveWithUserKey. Quando o pacote é salvo, o SSIS criptografa apenas as partes do pacote que contêm propriedades marcadas sensitive, como 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 SQL Server Agent etapa de trabalho, a conta padrão é a conta de serviço SQL Server Agent. Essa conta padrão provavelmente é um usuário diferente do autor do pacote. Portanto, a SQL Server Agent etapa de trabalho pode carregar 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 descontada 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 SSIS Dtutil.exe para alterar os níveis de proteção usando um arquivo .cmd e o subsistema de comando SQL Server Agent. Por exemplo, siga estas etapas. Como você pode usar o utilitário Dtutil.exe em arquivos e loops em lote, 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 um sistema operacional (cmd Exec) SQL Server Agent etapa de trabalho 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 SQL Server Agent configuração da etapa de trabalho 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 de erro sobre a falha do pacote SSIS

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

Sobre o registro em log do SSIS

Os provedores de log e log do SSIS permitem capturar detalhes sobre a execução e 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 que se assemelham ao seguinte. 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 as informações de comando e saída do subsistema exec

Usando a abordagem de comando do subsistema exec, você adiciona comutadores de log de console verbosos à linha de comando SSIS para chamar o arquivo executável da linha de comando 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 de Etapa na opção histórico para redirecionar as informações de log para um arquivo ou para o histórico de trabalho SQL Server Agent.

A seguir está 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 que se assemelham ao seguinte:

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 de etapa de trabalho do agente padrão as colocam nesse estado. Para obter mais informações sobre proxies SQL Server Agent e SSIS, confira os seguintes tópicos no SQL Server 2005 Books Online:

  • Agendamento da execução do pacote no SQL Server Agent
  • Criando proxies SQL Server Agent