Tutorial DTS

Tutorial DTS
DICIONÁRIO DE DADOS ................................................................................................. 2
IMPORTAÇÃO ENTRE DOIS BANCOS DO DADOS ................................................... 4
DEFININDO ORIGEM E DESTINO ........................................................................................... 4
INICIALIZANDO VARIÁVEIS.................................................................................................. 6
CRIANDO UM PROCEDIMENTO CONDICIONAL A SER EXECUTADO – DATA DRIVEN QUERY
TASK ................................................................................................................................... 8
Guia Source .................................................................................................................... 8
Guia Bindings ............................................................................................................... 10
Guia Queries ................................................................................................................ 10
Guia Lookups................................................................................................................ 12
Guia Transformations .................................................................................................. 12
EXECUTANDO A IMPORTAÇÃO ........................................................................................... 15
REALIZANDO A IMPORTAÇÃO DE UM ARQUIVO TEXTO EM UM SERVIDOR
FTP....................................................................................................................................... 16
REALIZANDO O FTP .......................................................................................................... 16
DESTINO DINÂMICO ........................................................................................................... 16
ADICIONANDO A ORIGEM DE DADOS COMO ARQUIVO ........................................................ 19
ALTERANDO A LOCALIZAÇÃO DO ARQUIVO DE ORIGEM VIA SCRIPT .................................. 20
ATRIBUINDO O DESTINO DOS DADOS IMPORTADOS ............................................................ 21
TAREFA PARA COPIAR OS DADOS ....................................................................................... 22
DEFININDO A ORDEM DE EXECUÇÃO .................................................................................. 23
Dicionário de Dados
DTS – Data Transformation Services
Enterprise Manager – É um ambiente genérico de gerenciamento de serviços da
Microsoft®. Neste tutorial se refere ao gerenciador gráfico do SQL Server.
Packages – Pacotes do DTS a serem executados
CSV – Coma separated values
HTML – Hyper Text Markup Language
INTRODUÇÃO
O DTS permite importar/exportar dados entre diferentes fontes de dados.
As fontes/origens de dados podem ser bancos de dados ou arquivos (CSV, excel, HTML).
Este tutorial pretende mostrar alguns procedimentos para configuração de pacotes de
tarefas a serem executados periodicamente.
Este tutorial assume que o usuário já tenha familiaridade com o SQL Server e comandos
PL/SQL.
Para ter acesso ao Data Transformation Services, o usuário deve executar o Enterprise
Manager, e abrir a pasta Data Transformation Services.
Ao clicar sob Local Packages, serão listados todos os pacotes existentes.
Visualização do DTS
Importação entre dois bancos do dados
Será criado um pacote DTS para copiar os dados de uma instância do SQL Server para
outra.
Neste exemplo serão importados dados de um banco de dados de postos para um banco de
dados de Quotas.
Para criar um novo pacote DTS, clicar com o botão direito sob o item “Local Packages” e
selecionar “New Package”.
Será mostrado um editor gráfico de pacotes.
Modo design do DTS
Definindo Origem e Destino
Criar uma conexão para origem dos dados e uma para destino.
Para criar a conexões, acionar Connection->Microsoft OLE DB Provider for SQL Server.
Será aberta uma caixa de diálogo para configurar a conexão com o banco SQL Server.
Diálogo de configuração de conexão
Neste exemplo, “Postos – Ain” é a origem e “QuotasPrd” é o destino.
Inicializando Variáveis
É comum a necessidade de inicializar algumas variáveis globais.
Estas variáveis podem ser utilizadas em qualquer procedimento realizado no DTS.
É possível – por exemplo – utilizar uma variável global como parâmetro de uma stored
procedure.
Para inicializar as variáveis globais, deve-se criar um “ActiveX Script Task”.
Este componente permite criar procedimentos em linguagem de script interpretada (como
VBScript).
Para criar uma task execute: Task->ActiveX Task..
O exemplo abaixo inicializa o ano/mês de referência, que deve ser um mês anterior à
execução do pacote.
Observar que as variáveis locais ficam armazenadas em DTSTaskGlobalVariables.
A sintaxe é: DTSTaskGlobalVariables(<<NOME_VARIAVEL>>).value
Código fonte:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
'/**
' * Inicializa variaveis globais
'*
' * lngAnoMesReferencia - ano/mes (yyyymm) referencia a data que o pacote for executado
' */
Function Main()
DTSGlobalVariables("lngAnoMesReferencia").Value = clng(getAnoMesRegerencia())
Main = DTSTaskExecResult_Success
End Function
'/**
' * Retorna o ano/mes (yyyymm) de referencia da data que este pacote for rodado.
' * Por exemplo:
' * Caso seja rodado no dia 2004-09-08 (yyyy-mm-dd), ira retornar 200408 (yyyymm)
'*
' * @return inteiro longo que representa ano/mes de referencia (yyyymm)
' */
Function getAnoMesRegerencia() 'As Long
Dim dteReferencia 'As Date
dteReferencia = DateAdd("m", -1, Now())
If Month(dteReferencia) < 9 Then
getAnoMesRegerencia = CLng(CStr(Year(dteReferencia)) & "0" & CStr(Month(dteReferencia)))
Else
getAnoMesRegerencia = CLng(CStr(Year(dteReferencia)) & CStr(Month(dteReferencia)))
End If
End Function
Tela de criação de uma task ActiveX Script
Criando um procedimento condicional a ser executado – Data
Driven Query Task
Para realizar a importação de dados, podem ser utilizados o “Execute SQL Task” ou o
“Transform Data Task”. Neste exemplo utilizo o “Data Driven Task” por ser mais flexível.
Ele permite criar consultas para inserir/atualizar/deletar/selecionar que serão executadas
conforme uma ou mais condições.
Por exemplo:
Se o dado a ser copiado já existe, deve ser realizado um UPDATE. Caso contrário deve ser
realizado um INSERT.
Sendo assim, são necessárias uma consulta para INSERT e outra para UPDATE. Será
verificado se o registro já existe para escolher qual das consultas deve ser executada para
cada linha importada.
Para criar um “Data Driven Query Task” vá em: Task->Data Driven Query Task.
Será mostrado a seguinte caixa de diálogo:
Data Driven Query Task Properties
Guia Source
Nesta guia é definida a fonte de dados. Pode-se selecionar uma tabela ou escrever uma
consulta SQL (executar uma stored procedure, por exemplo).
No caso de consultas parametrizadas, deve-se especificar os parâmetros com o caractere
“?”.
A ordem dos parâmetros respeita a ordem de ocorrência dos caracteres “?”;
Data Driven Query – Guia Source
Para mapear os parâmetros com variáveis globais ou constantes, clicar em “Parameters”.
É importante prestar atenção a ordem de ocorrência dos parâmetros.
Ex.: O primeiro ? é o Param1, o segundo é o Param2 e assim por diante...
Guia Bindings
Nesta guia define-se o destino. Deve-se selecionar a tabela que irá receber os dados.
Data Driven Query Task – Guia Bindinfs
Guia Queries
Aqui são definidas as consultas que podem ser executadas.
Podem ser definidas consultas de Insert/Update/Delete/Select.
Construa as consultas de Insert e delete de forma a receber os dados da origem.
Código Insert:
INSERT INTO
dbo.TbFiscaisPostos (CodEscala, RgFiscal, AnoMesRef)
VALUES (?, ?, ?)
Código Update (neste exemplo o update não faz muito sentido, pois todos os campos fazem
parte da chave primária, logo não será utilizado):
UPDATE dbo.TbFiscaisPostos SET
CodEscala = ?,
RgFiscal = ?,
AnoMesRef = ?
WHERE CodEscala = ?
AND (RgFiscal = ?)
AND (AnoMesRef = ?)
Data Driven Query – Queries
Os parâmetros das consultas devem ser mapeados corretamente na listagem de parâmetros.
Cada registro existente na origem de dados irá executar uma destas consultas, e os
parâmetros mapeados serão trocados pelos respectivos valores.
Guia Lookups
Aqui são criadas consultas a serem utilizadas dentro do ActiveX Sctript para esta Data
Driven Query.
Por exemplo:
Para saber se deve ser realizado um insert ou um update, é necessário consultar se o registro
que está sendo importado já existe.
Para criar a consulta, preencha a coluna “Name”, escolha a conexão e clique no botão “...”
da coluna query..
Data Driven Query – Lookups
A consulta utilizada é:
SELECT COUNT(1) AS Expr1
FROM dbo.TbFiscaisPostos
WHERE (CodEscala = ?) AND (RgFiscal = ?) AND (AnoMesRef = ?)
Guia Transformations
Nesta guia são mapeados a origem e o destino.
Data Driven Query Task – Transformations
Nas guias “Source Columns” e “Binding Columns” selecione os atributos a serem
utilizados.
Deletar os scripts existentes e clicar em “New” para criar um novo ActiveX Script.
Abrir-se-á uma nova janela, que permitirá criar um novo ActiveXScript (escolher esta
opção).
Ao clicar em properties e será apresentado um editor de script.
ActiveX Script
Será criado, automaticamente um código que mapeia as origens nos destinos.
Como o código abaixo:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("AnoMesRef") = DTSSource("anoMesRef")
DTSDestination("RgFiscal") = DTSSource("rgFiscal")
DTSDestination("CodEscala") = DTSSource("codEscala")
Main = DTSTransformstat_InsertQuery
End Function
Neste script deve ser realizado o teste para descobrir se o registro que esta sendo importado
já existe no destino.
A consulta (INSERT/UPDATE) a ser executada depende do retorno da função main.
Algumas constantes que podem ser retornadas pela função main:
DTSTransformStat_InsertQuery – indica que deve ser utilizada a consulta de insert
DTSTransformStat_UpdateQuery – indica que deve ser utilizada a consulta de update.
DTSTransformStat_DeleteQuery – indica que deve ser utilizada a consulta de delete
DTSTransformStat_SelectQuery - indica que deve ser utilizada a consulta de select
Quando o registro já existir, usaremos DTSTransformStat_SkipInsert.
Esta constante indica que nada deve ser feito.
Alterar o código para:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("AnoMesRef") = DTSSource("anoMesRef")
DTSDestination("RgFiscal") = DTSSource("rgFiscal")
DTSDestination("CodEscala") = DTSSource("codEscala")
if DTSLookups("exists").Execute(DTSSource("codEscala"), _
DTSSource("rgFiscal"), DTSSource("codEscala")) = 0 then
Main = DTSTransformstat_InsertQuery
else
Main = DTSTransformStat_SkipInsert
end if
End Function
Este trecho de código utiliza a consulta criada na guia Lookups para verificar se o registro
já existe.
Não existindo o registro é utilizada a query de insert. Caso contrário, nada será feito.
Executando a importação
É necessário configurar a ordem de execução do pacote.
Para isto clique na DataDriven Query criada com o botão direito do mouse->Workflow>Workflow Propertirs.
No diálogo apresentado selecione o modulo ActiveX Script criado anteriormente.
O modelo workflow ficará como este:
Modelo workflow
Realizando a importação de um arquivo texto em um
servidor FTP
Este exemplo visa atender a necessidade de importação de arquivos gerados por mainframe
no padrão CSV.
Realizando o FTP
Acionar Tasks->File Transfer Protocol Task.
Abrir-se-á uma nova caixa de diálogo solicitando os parâmetros de conexão FTP.
Na guia location deve-se preencher os dados de origem e destino.
Na guia files deve-se escolher os arquivos a serem baixados.
Destino dinâmico
No momento em que o destino é especificado, o diretório listado é o do computador do
usuário.
Porém, quando o pacote for executado em um servidor, o destino será procurado no
servidor.
Isto é um problema, pois se faz necessária a replicação do ambiente do usuário no servidor.
Ou seja: se for configurado para fazer o download do arquivo em d:\sistemas\sistema1, este
diretório deverá existir no servidor.
Além disso, este diretório deverá ter permissão para escrita.
Uma forma de resolver este problema é utilizar o TEMP do usuário.
Assim, quando o pacote for executado por um job do SQL Server, o arquivo ficará no temp
do usuário do SQL Server.
E quando for executado pelo desenvolvedor, ficará no TEMP do desenvolvedor.
Para isto, será utilizado o “Dynamic Properties Task”. (Tasks-> Dynamic Properties Task).
Diálogo Dynamic Properties Task
Clicar em Add para definir uma nova propriedade dinâmica.
Abrir a árvore Tasks e selecionar o DTSTask_DTSFTPTask_1 (que é uma referência ao
task de FTP criado anteriormente).
Serão listadas todas as propriedades do task FTP.
A propriedade a ser configurada é “DestSite”.
Alterando propriedades dinamicamente
Clicar em DestSite e depois em Set.
Será aberta uma caixa de diálogo solicitando a origem da propriedade.
Selecione “Environment Variable” em “Source” e “TEMP” em “Variable”.
Atribuindo a variável TEMP
Adicionando a origem de dados como arquivo
Acionar Connection->Text File (Source) para adicionar uma conexão com o arquivo de
texto.
Como a localização do arquivo será dinâmica, apenas aponte para um arquivo texto de
exemplo (não importando a sua localização).
Clique em properties para configurar como os dados devem ser enxergados no arquivo.
Arquivo de origem
Alterando a localização do arquivo de origem via Script
Acione “Task->ActiveX Script Task” para criar um novo módulo de script.
Neste módulo será alterada a localização da origem (arquivo de texto) de dados (a que será
copiada de um servidor FTP).
Veja o código abaixo:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim oPacote 'as DTS.Package
dim o 'As Object
Set oPacote = DTSGlobalVariables.Parent
oPacote.Connections("Arquivo CSV").dataSource =
oPacote.Tasks("DTSTask_DTSFTPTask_1").customTask.destSite & "\teste.txt"
Main = DTSTaskExecResult_Success
End Function
Na linha:
oPacote.Connections("Arquivo CSV").dataSource =
oPacote.Tasks("DTSTask_DTSFTPTask_1").customTask.destSite & "\teste.txt"
Está sendo atribuído o arquivo copiado do servidor FTP.
“Arquivo CSV” é o nome do Text File e DTSTask_DTSFTPTask_1 é o nome do task FTP.
Script para alterar a origem do arquivo texto
Atribuindo o destino dos dados importados
Crie uma nova conexão para onde os dados serão copiados.
Neste exemplo será uma instância do SQL Server.
Connection->Microsoft OLE DB Provider for SQL Server
Será aberta uma caixa de diálogo para configurar a conexão com o banco SQL Server.
Conexão de destino
Tarefa para copiar os dados
Para copiar os dados, utilize Task->Transformation Data Task.
Selecione primeiro a origem, depois o destino.
Abra as propriedades desta task e edite o destino, escolhendo a tabela a ser populada.
Destino da importação
Definindo a ordem de execução
O pacote deve ser executado na seguinte ordem:
1 – Dynamic Properties
2 – FTP
3 – Módulo ActiveX
4 – Text File
Para definir a ordem, pressionar a tecla Ctrl selecionar dois itens (na ordem de execução).
Depois acionar Workflow->On success.
Ou seja:
Selecionar 1 e 2 e acionar Workflow->On success.
Selecionar 2 e 3 e acionar Workflow->On success.
Selecionar 3 e 4 e acionar Workflow->On success.