Arquivo

Archive for the ‘Integration Services’ Category

Integration Services: Integração entre Oracle e SQL Server

fevereiro 21, 2011 3 comentários

Recentemente escrevi alguns artigos sobre integração com arquivo txt com o Integration Services, porém existe outra forma interessante de realizar integração que é através de banco de dados diferentes.

Nesse artigo pretendo fazer um exemplo onde consulto alguns registros no Oracle e realizo um insert desses registro no SQL Server.

A idéia é consultar as cidades que estão na tabela Oracle e importar para o SQL Server.

Crie a seguinte tabela no SQL Server.

CREATE TABLE [dbo].[CIDADE](
	[CD_CIDADE] [numeric](10, 0) NOT NULL,
	[DS_CIDADE] [varchar](100) NULL,
	[CD_UF] [varchar](2) NULL,
 CONSTRAINT [CIDADE_PK] PRIMARY KEY CLUSTERED
(
	[CD_CIDADE] AS
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Agora crie a seguinte tabela no Oracle

CREATE TABLE MUNICIPIO (
  CODIGO_MUNICIPIO NUMERIC(10) NOT NULL,
  NOME_MUNICIPIO VARCHAR2(100),
  UNIDADE_FEDERATIVA VARCHAR(2)
 );
ALTER TABLE MUNICIPIO ADD CONSTRAINT MUNICIPIO_PK PRIMARY KEY (CODIGO_MUNICIPIO);

Vamos realizar alguns insert na tabela MUNICIPIO na base Oracle.

<p>INSERT INTO MUNICIPIO VALUES (1, 'Barra Bonita', 'SP');
<p>INSERT INTO MUNICIPIO VALUES (2, 'Bauru', 'SP');
<p>INSERT INTO MUNICIPIO VALUES (3, 'Belo Horizonte', 'MG');
<p>INSERT INTO MUNICIPIO VALUES (4, 'São Manuel', 'SP');
<p>

Pronto temos nossa base de dados pronta, agora vamos ao Integration Services. Abra o SQL Server Business Intelligence Development Studio e crie um novo projeto Integration Services Project, chame o IntegracaoEntreBancos.

Na guia Control Flow adicione um componente DataFlow Task. Esse componente suporta um fluxo de dados que permite mover dados de uma fonte de dados para outra fonte. Mais informações sobre ele, acesse aqui.

Clique duas vezes sobre o componente DataFlow Task para que você veja levado a guia Data Flow.

Aqui você deve adicionar 3 componentes:

OLE DB Source: Realiza a conexão e a consulta com o banco de origem, no nosso exemplo o Oracle, mais informações aqui.

Derived Column: Carrega dados de uma variável de dados e realizar uma operação de insert, mais informações aqui.

OLE DB Destination: Gravar os arquivos em outra fonte de dados, pode ser um arquivo texto ou uma base de dados, mais informações aqui.

Com nossos 3 componentes, vamos configurar primeiramente o OLE DB Source, clique duas vezes sobre o componente e a tela OLE DB Source Editor será exibida. Primeiro passo é criar uma conexão com a base Oracle. Clique no botão New e a tela Configure OLE DB Connection Manager será exibida, clique novamente no botão New para criar a conexão.

Em Provider selecione Microsoft OLE DB Provider for Oracle, preencha os campos com usuário e senha e com a conexão com a base e teste a conexão. Com a conexão pronta clique em OK.

De volta a tela de OLE DB Source Editor em Data access mode selecione  SQL Command e digite a seguinte consulta:

<p>SELECT CODIGO_MUNICIPIO,
           NOME_MUNICIPIO,
           UNIDADE_FEDERATIVA
  FROM MUNICIPIO

Clique no botão Preview e veja o resultado da consulta, a tela deve ficar igual da imagem abaixo:

Clique no botão OK e ligue a seta verde do componente OLE BD Source ao componente Derived Column.

Clique duas vezes sobre o componente Derived Column para que a tela Devired Column Transformation Editor seja exibida.

Nesse componente vamos especificar quais as colunas serão retornadas e a alias do retorno, também podemos criar novas colunas através de expressões. Eu não tinha pensando nesse exemplo em utilizar uma coluna de expressão, mas agora decidir usar, então vamos fazer uma alteração na nossa tabela no SQL Server.

Vamos criar uma nova coluna na tabela Cidade no SQL Server, segue abaixo:

ALTER TABLE CIDADE ADD DS_CIDADE_UF NVARCHAR(200)

Essa coluna irá receber o nome da cidade mais o nome a sigla da unidade federativa. Voltando para a tela Derived Column Transformation Editor, realize a configuração igual a imagem abaixo:

O campo Derived Column Name é o nome que será retornado pelo componente. Derived Column é de que coluna essa “nova coluna” será derivada e expression é o valor que será retornado. Veja que a coluna DS_MUNICIPIO_UF criei uma expressão concatenando dois valores. As demais colunas você não precisa adicionar pois as mesma já estão adicionada como você pode ver na imagem acima no quadrado vermelho.

Clique em OK e ligue a seta verde do componente Derived Column com o componente OLE Source Destination.

Agora vamos configurar o OLE Source Destination, clique duas vezes sobre ele para que a tela OLE Source Destination Editor seja exibida. Será necessário criar uma conexão com a base SQL Server, caso você não tenha criado, crie uma como já expliquei em artigos anteriores.

Após criar a conexão em Data access mode selecione Table or View – fast load e em Name of the table or the view selecione a tabela Cidade. Deve ficar como a imagem abaixo:

Do lado superior esquerdo selecione mappings e faça a ligação entre as colunas que o componente Derived Column retorna com as colunas da tabela cidade do banco de dados SQL Server como imagem abaixo:

Pronto nosso projeto está pronto. Execute o projeto e em seguida faça uma consulta na tabela cidade no banco de dados SQL Server, você verá que os registros foram importados e que a coluna DS_MUNICIPIO_UF recebeu a concatenação que criamos no componente Derived Column, como na imagem abaixo:

Espero que tenham gostado e até a próxima.

Importar dados de um arquivo txt para uma base de dados – Parte 2

janeiro 10, 2011 1 comentário

Olá.

Em meu artigo anterior expliquei como importar dados de um arquivo txt que tinhas as colunas separadas por (;). Recebi alguns e-mails perguntando como fazer o mesmo procedimento, porém utilizando colunas com valores fixos, vou explicar (é necessário ter lido o artigo anterior para compreender).

Primeiro passo é limpar a tabela MUNICIPIO que criamos no banco de dados, faça:


DELETE FROM MUNICIPIO

Em seguida vamos criar um novo arquivo txt o qual está formatado da seguinte forma e salvar com o nome municipio2.txt:

Agora abra o projeto que você desenvolveu no artigo anterior no SQL Server Business Intelligence Development Studio, acesse a guia Data Flow e clique duas vezes no componente Flat File Source.

Com a janela Flat File Source Editor aberta clique no botão New para criamos uma conexão com o novo arquivo txt que criamos.

Na janela Flat File Connection Manager Editor, clique em browser e selecione o arquivo municipio2.txt e na opção Format selecione Ragged right conforme imagem abaixo:

Nas opções a sua esquerda selecione Columns, nessa opção poderemos delimitar as colunas que desejamos.  Veja que as colunas já são delimitadas pelo próprio Integration Services conforme imagem abaixo:

Na opções a sua esquerda selecione Advanced, veja que as colunas foram criadas e já com com o tamanho necessário, vamos alterar algumas informações:

Column0

Name: CD_MUNICIPIO

InputColumnWidth: 4

DataPrecision: 4

DataType: numeric[DT_NUMERIC]

Column1

Name:  NM_MUNICIPIO

InputColumnWidth: 18

DataType: string[DT_STR]

OutputColumnWidth: 18

Column2

Name: CD_UF

DataType: string[DT_STR]

OutputColumnWidth: 2

Exclua a Column3 ela não será utilizada e clique no botão OK.

Os demais passos não precisam ser alterados, devem ficar da mesma forma que foi explicado no artigo anterior (componente ADO.NET Destination).

Execute o projeto e veja que a integração será realizada.

Recomendo você ler esse artigo no site do MSDN Brasil com algumas informações importantes.

Espero que tenham gostado e até a próxima.

Importar dados de um arquivo txt para uma base de dados

Olá. Consegui escrever meu primeiro artigo de 2011 e assim com o primeiro post de 2010 esse artigo é sobre Integration Services.

A integração entre sistema sempre está presente entre as aplicações.  Em um artigo anterior demonstrei como capturar as informações que estão na base de dados e colocá-las em um arquivo TXT. Nesse artigo vou demostrar o processo inverso, através de um arquivo TXT realizar uma inserção em uma tabela do banco de dados.

Para realizar esse exemplo eu criei uma tabela em um banco de dados qualquer chamado MUNICIPIO. Abaixo temos o script.

CREATE TABLE [dbo].[MUNICIPIO]
(
	[CD_MUNICIPIO] [numeric](10, 0) NOT NULL,
	[NM_MUNICIPIO] [varchar](100) NULL,
	[CD_UF] [varchar](2) NULL,
)
GO

ALTER TABLE MUNICIPIO ADD CONSTRAINT MUNICIPIO_PK PRIMARY KEY (CD_MUNICIPIO)
GO

Com nossa tabela criada vamos criar agora um arquivo que irá conter as informações que serão migradas para nossa base de dados. O conteúdo do TXT segue abaixo:

0001;Barra Bonita;SP
0002;São Paulo;SP
0003;Rio de Janeiro;RJ
0004;Curitiba;PR
0005;Palmas;TO
0006;União dos Palmares;AL

Salve o txt e coloque o nome de município.txt. Agora vamos ao nosso projeto, execute o SQL Server Business Intelligence Development Studio, clique em File > New > Project e crie um projeto do tipo Integration Services Project. Chame de ImportarMunicipio.

Acesse a toolbox e arraste para dentro Control Flow o componente Data Flow Task. Clique duas vezes sobre o componente para ser levado para a guia Data Flow.

Nessa guia vamos arrastar dois componentes, o primeiro é Flat File Source. Esse componente irá acessar um arquivo como base de dados. No nosso exemplo o arquivo município.txt que criamos.

De duplo clique sobre o componente Flat File Source e a janela Flat File Sourece Editor será exibida. Clique no botão New e uma nova janela será exibida.

Na coluna Connection manager name preencha com Município. Na coluna File name clique no botão browser e selecione o arquivo txt que criamos. Veja que existem outras opções nessa janela, nesse artigo não vou explicar sobre eles, deixamos para outra oportunidade.

Após selecionar o arquivo, do lado direito você verá a opção Columns, selecione essa opção e você verá que o próprio componente já conseguiu formatar o arquivo nas colunas. Veja na imagem abaixo.

 

Agora selecione a opção Advanced. Aqui vamos formatar o nome das colunas e o tamanho dos campos conforme criamos nossa tabela, veja na imagem abaixo como deve ficar.

Clique no botão OK até retornar a guia Data Flow.

Vamos utilizar o segundo componente que se chama ADO NET Destination. Com esse componente vamos informar para qual base de dado e para qual tabela irão às informações de nosso arquivo txt.

Primeiro selecione a seta verde do componente Flat File Source e ligue com o componente ADO.NET Destination.

Clique duas vezes nesse componente, uma mensagem será exibida informando que esse componente não tem colunas de entrada e se desejamos configura-lo, clique na opção Yes.

Com a janela ADO.NET Destination Editor aberta clique no botão New ao lado de Connection manager para criamos uma nova conexão, no caso com a base em que criamos a tabela MUNICIPIO. (não vou explicar como criar uma conexão, caso exista dificuldade acesse meu artigo anterior aqui).

Com a conexão criada, na opção Use a table or view, selecione a tabela MUNICIPIO em seguida clique na opção Mappings. Você verá que o próprio componente já conseguiu relacionar as colunas do arquivo com as colunas do componente ADO.NET Destination, isso porque no passo anterior colocamos os nomes das colunas do arquivo txt igual as da tabela MUNICIPIO, caso isso não fosse feito, teríamos que informar as colunas para o componente ADO.NET Destination. Veja na imagem abaixo como ficou.

Pronto agora clique em OK e compile seu projeto. Veja que os passos ficarão verde, faça uma consulta em sua tabela no banco de dados e veja que os dados do arquivo foram inseridos com sucesso na tabela.

Espero que tenham gostado pessoal, esse é o primeiro de muitos artigos de 2011, até a próxima :D.

 

Criar arquivo de integração com Integration Services

Apesar da evolução do desenvolvimento de produtos e soluções e com o surgimento de novos conceitos (Web Services, WCF, etc) sempre existe a necessidade de realizar integração através de arquivos txt. (CNAB é um exemplo).

Uma forma de criar uma rotina de integração é utilizar o Integration Services da Microsoft. Para criar uma aplicação desse tipo precisado do Microsoft SQL Server (2005 ou 2008) e do SQL Server Business Intelligence Development Studio.

Inicie o SQL Server Business Intelligence Development Studio e acesse File\New Project e selecione Integration Services Projetc conforme imagem abaixo: Imagem 1.

Agora com o projeto criado vamos adicionar uma conexão com o banco de dados, nesse exemplo vamos utilizar o Adventure Works.

No Soluction Explorer clique com o botão direito em Data Sources\New Data Sources. Clique em Next na tela de “Welcome to the Data source Wizard”.

Na tela “Select how to define the connection” vamos criar uma string de conexão com nossa base de dados. Clique no botão new e a tela “Connection Manager” será exibida.

No combo Provider, selecione “Microsoft OLE DB Provider for SQL Server” na pasta Native OLE DB.

Na opção Server name selecione localhost, no caso do login vamos manter Windows Authentication e na opção Select or enter a database name selecione nossa base AdventureWorks. As configurações deve ficar igual a imagem 2.

Para finalizar clique no botão Test Connection. A mensagem Test connection succeeded deve ser exibida. Clique no botão OK para finalizar.

Clique em Next e a tela Completing the Wizard será exibida, vamos colocar o nome de nosso data source como “AdventureWorks” e clicar no botão Finish.

De volta a Soluction Explorer vamos expandir a pasta SSIS Packages e abrir o arquivo Package.dtsx que é o nosso pacote de integração.

Agora vamos a Toolbox e vamos selecionar o componente Data Flow Task. Esse componente realiza um mecanismo de fluxo de dados permitindo alteração e manusenio de dados.

Arraste o Data Flow Task para a Control Flow, clique duas vezes sobre ele e seremos levados ao a guia Data Flow.

Dentro do Data Flow iremos trabalhar com 3 componentes que serão listados abaixo:

  • OLE DB Source: Extrair dados de uma base de dados.
  • Derived Column: Criar novas colunas, com esse componente é possui concater 2 colunas e criar uma nova coluna.
  • Flat Flie Destination: Grava dados em um arquivo texto.

Clique duas vezes em OLE DB Source e a tela OLE DB Source Editor será exibida.

Em connection manager faça as configurações conforme imagem 3 e clique no botão OK.

Em seguida ligue a seta verde do componente OLE DB Source com o componente Derived Column. De duplo clique no componente Derived Column.

A tela Derived Column Transformation Editor será exibida. Em Derived Column Name digite FullName e em Expression digite FirstName + LastName. Acabamos de criar uma nova coluna baseada em 2 colunas já existem. Clique em Ok.

Agora lique a seta verde do componente Derived Column no componente Flat File Destination Editor e clique no botão new e a tela Flat File Format será exibida.

Nessa tela podemos selecionar como queremos que nosso arquivo txt seja gerado, temos as opções:

  • Delimited: Colunas Delimitadas por vírgulas.
  • Fixed width: As colunas são definidas por larguras fixas.
  • Fixed width wit row delimiters: As colunas são definidas pela largura fixa. Uma coluna extra, delimitado pelas caracteristicas nova linha, é adicionado para definir os delimitadores de linha.
  • Ragged right: As colunas são definidas pela largura fixa, exceto a última, que é delimitada pelo caráter nova linha.

Para mais informações sobre componente Flat File Destination acesse http://msdn.microsoft.com/en-us/library/ms141668.aspx

Vamos selecionar Delimited e a tela Flat File Connectoin Manager Editor será exibida.

Primeiro preenchemos o campo Description com MeuFlatFile e depois no campo File name digitamos C:\integracao.txt. Clique em Ok.

Agora é só compilar o projeto e verificar o arquivo de integração. Deve ser parecido com o da imagem 4.

Download do código fonte em disponível aqui.
Obrigado e até a próxima.