Categorias
SQL Server Troubleshooting Virtual PASS BR

SSIS – Proxy Account Permission

[bing_translator]

A utilização de Proxy Account para execução de pacotes SSIS dentro do SQL Server Agent é um procedimento para utilização de usuários que não são SYSADMIN executarem pacotes com outras credencias que não é a da conta de serviço do SQL Server Agent.

Existem varias referencias de como criar e utilizar Proxy Account porem eu passei por um problema com a utilização desse cenário.

Cenario:

Um pacote SSIS , que está armazenado em file system, com uma funcionalidade que deve ler um arquivo EXCEL em uma pasta compartilhada na rede e carregar os dados para outro lugar usando um login Windows.

A pasta compartilhada está em um domínio (Domínio A) e a servidor de Integration Services está em outro domínio (Domínio B)

Problema:

Tudo parecia perfeito, credencial criada, proxy account criada e associada corretamente , job criado correto porem na hora de executar o job o seguinte erro era apresentado:

Error:

Code: 0xC0202009

Source:  Excel Source [705]

Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

End Error

Error:

Code: 0xC02020E8

Source: Excel Source [705]

Description: Opening a rowset for “Plan1$” failed. Check that the object exists in the database.

End Error

Error:

Code: 0xC004706B

Source:  SSIS.Pipeline

Description: “component “Excel Source” (705)” failed validation and returned validation status “VS_ISBROKEN”.

End Error

A primeira tentativa de solucionar foi validar a mensagem de erro que dizia que ao abrir a “Plan1$” a operação falhou! Abri o pacote, verifiquei todas a conexões, tentei alterar algumas configurações do pacote e nada adiantou, tudo correto!

Solução:

Depois de um tempo pude perceber que o primeiro erro não estava na falha ao tentar abrir o arquivo “Plan1$” e isso era consequência.

Comecei a analisar a primeira mensagem: Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

Após pesquisar um pouco na internet achei o seguinte artigo:

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/10/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx

Quando cheguei ao item C apareceu um luz no fim do túnel para mim. Porem agora o problema era saber qual pasta dar permissão.

Nesse momento fui validar as variáveis de ambiente para tentar descobrir onde ficaria a pasta TEMP que o artigo dizia que deve ter permissão.

SSIS-ProxyAccountPermission - 01

 

 

 

 

 

 

Imagem 01

Agora sim!!! Tinha todas as informações e concedido acesso de leitura e escrita para o usuário do proxy account nessa pasta especifica. Hora de testar o JOB e adivinhem: mesmo erro novamente! Sinceramente não sabia o motivo do erro, pois uma vez que dei permissão na pasta e tudo o que o artigo falava estava correto, não sabia o motivo de não funcionar .

Sem saber o que fazer, surgiu a ideia de usar o Process Monitor (sysinternal) para tentar validar o que estava acontecendo no Windows. Depois de algum tempo analisando o processo e o erro consegui achar a seguinte informação:

SSIS-ProxyAccountPermission - 02

 

Acesso negado em um pasta!!! Mais uma vez uma luz apareceu porem sem saber o motivo do processo usar o usuário “Default” e a pasta “Temporary Internet Files”.

Enfim, para resolver o problema concedi permissão de escrita e leitura dentro da pasta “C:\User\Default\AppData\Local” para o usuário do Proxy Account e executei novamente o JOB que dessa vez terminou com sucesso!!!

Não sou especialista em SSIS para entender o funcionamento interno, porem somente com uso da ferramenta Process Monitor foi possível verificar o que realmente o processo fazia e onde ele precisava de permissão.

Referencias:

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/10/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx

http://technet.microsoft.com/en-us/sqlserver/ff686764.aspx

Categorias
Virtual PASS BR

Cache Transformation na pratica…

“O componente Cache Transformation grava dados de uma fonte de dados conectada no fluxo de dados em um gerenciador de conexões do Cache.” Essa é a definição que se encontra no TechNet.

O Cache Transformation pode ser utilizado de muitas maneiras mas a utilização combinada com o componente Lookup Transformation sem duvida é uma das melhores praticas. Isso porque o Lookup Transformation permite uma conexão com um arquivo de cache facilitando assim a busca por dados.

A combinação desses dois componentes em um ambiente onde temos pesquisa em grande quantidade de dados (milhões ou bilhões de registros) é uma grande vantagem em relação a pesquisa OLEDB realizada pelo Lookup Transformation.

Para mais conceitos sobre o Cache Transformation acesse:

Cache Transform

Cache Connection Manager

Cache Connection Manager Editor

Nesse exemplo, iremos utilizar 2 Data Flow: 1º para criar o Cache Transformation e o 2º para utilizar o Cache Transformation.

Package

 

1º Data Flow

Para utilizarmos esse componente primeiramente devemos carregar com informações e para isso utilizamos um Data Source de uma forma simples.ControlTaskLoadCache

Para configurar o Cache Transformation de duplo clique sobre ele e a seguinte tela aparecerá:CacheTranformationEditor

Clique em New… para criar uma nova conexão de cache caso não exista uma.CacheTranformationEditor01

De um nome para conexão e utilize um arquivo para armazenar as informações. Na aba Columns identifique a ordem do índice de pesquisa.

CacheTranformationEditor02

Clique em OK para concluir e volte para o Cache Transformation Editor. Selecione a aba Mappings para mapear as colunas com suas respectivas saídasCacheTranformationEditor03

 

2º Data Flow

Nesse Data Flow é onde iremos utilizar a conexão cache dentro do componente Lookup Transformation para carregar uma nova tabela. Dentro do Lookup Transformation existe a opção de conexão com o arquivo cache.LookupTransformation01

Na aba Connection especifique a conexão que acabamos de criar no Data Flow anterior

LookupTransformation02

Na aba Columns você deve mapear as colunas de referencia e saída. LookupTransformation03

Pronto, daqui para frente devemos desenvolver nosso pacote de acordo com as necessidades. Para finalizar meu pacote eu criei uma tabela de destino.

ControlTaskFullLoad

Isso ai.. até mais!!

Categorias
SQL Server

CDC no SQL Server 2012 na prática…

Com a chegada do SQL Server 2012 melhorias foram realizadas e novidades implementadas e é uma dessas novidades que apresentarei.

No SSIS alguns novos componentes foram implementados para facilitar a utilização do CDC (Change Data Capture):

clip_image001 Esse componente se encontra no Control Flow e ele que irá realizar todo o controle necessário acessar as informações através do Log Sequence Number (LSN) . Mais informação sobre o CDC Contral Task acesse o link.

http://msdn.microsoft.com/en-us/library/hh758674.aspx

clip_image002 Esse componente se encontra dentro do Data Flow e ele irá realizar a leitura dos seus dados. Mais informação sobre o CDC Source acesse o link.

http://msdn.microsoft.com/en-us/library/hh758686.aspx

clip_image003 Esse componente se encontra dentro do Data Flow e ele irá separar seus dados de acordo com as opções escolhidas dentro do CDC Source. Mais informações sobre o CDC Splitter acesso o link.

http://msdn.microsoft.com/en-us/library/hh758656.aspx

Preparação do Ambiente

O CDC tem inúmeras utilidades, mas uma delas é ajudar no processo de carga de um ambiente de Data Warehouse e esse foi o cenário que escolhi.

1º Passo – Criação dos databases

Irei utilizar 3 databases simulando um ambiente de carregamento de dados em um DW.

 1: CREATE DATABASE EXEMPLO_CDC

 2: GO

 3: CREATE DATABASE STAGING

 4: GO

 5: CREATE DATABASE DW_EXEMPLO_DCD

 6: GO

2º Passo – Preparando os databases

O database EXEMPLO_CDC ira servir como databases OLTP, onde todos os dados transacionais serão mantidos e é nele que temos que habilitar o CDC.

 1: USE EXEMPLO_CDC

 2: GO

 3: EXECUTE sys.sp_cdc_enable_db

 4: GO

Para mais informações sobre a stored procedure de sistema acesse o link http://msdn.microsoft.com/en-us/library/bb510486

Agora crie uma tabela e habilite o CDC para esse objeto.

 1: CREATE TABLE [dbo].[CATEGORIA]

 2: (ID INT NOT NULL IDENTITY PRIMARY KEY,

 3: NOME_CATEGORIA VARCHAR(100) NOT NULL)

 4:

 5: EXECUTE sys.sp_cdc_enable_table

 6: @source_schema = N'dbo',

 7: @source_name = N'CATEGORIA',

 8: @role_name = N'cdc_admin',

 9: @supports_net_changes = 1

 10: GO

 11:

Para mais informaçõe sobre a stored procedure de sistema acesso o link

http://msdn.microsoft.com/en-us/library/bb522475.aspx

 1: INSERT INTO [dbo].[CATEGORIA] VALUES

 2:  ('CATEGORIA 1')

 3: ,('CATEGORIA 2')

 4: ,('CATEGORIA 3')

 5: GO

 6:

 7: USE DW_EXEMPLO_CDC

 8: GO

 9: CREATE TABLE [dbo].[CATEGORIA_DESTINO]

 10: (ID INT NOT NULL PRIMARY KEY,

 11: NOME_CATEGORIA VARCHAR(100) NOT NULL)

 12:

Criando Pacote

Para esse exemplo irei criar dois pacotes, o primeiro de carga inicial ou carga completa e o segundo de carga incremental.

Pacote Inicial

Nesse pacote iremos criar uma carga completa dos dados. A primeira tarefa a se realizar é utilizar o componente CDC Control Task.

Algumas configurações importantes devem ser feitas nesse componentes.

  • CDC control operation: Utilize a opção Mark Initial load start. Esta operação é usada no começo de um pacote da carga inicial para registrar o LSN atual no banco de dados de origem antes de o pacote da carga inicial começar a ler as tabelas de origem.
  • Crie uma variável, User::CDC_State, para armazenar o estado da operação do CDC. O botão New faz isso automaticamente.
  • Utilize a opção Automatically store in a database table para persistir o estado do CDC.
  • Crie uma nova tabela para armazenar o estado do CDC. Clique em New e o assistente irá te fornecer um código para ser executado. Esta tabela será usada para rastrear as informações de carga CDC, de modo que você só pegar as novas alterações a cada vez que o pacote de carga incremental é executado
  • State Name: O nome associado ao estado persistente. Esse nome é usado para verificar a linha de estado na tabela de estado.

clip_image004

Figura 1

Proxima tarefa é iniciar seu Data Flow que nesse exemplo é bem simples.

clip_image005

Figura 2

Despois de realizar todos as manipulações de dados temos que novamente colocar o componente de CDC Control Task porem com uma modificação. Altere a opção CDC control operation para Mark initial load end

clip_image006

Figura 3

Seu pacote deverá parecer como a figura 4.

clip_image007

Figura 4

Pacote Incremental

Nesse momento irei utilizar o banco de dado STAGING para criar duas tabelas: CATEGORIA_DESTINO_DELTE e CATEGORIA_DESTINO_UPDATE. O motivo dessas tabelas é realizar as operações de UPDATE e DELETE de uma forma performática.

 1: USE STAGING

 2: GO

 3:

 4: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CATEGORIA_DESTINO_DELETE]') AND type in (N'U'))

 5: BEGIN

 6:     SELECT TOP 0 * INTO [dbo].[CATEGORIA_DESTINO_DELETE]

 7:     FROM [DW_EXEMPLO_DCD].[dbo].[CATEGORIA_DESTINO]

 8: END

 9:

 10: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CATEGORIA_DESTINO_UPDATE]') AND type in (N'U'))

 11: BEGIN

 12:     SELECT TOP 0 * INTO [dbo].[CATEGORIA_DESTINO_UPDATE]

 13:     FROM [DW_EXEMPLO_DCD].[dbo].[CATEGORIA_DESTINO]

 14: END

No pacote inclua o componente CDC Control Task com as seguintes configurações:

  • Crie ou configure uma conexao para o database de origem.
  • Configure o CDC Control Operation para Get processing range
  • Crie uma nova varial de estado (CDC_state)
  • Crie ou configure uma conexao para o database de destino
  • Selecion a tabela de estado que foi crianda anteriormente pelo pacote inicial ([dbo].[cdc_states])
  • Configure State name – Este nome deve ser igual ao que foi usado no pacote inicial (CDC_State)

clip_image008

Figura 5

Volte ao Control Flow e inclua um Data Flow e ligue-o no CDC Control Task;

Adicione um componente de CDC Source:

  • Configure a conexão para o database de origem;
  • Selecione a tabela [dbo].[CATEGORIA]. Nessa combo box irão aparecer apenas tabelas que estão habilitadas para o uso do CDC;
  • Configure o CDC processing mode para Net. Opção que busca apenas as alterações realizadas na tabela de origem;
  • Selecione a variável CDC_State;
  • Verifique a aba Columns para ter certeza que os dados estão sendo mapiados corretamente;

clip_image010

Figura 6

OBS: O CDC processing mode é a configuração mais importante aqui, pois cada modo de processamento terá um comportamento diferente que espera uma manipulação especifica.

Adicione o componete CDC Splitter.

Agora adicione 3 componetes ADO.NET Destination, um para nova linhas (Insert), outro para linhas atualizadas(UPDATE) e o ultimo para linhas exluidas (DELETE).

O componente para novas linhas (INSERT) você deve configura-lo para carregar diretamente a tabela [dbo].[CATEGORIA_DESTINO].

Os outros componentes devem ser mapeados para inserirem linhas nas tabelas [dbo].[CATEGORIA_DESTINO_DELETE] para linhas exluidas e [dbo].[CATEGORIA_DESTINO_UPDATE] para linhas atualizadas.

Agora o seu Data Flow deve estar parecido com a figura 7

clip_image011

Figura 7

Volte para o Control Flow e adicione dois componentes de Execute SQL Task. Um para realizar o UPDATE na tabela final e outro para realizar o DELETE na tabela final. Dessa maneira temos uma lote sendo executadas em cima de um conjunto de dados e não uma única linha sendo executada em um lote.

Abaixo segue os comandos de UPDATE e DELETE que utilizei.

 1: -- Update

 2:

 3: UPDATE dest

 4: SET

 5: dest.NOME_CATEGORIA = stg.NOME_CATEGORIA

 6: FROM

 7: [dbo].[CATEGORIA_DESTINO] dest

 8: INNER JOIN [STAGING].[dbo].[CATEGORIA_DESTINO_UPDATE] STG

 9: ON STG.ID = dest.ID

 10:

 11: -- Delete 

 12:

 13: DELETE FROM [CATEGORIA_DESTINO]

 14: WHERE ID IN

 15: (

 16: SELECT ID

 17: FROM [STAGING].[dbo].[CATEGORIA_DESTINO_DELETE]

 18: )

Por ultimo temos que atualizar nosso controle do CDC. Mais uma vez utilize o CDC Control Task para realizar essa tarefa e altera a opção CDC control operation para Mark processed range.

clip_image012

Figura 8

Inclui também um componente para limpar as tabelas do STAGING realizando um TRUNCATE nas tabelas.

O seu pacote deve estar parecido com a figura 9

clip_image013

Figura 9

Executando os Pacotes

Como já temos uma carga inicial com 3 registros vamos executar o pacote Inicial para realizar a carga.

clip_image014

Figura 10

Depois de executar o pacote Inicial podemos observar que a tabela cdc_states contem a seguinte informação.

clip_image015

Figura 11

Agora se executarmos o pacote incremental nesse momento não teremos nenhuma informação nova. Por isso vamos manipular alguns dados.

 1: USE EXEMPLO_CDC

 2: GO

 3: INSERT INTO dbo.CATEGORIA VALUES

 4: ('CATEGORIA 4')

 5: ,('CATEGORIA 5')

 6: ,('CATEGORIA 6')

 7: GO

 8: UPDATE dbo.CATEGORIA set NOME_CATEGORIA = 'CATEGORIA ALTERACAO 1'

 9: WHERE ID = 1

 10: UPDATE dbo.CATEGORIA set NOME_CATEGORIA = 'CATEGORIA ALTERACAO 2'

 11: WHERE ID = 2

 12: DELETE FROM dbo.CATEGORIA WHERE ID = 3

Depois de executar os comandos acima, vamos executar o pacote incrementar.

clip_image016

Figura 12

Fazendo uma consulta no seu destino podemos ver que os dados estao corretamente carregados de uma forma simples e eficiente.

clip_image017

Figura 13

Espero poder ajudar assim como o post original me ajudou. Eu literalmente traduzi e incrementei algumas coisas, mas os créditos são do Matt Masson. Para ver o post original acesse http://www.mattmasson.com/index.php/2011/12/cdc-in-ssis-for-sql-server-2012-2/

Obrigado!

Categorias
Vídeos

SSIS – Carregando arquivos EXCEL

Carregando vários arquivos EXCEL com SSIS para uma tabela de dados

Essa semana precisei carregar vários arquivos EXCEL para um DW e foi um pouco confuso, pois até o momento não tinha precisado fazer isso.

Depois de pesquisar e com ajuda de outros colegas, percebemos que é uma coisa muito simples porem muito útil.

Isso ai.. até a próxima!!