Data Transformation Services (DTS)
por Anderson Ferreira Souza
O Sql Server possui um recurso extremamente poderoso que é muito pouco utilizado pelos
administradores e programadores. Com certeza, sendo melhor explorado, poderá facilitar muito
nosso dia-a-dia, facilitando e melhorando as tarefas de importação e tratamento de dados.
O Data Transformation Services (DTS), com o próprio nome diz, é uma ferramenta para a
‘transformação’ de dados, que dizer, utilizando esta ferramenta, conseguimos importar e exportar
dados de e para praticamente qualquer tipo de formato de dados mais conhecido, sendo de txt para
sql server, de access para excel, de excel para access, de excel para sql server, de sql server para
txt... além de nos permitir o tratamento desses dados durante a importação, e várias outras tarefas
que podem ser programadas para serem executadas seguindo um fluxo pré-determinado pelo criador
do pacote.
Como a melhor forma de conhecermos uma nova ferramenta é colocando a mão na massa, vamos
construir um pequeno exemplo que vai importar dados de um arquivo texto chamado exemplo.txt,
para uma tabela no banco de exemplo NorthWind do Sql Server.
Para isto, vamos utilizar dois objetos Execute SQL Task
, um objeto DATA, tipo Text File
como origem de dados, já que vamos importar dados de um arquivo texto, e mais um objeto
Microsoft OLE DB Provider for SQL Server , que será nosso destino para os dados importados.
O que vamos fazer é tentar importar esses dados e, caso a importação seja realizada com sucesso,
vamos excluir o arquivo exemplo.txt, caso ocorra algum erro, vamos primeiro renomear o arquivo
exemplo.txt para exemplo_ddmmyyyy.txt e só depois excluí-lo.
No final, nosso exemplo ficará como mostrado na figura 1.
Figura 1
Antes de continuar, precisamos primeiro entender as opções de Workflow de um projeto DTS.
Selecionando o item workflow no menu, você poderá perceber que existem quatro opções de fluxo
que podemos utilizar em nosso projeto:
• O primeiro, Add Transform, representado pela seta cinza, é utilizado para a importação dos
dados propriamente dita ou, como o próprio termo diz, transformar os dados, de um formato
de armazenamento para outro.
•
•
•
Logo após temos o On Completion, representado pela seta azul, que aponta o caminho do
fluxo de tarefas que serão executadas assim que a tarefa anterior for completada,
independente de ter ocorrido erro ou não.
On Success, representado pela seta verde, indica o próximo passo que deve ser executado
apenas se ocorrer sucesso na tarefa anterior.
E, por último, On Failure, representado pela seta vermelha, que indica o próximo passo que
deve ser executado apenas se ocorrer falha na tarefa anterior.
Assim, fica mais fácil entender o que está sendo feito em nosso exemplo. Você pode ver que
começamos tentando importar os dados utilizando como origem o objeto Text File e como destino o
MS Ole Db e, caso ocorra um erro, o fluxo segue para o objeto ‘Copia Arquivo’ e logo depois,
ocorrendo sucesso ou não, seguimos para ‘Exclui Arquivo’. Caso tenha ocorrido sucesso na
importação, vamos diretor para ‘Exclui Arquivo’.
Então vamos lá.
Em seu servidor Sql Server, expanda a opção Data Transformation Services e selecione Local
Packages. Selecione a opção New Package clicando com o botão direito do mouse (figura 2) e então
será aberta a área de desenho do novo pacote, como mostra a figura 3.
Figura 2
Figura 3
Você poderá ver que na paleta Task, existem objetos vários objetos bem interessantes que podemos
utilizar para implementar nosso pacote DTS, como por exemplo o Send Mail
, que pode ser
utilizado para se enviar um e-mail aos administradores, informando sobre a execução do pacote, por
exemplo. Outro objeto muito importante é o Execute SQL Task
, que utilizaremos em nosso
exemplo, que pode executar qualquer string SQL válida.
A paleta Data, exibe os objetos que podemos utilizar na transformação dos dados, como podem ver,
há um arsenal bem completo, de forma que podemos importar ou exportar dados de e para qualquer
um destes formatos exibidos. Sql Server para texto, texto para Sql Server, excel para access e vice
versa... é só utilizar a imaginação e ver quanta coisa pode ser feita por aqui!
Muito bem. Estando com a tela de projeto aberta, selecione o objeto Text File (source)
, que vai
apontar para nosso arquivo exemplo.txt.
Assim que você selecionar o botão, aparecerá a tela de configuração, como mostra a figura 4:
Figura 4
Informe o caminho e nome do arquivo que deseja utilizar como origem, no nosso caso, vou
selecionar um arquivo localizado em c:\, com o nome exemplo.txt. O conteúdo deste arquivo será
cinco linhas com os dados da compra de produtos, separado por ponto-vírgula, sendo o primeiro
campo o código do produto, o segundo a quantidade e o terceiro campo a data da compra. O
conteúdo deste arquivo está listado na figura 5:
Figura 5
Depois de informar o arquivo, selecione o botão Properties para configurarmos os parâmetros de
importação deste arquivo. Na próxima tela (figura 6), escolha a opção Delimited, já que estamos
utilizando um arquivo delimitado por ponto-vírgula. Selecione Avançar.
Figura 6
Agora, vamos selecionar o tipo de delimitador, no nosso caso, Semicolon (figura 7), e selecione
Concluir e depois OK.
Figura 7
Pronto, já definimos nossa origem de dados você pode ver que o objeto Text File foi colocado na
área de desenho, agora vamos definir o destino.
Selecione na paleta Data o objeto Microsoft OLE DB Provider for SQL Server
, e tela de
configuração irá aparecer (figura 8).
Figura 8
Nesta tela definiremos o servidor a ser utilizado, a forma de conexão e o banco de dados. Informe
estes dados de acordo com as configurações de seu servidor, no meu caso, vou utilizar o banco de
dados de exemplo Northwind. Selecione OK e você vai ver que foi acrescentado o objeto na área de
desenho, já definimos o destino, falta agora configurar apenas as regras para a importação dos
dados.
Selecione os dois objetos na área de desenho arrastando o mouse sobre eles, selecione Workflow no
menu e Add Transform, será criada uma seta ligando os objetos selecionados, dê dois cliques na
seta de transformação para podermos edita-la, irá aparecer uma tela como a da Figura 9.
Figura 9
Nesta tela (Figura 9), definimos a origem dos dados na aba Source. Como estamos importando de
um arquivo texto, temos apenas a opção Table name, que é o nome do arquivo. Se estivéssemos
importando dados de um arquivo Access, por exemplo, em Table name poderíamos selecionar o
nome da tabela que desejamos importar. Neste caso, também poderíamos alterar o rádio para SQL
query, um recurso bastante poderoso nesta tela, em que podemos utilizar uma string SQL para gerar
minha origem de dados. Assim, poderia, por exemplo, utilizar uma cláusula WHERE para filtrar os
dados, ou obter os registros em mais de uma tabela utilizando o JOIN entre as tabelas de um banco
de dados. Como podem ver, é um recurso bastante prático, que nos abre um grande leque de opções
na importação.
Na próxima aba (Destination – Figura 10), definimos as configurações do destino. Podemos aqui,
escolher uma tabela existente no banco, caso já existe, ou criar uma nova clicando no botão Create
New. Em nosso exemplo, utilizei esta opção para criar uma tabela nova com 3 campos como pode
ser visto na Figura 10.
Figura 10
Na aba Transformations (Figura 11), iremos determinar a referência entre os campos de origem e
destino, dizendo, em nosso exemplo que o primeiro campo do arquivo texto será importado para o
campo Codigo de nossa tabela no banco Northwind e assim por diante.
Figura 11
Na última aba (Advanced), temos as opções avançadas que não iremos alterar neste momento, mas
há recursos interessantes que podem ser configuradas aqui como, por exemplo, as opções que
determinam que a importação pule um número determinado de linhas no início e/ou final de nosso
arquivo.
Mas vamos em frente. Clique no botão OK e, com isto, nós já terminamos a parte da importação
dos dados, se executarmos nosso pacote agora, a importação já será executada como foi
determinado por nossa configuração, mas não iremos parar por aí, vamos incrementar um pouco
mais nosso exemplo e colocar as opções para manipular o arquivo texto de acordo com o resultado
da importação.
Clique na paleta Task em um componente Execute SQL Task
configuração (Figura 12).
, e irá aparecer a tela de
Figura 12
Preencha os campos como na figura e clique em OK. Em nosso exemplo, alterei a descrição para
‘Exclui Arquivo’ e acrescentei uma linha em SQL statement:
master..xp_cmdshell 'del c:\exemplo.txt', NO_OUTPUT
O que faz esta linha? Simplesmente chama a procedure xp_cmdshell para executar o comando ‘del
c:\exemplo.txt’ para excluir este arquivo do disco.
Agora falta definirmos que este comando seja executado logo após a importação, caso não tenha
ocorrido erro.
Selecione com o mouse os componentes Microsoft OLE DB Provider for SQL Server
e Execute
SQL Task
(agora renomeado para ‘Exclui Arquivo’) que você colocou na tela, clique em
Workflow no menu e logo depois em On Success. Você verá que foi criada outra seta entre estes
dois objetos, de cor verde, o que quer dizer que esta tarefa será executada apenas se a importação
ocorrer sem erros.
Agora, clique na paleta Task no componente Execute SQL Task
novamente para criarmos a
tarefa que será executada caso ocorra um erro e preencha de acordo com a figura 13.
Figura 13
Foi alterado o nome da tarefa para ‘Copia Arquivo’ e em SQL statement, temos o seguinte
comando:
declare @nom as char(80)
select @nom = 'copy c:\exemplo.txt c:\ExemploFail_'+convert(varchar, datepart(yyyy,
GetDate()))+convert(varchar,
datepart(mm,
GetDate()))+convert(varchar,
datepart(dd,
GetDate()))+convert(varchar,
datepart(hh,
GetDate()))+convert(varchar,
datepart(mi,
GetDate()))+convert(varchar, datepart(ss, GetDate()))+'.txt'
exec master..xp_cmdshell @nom, NO_OUTPUT
O que esta string faz é copiar o arquivo exemplo.txt para o mesmo local, mas alterando seu nome
para ExemploFail_yyyymmddhhmmss.txt, quer dizer, estamos arquivando este arquivo com a data
e hora em que ocorreu o erro de importação.
Para terminar precisamos informar que esta tarefa deve ser executada caso ocorra um erro, então
selecione com o mouse os componentes Microsoft OLE DB Provider for SQL Server
e Execute
SQL Task
(agora renomeado para ‘Copia Arquivo’), clique em Workflow no menu e logo
depois em On Failure. Você verá que foi criada outra seta entre estes dois objetos, de cor vermelha,
o que quer dizer que esta tarefa será executada apenas se houver erros na importação.
Finalmente, vamos informar ao sistema para executar a exclusão deste arquivo depois de tentar
renomeá-lo, tendo ocorrido erro ou sucesso nesta tarefa.
Selecione com o mouse os componentes Execute SQL Task
(renomeado para ‘Copia Arquivo’)
e o Execute SQL Task
(renomeado para ‘Exclui Arquivo’), clique em Workflow no menu e
logo depois em On Completion. Você verá que foi criada outra seta entre estes dois objetos, de cor
azul, o que quer dizer que esta tarefa será sempre executada.
Neste ponto, nosso projeto deve se parecer com a Figura 14.
Figura 14
Pronto, basta agora salvar o projeto e executar o pacote clicando em Execute
ferramentas.
na barra de
Faça alguns testes e verá que o pacote irá se comportar de forma diferente caso ocorra erro ou
sucesso na importação dos dados.
Por enquanto ficamos por aqui. Podemos ver que o recurso DTS do Sql Server é bastante poderoso,
nos dando uma incrível ferramenta de importação e tratamento de dados que pode resolver vários
problemas encontrados em nosso dia-a-dia, simplificando a automatização de tarefas onde antes
precisaríamos construir um sistema bem mais complexo. Isto sem contar tantas outras vantagens
que encontramos ao utilizar seus recursos.
Até a próxima...
Anderson Ferreira Souza é Bacharel em
Ciências da Computação, desenvolve projetos
em delphi e administrador/programador Sql
Server, atualmente é Analista de Sistemas do
Campus de Passos da Universidade do Estado
de Minas Gerais.
Pode ser contatado em [email protected]