Arquivo da categoria: BI, DW, ETL (SQL Server)

Por que utilizar uma ferramenta de ETL ?

Bom Dia Pessoal,

Essa semana ministrarei um curso oficial de Integration Services em um CPLS. Já ministrei esse curso algumas vezes e sempre no início do curso eu deixo o assunto SSIS de lado e começo a falar de ETL de uma forma geral. ETL é o acrônimo de Extract, Transform & Load e trata-se de um processo indispensável na construção de um Data Warehouse. O Data Warehouse dificilmente existirá sem um processo de ETL (por mais simples que seja), mas processos de ETL existem para atender a muitas necessidades (não somente abaster um Data Warehouse).

Há milhares de ferramentas de ETL disponíveis no mercado como o Oracle Warehouse Builder (OWB), IBM Information Server (Data Stage), o Integration Services (SSIS), o Power Center (antigo Power Mart), etc para citar algumas. Normalmente ferramentas de ETL custam caro e não raras às vezes as pessoas se perguntam se é mesmo necessário adquirir uma ferramenta de ETL. Já escutei algumas coisas do tipo:

  • "Se eu vou só transportar dados de um lugar pro outro eu preciso comprar uma ferramenta pra isso ?"
  • "É só pegar meia dúzia de tabelinhas do banco A e mais algumas do banco B e colocar no banco C. Pra que ferramenta ?"
  • "Putz, além de ter de entender de PL/SQL, TSQL e Java eu agora vou ter que aprender mais uma tecnologia só pra transportar dados ?"
  • "Não dá pra fazer um programinha pra poder fazer essas cargas ? Uma ferramenta de ETL é muito cara"
  • "Se usamos somente banco de dados ORACLE não precisamos de ferramenta de ETL, afinal é de ORACLE pra ORACLE e aí é tudo compatível"

De fato são questionamentos que à primeira vista parecem imbatíveis. Os custos com ferramentas de ETL podem ser expressivos variando desde alguns dólares por CPU até algumas centenas de milhares de dólares para licenciar um servidor. Nenhum gestor irá dispender recursos financeiros se julgar que eles não absolutamente necessários e isso se aplica a ferramentas de ETL. Avaliar o valor agregado de um novo servidor ou de um novo software é algo relativamente direto. Bastaria avaliar a quantidade de transações processadas, a satisfação dos usuários, o aumento de visitas no site, a produtividade adquirida, etc. Entretanto avaliar os ganhos de uma ferramenta de ETL antes mesmo de adquirí-la já é algo bem mais complicado. Se o retorno é difícil de ser mensurado e de ser visto, naturalmente adquirir uma ferramenta de ETL pode não ser algo fácil. Mesmo as alternativas Open Source como Pentaho podem ser difíceis de implementar se os benefícios do uso da ferramenta não forem claros.

Dado esse contexto é comum surgir o questionamento "Preciso mesmo de uma ferramenta de ETL ? Não dá para desenvolver isso na mão ?". A resposta categórica de muitos fabricantes é simplesmente dizer que a ferramenta de ETL é indispensável, que tem várias vantagens, é mais performática, etc. Não discordo de muitos desses argumentos, mas pessoalmente acredito que possuir uma ferramenta de ETL não é indispensável para todas as situações e há cenários que a construção "hard code" pode ser mais vantajosa. Se me perguntassem se um processo de ETL deve ou não utilizar uma ferramenta eu diria "depende". Há vantagens e desvantagens associadas em utilizar uma ferramenta ou fazer todo o processo "hard code".

Algumas vantagens do uso de processo "Hard Code"

  • Controle: Se você desenvolve tudo do zero, você tem controle completo sobre o processo. Esse controle evita qualquer tipo de "caixa preta" ou desconhecimento de como uma determinada parte da carga funciona. Todo o processo é conhecido e controlada de ponta a ponta e pode ser modificado da forma desejada quando necessário.
  • Customização: As possibilidades de customização são infinitas, pois, todo o código do processo de carga estará disponível para eventuais mudanças. Isso inclui novas funcionalidades na carga, adaptabilidade em relação a algum framework interno da empresa, etc.
  • Convergência com a plataforma tecnológica: Não será necessário adquirir hardware, software ou um sistema operacional a parte para adaptar a ferramenta de ETL ao direcionamento tecnológico da organização. A criação de um processo de ETL "hard code" permite a escolha da linguagem de programação que a organização jugar mais adequado.
  • Convivência com o legado: O desenvolvimento interno sempre se adaptará aos sistemas legados e nunca será impositiva a mudança do legado para adaptar-se às cargas e (ou) construção de pequenos módulos para realizar essa integração.
  • Suporte: A construção própria dispensa contratos de suporte e manutenção com o fabricante. Você também não será surpreendido pelo fato de um fabricante simplesmente descontinuar o produto e não comercializar mais suporte.
  • Debugação: As atividades de DEBUG não vão se deparar com uma parte não "debugável" do código, pois, não haverá no processo partes cujo codigo fonte não esteja disponível

Algumas vantagens do uso de ferramentas de ETL

  • Desenvolvimento das cargas: Desenvolver uma rotina de carga em uma ferramenta de ETL é muito mais fácil e rápido que codificá-la. Dependendo da facilidade da ferramenta é possível inclusive que usuários não técnicos a utilizem para cargas mais simples.
  • Manutenção das cargas: As tarefas de manutenção de uma rotina de carga são mais fáceis de realizar em relação à manutenção de código.
  • Desempenho: As ferramentas de ETL utilizam métodos mais performáticos para trabalhar com grandes volumes e normalmente conseguem extrair, transformar e carregar dados com mais velocidade e menos utilização de recursos. Isso inclui operações não logadas, gravações em bloco, etc.
  • Execução em paralelo: Ferramentas de ETL possuem recursos de paralelização nativos e facilmente implementáveis.
  • Escalabilidade: Ferramentas de ETL podem ser transferidas de servidor mais facilmente e até eventualemente distribuir sua carga entre vários servidores.
  • Diversidade de conectores: A conexão de uma ferramenta de ETL com múltiplas fontes de dados é transparente. Caso apareça alguma fonte não trivial como o SAP, Mainframe, VSAM, etc é possível adquirir o conector sem a necessidade de codificar um.
  • Separação entre funcionalidade e manipulação de dados: Uma ferramenta de ETL já possui suas funcionalidades disponíveis (Lookup, Merge, Split, Expressões calculadas, etc). Só é necessário concentrar-se em como fluir os dados dentro da carga e não codificar cada tarefa da carga.
  • Reusabilidade: Uma carga normalmente pode ser reaproveitada dentro de outras cargas ou sobre a forma de um template
  • Reinicialização: Ferramentas de ETL possuem a capacidade de reiniciar a carga de onde pararam sem a necessidade de codificar essa inteligência
  • Manutenção de Metadados: Os metadados são gerados e mantidos automaticamente com a ferramenta evitando que problemas de conversão gerem dados não íntegros ao final do processo. A manutenção de metadados também evita ou alerta para alterações de esquema que invalidem a carga.
  • Documentação: As ferramentas de ETL possuem mecanismos de documentação (quando não são autoexplicativas). Isso pode ser um diferencial significativo principalmente para equipes de alta rotatividade.
  • Maior garantia da qualidade dos dados: Ferramentas de ETL podem disponibilizar meios para trabalhar a qualidade dos dados através de algoritmos complexos (lógica fuzzy, IA, etc).
  • Auditoria & Tracking: É possível implementar recursos de auditoria e tracking para conhecer de onde veio o registro, que transformações sofreu e como foi carregado.
  • Segurança: É possível tornar a segurança mais modular dividindo-se os papéis (criação de cargas, execução de cargas, agendamento, etc)

Como é possível observar há vantagens e desvantagens associadas entre utilizar ou não uma ferramenta de ETL e muitos fatores podem influenciar nessa escolha (custo, tamanho do projeto, complexidade das cargas, etc). Com o declínio dos custos das ferramentas ETL e aumento constante de uso de ETL (principalmente para atender às crescentes demandas de BI), creio que optar por desenvolver todo o processo de ETL de forma "hard code" será algo cada vez mais sem sentido. Mesmo uma tarefa simples como retirar dados do SQL Server e colocar no Excel envolve um certo trabalho se for completamente codificada.

Um cenário comum bem relacionado a essa avaliação é a utilização de stored procedures para carregar dados. Já vi diversas implementações que utilizam stored procedures para carregar dados. Em minha opinião, utilizar stored procedures para carregar dados é algo que funciona bem para pequenos projetos e iniciativas, mas é algo inadequado para processos de cargas complexas e (ou) grandes volumes. Apresento abaixo algumas razões pela qual stored procedures não devem ser utilizadas em atividades de carga. Foquei mais no SQL Server, mas provavelmente boa parte das razões (senão todas) são válidas para outros SGBDs.

  • Incapacidade de rodar em paralelo: Por melhor codificada que uma stored procedure possa ser ela não é capaz de paralelizar comandos, ou seja, não há como uma stored procedure rodar duas instruções de INSERT em paralelo. Será necessário finalizar a primeira instrução para prosseguir para a segunda. Com grandes cargas e muitas bases, tal abordagem pode simplesmente ser proibitiva em virtude da janela de tempo
  • Incapacidade de se recuperar no caso de uma parada: Se o processo que roda uma stored procedure for eliminado por qualquer razão (kill, deadlock victim, reinicialização do servidor, etc) não há como a carga se recuperar do ponto em que parou. É possível codificar essa inteligência na stored procedure, mas reinicializar a partir de uma parada não é uma característica nata.
  • Contexto transacional: Stored Procedures rodam sobre um contexto transacional e vão impor bloqueios até que finalizem sua execução. Se uma stored procedure demorar para executar é possível que boa parte dos seus recursos sofra contenção até que ela finalize provocando bloqueios e lentidão para os outros processos. Se ela for eliminada, o custo de ROLLBACK pode ser considerável gerando mais indisponibilidade no acesso a dados além de aumentar o log de transações. Tais características podem ser descartadas em um processo de ETL retirando seu contexto transacional bem como implementar cargas não logadas.
  • Não isolamento: Stored Procedures não podem ser isoladas do resto das atividades do SGBD. Elas sempre irão rodar no mesmo lugar que o SQL Server está impossibilitando movê-las para outro servidor para aliviar a carga das demais tarefas do SQL Server. Embora o Resource Governor possa ajudar com essa limitação não haverá como retirar as stored procedures do servidor onde o SQL Server está.
  • Dificuldades para escalar: Se as stored procedures estão provocando lentidão em um servidor SQL Server não há muita escolha senão aumentar as capacidades do servidor de banco de dados. Se uma ferramenta de ETL fosse utilizada outros mecanismos de escalabilidade podem estar disponíveis como o balanceamento de carga por exemplo.
  • Manutenção e Complexidade: Cargas complexas podem significar stored procedures bem longas com milhares de linhas (já vi SP com 20.000 linhas). Dar manutenção em um código desses é algo bem complicado já que não há nenhuma iteração visual. As atividades de testes e análise de impactos também são muito mais complicadas.

Nas palavras de Ralph Kimball no clássico "Data Warehouse ETL Toolkit", ele é enfático quando diz: "Um sistema de ETL pode fazer ou invialibizar o Data Warehouse". Embora o processo de ETL seja uma atividade em background que o usuário não visualize, ele certamente consome a maior quantidade de tempo de um projeto de Data Warehouse e Business Intelligence (há percentuais variando de 70% a 90%). A escolha em optar ou não para uma ferramenta de ETL juntamente com a complexidade do projeto é crucial na determinação desse percentual.

[ ]s,

Gustavo

Como importar dados a partir de um range de células de uma planilha excel ?

Boa Tarde Pessoal,

Essas últimas semanas eu fiquei bem atarefado por contas de alguns incidentes em produção no trabalho que renderam algumas madrugadas. Aliado a isso passei as duas últimas semanas ministrando cursos oficiais na área de BI no SQL Server 2008. Estou correndo para tirar o atraso e para não perder o hábito de postar vou deixar aqui uma dica rápida relacionada ao Integration Services. Um dia desses me deparei no fórum com um dúvida bem interessante. Referia-se a como fazer uma importação de dados a partir de um range de células do Excel. O Excel é sem dúvida um fonte de dados muito popular e não raras às vezes os projetos com o Integration Services trabalham com ele. É bem simples importar dados de uma planilha inteira, mas o que fazer quando apenas uma parte dos dados deve ser realmente importada ? Seria fácil fazer a importação completa para uma área temporária e posteriormente efetuar o descarte dos dados ou até utilizar uma transformação do tipo SPLIT, mas em ambos os casos a leitura da planilha seria completa o que me leva a crer que essas não seriam alternativas muito performáticas. Haveria uma forma de importar somente os dados necessários diretamente da origem ? Vejamos como fazê-lo.

Uma planilha de exemplo

Para demonstrar como funciona a importação, utilizei uma planilha com alguns exemplos de produtos ofertados na internet. A planilha é relativamente simples tendo apenas o nome do produto, o preço e o nome do ofertante conforme o esboço abaixo:

A construção do pacote

A montagem do pacote não tem mistério. Estou considerando que o arquivo XLSX está localizado em C:\Lista.xlsx. Será necessário um Data Flow com o conector Excel Source. Configurei as seguintes propriedades para o conector com o Excel.

Aba Propriedades
Connection Manager

OLE DB Connection Manager: (Clicar em New)
    Excel File Path (Browse): C:\Lista.xlsx
    Excel version: Microsoft Excel 2007
    First row has column names (marcado)

Data access mode: Table or view

Name of the Excel sheet: Produtos$

Columns External Column – Output Column
    Produto – Produto
    Preço – Preço
    Ofertante – Ofertante

Para conferir se a conexão com o Excel foi realizada corretamente e aproveitar para dar uma visualização nos dados, basta apenas clicar sobre o botão Preview na aba connection manager conforme a figura abaixo:

Da forma como foi montado o conector com o Excel, todos os dados da planilha "Produto" serão importados. Essa é a forma padrão, mas é contrário ao proposto pelo artigo. Supondo que apenas os produtos ofertados pelas lojas americanas sejam desejados, ou seja, o intervalo de células A5 até C7, será necessário fazer algumas alterações no connection manager e no Excel Source conforme os passos abaixo:

  • Navegue até o painel central inferior
  • Clique sobre o connection manager com o Excel (possivelmente chamado de Excel Connection Manager)
  • Desmarque a opção First row has columns names
  • Clique sobre o conector com o Excel
  • Ao aparecer uma janela (Editing Component) aceite a correção de erros clicando em Yes
  • Na aba Connection Manager mude a propriedade Data Access Mode para SQL Command
  • Na propriedade SQL command text preencha SELECT * FROM [Produtos$A5:C7]
  • Clique em Preview para visualizar os dados

Os dados mostram que as colunas Produto, Preço e Ofertante foram renomeadas para F1, F2 e F3 respectivamente como consequência das alterações no Connection Manager. Os dados contemplados na visualização são apenas os referentes às lojas americanas por conta do range de células (A5:C7).

A conexão com os dados considerando as células desejadas foi efetuada com êxito. O próximo passo é a configuração das colunas para que alguma transformação ou destino possa consumir esses dados.

  • Clique sobre Columns
  • Em Output Column renomeie F1 para Produto
  • Em Output Column renomeie F2 para Preco
  • Em Output Column renomeie F3 para Ofertante

Com a fonte de dados devidamente configurada, é possível escolher qualquer conector de destino para carregar os dados. Para esse exemplo optei pelo Flat File Destination.

As propriedades do conector Raw Destination devem ser configuradas conforme a tabela abaixo:

Aba Propriedades
Connection Manager Flat File connection manager: (Clicar em New)
    Flat File Format: Delimited
    Connection Manager name: Lista
    General
        File name: C:\Lista.txt (Aceitar todos os padrões)
    Columns (Aceitar todos os padrões)
    Advanced
        Produto (Mudar a propriedade ColumnDelimiter para Tab {t})
        Preço (Mudar a propriedade ColumnDelimiter para Tab {t})
Mappings Input Column – Output Column
    Produto – Produto
    Preço – Preço
    Ofertante – Ofertante

Com as configurações de todos os objetos efetuadas corretamente só é necessário executar o pacote e conferir o layout do arquivo. Somente as células desejadas foram contempladas.

A abertura do arquivo C:\Lista.txt mostra que apenas os produtos e preços das lojas americanas foram exportados. Com a correta configuração do conector ao Excel, esses dados poderiam ser exportados para outras fontes como o SQL Server, Oracle, etc. O grande detalhe para filtrar as células está na consulta via comando à planilha desejada. Aos que não conseguiram reproduzir o exemplo e (ou) desejem o projeto SSIS utilizado, disponibilizei o mesmo no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20100328%5E_ExcelImportCells.zip

[ ]s,

Gustavo

Como importar e exportar imagens entre o SQL Server e o File System ? – Parte II

Olá Pessoal,

No último artigo, descrevi algumas formas de importar / exportar imagens entre o SQL Server e o File System. A solução envolveu alguns comandos TSQL e o legado utilitário TextCopy. Nesse artigo demonstrarei como realizar essas importações / exportações através do SSIS. O SSIS possui recursos bem interessantes para efetuar essas tarefas.

Para demonstrar como importar e exportar as imagens entre o SQL Server e o File System usando o SSIS, utilizarei as imagens das bandeiras de alguns países (Alemanha, Brasil, China, Espanha, Estados Unidos e Inglaterra). As mensagens estão disponíveis no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20090610%7C_Imagens.zip

O primeiro passo é a criação dos objetos para armazenar as imagens.

— Cria um banco de dados
CREATE DATABASE Imagens

— Muda o contexto de Banco de Dados
USE Imagens;

— Cria uma tabela para armazenar as bandeiras
CREATE TABLE Bandeiras (Pais VARCHAR(50),
    Arquivo VARCHAR(100), Imagem VARBINARY(MAX) NOT NULL)

— Adiciona um valor Default (recomendável para Blobs)
ALTER TABLE Bandeiras ADD CONSTRAINT
    DF_Imagem DEFAULT 0x0 FOR Imagem

O script cria um banco de dados e uma tabela para armazenar as imagens. É recomendável utilizar um valor Default para colunas Blobs, pois, nem sempre os aplicativos e APIs conseguem trabalhar com valores nulos. Diferente do script do artigo anterior, o tipo VARBINARY(MAX) foi utilizado, pois, o IMAGE encontra-se em desuso e possivelmente não estará presente nas versões futuras do SQL Server.

Em seguida é necessária a criação de um pacote SSIS para a importação das imagens (chamei-o de ImpImagens). A criação do pacote é bem simples (um projeto SSIS no BIDS). Como haverá iteração pelas imagens (que na verdade são arquivos) é necessário utilizar um container do tipo ForEachLoop. As seguintes propriedades desse container devem ser configuradas:

Aba Propriedades
General Name: LoopBlobs
Description: Realiza um loop para ler os arquivos (Imagens)
Collection Enumerator: Foreach File Enumerator
Folder: G:\Imagens (supondo que as imagens estejam nesse local)
Files: *.jpg
Retrive File Name: Fully Qualified
Variable Mappings Variable: New Variable
    Container: LoopBlobs (ou ForEach Loop Container)
    Name: ImagemJPG
    Value Type: String
Index: 0

Um dos pré-requisitos para importar imagens através do DataFlow é ter um arquivo com a lista das imagens a serem importadas. Através do Container e do Script Task é possível construir essa lista dinamicamente ao invés de ter o arquivo preinformado. Uma tarefa do tipo Script Task deve ficar dentro do Container com as seguintes propriedades:

Aba Propriedades
General

Name: ListarArquivo
Description: Monta a lista de arquivos a serem importados

Script ScriptLanguage: Microsoft Visual Basic .NET
ReadOnlyVariables: User::ImagemJPG

O script deve ter o seguinte código (Design Script):

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()

        ‘ Carrega o arquivo G:\Imagens\ListaImagens.txt com o nome da Imagem
        Dim sw As New StreamWriter("G:\Imagens\ListaImagens.txt", True)
        sw.WriteLine(Dts.Variables("User::ImagemJPG").Value.ToString())
        sw.Close()
        sw = Nothing

        ‘ Conclui a tarefa com êxito
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Posteriomente um Data Flow deve ser adicionado e uma relação de precedência entre o Container e o Data Flow deve ser configurada. O Data Flow deve ter a propriedade Name mudada para ImportarImagens e a description como Importa as imagens listadas no arquivo. O container deve ser executado para que o arquivo seja criado. O Control Flow deve ter uma aparência semelhante à figura abaixo:

Na aba Data Flow, no painel Connection Manager, uma nova conexão deve ser realizada (New Flat File Connection):

Aba Propriedades
General

Connection Manager Name: ListaImagens
Description: Arquivo com a Lista de Imagens
FileName: G:\Imagens\ListaImagens.txt

Advanced Renomear a coluna Column 0 para Arquivo
Data Type: string [DT_STR]
OutputColumnWidth: 100

Posteriormente uma conexão (Data Flow Source) do tipo Flat File deve ser realizada conforme propriedades especificadas a seguir:

Aba Propriedades
Connection Manager

Flat File Connection Manager Name: ListaImagens

Columns External Column: Arquivo
Output Column: Arquivo
Properties Renomear o nome para Flat File Source para ArquivoImagens

O conteúdo do arquivo para qual a conexão foi configurada possui apenas o nome dos arquivos obtidos pelo Loop ForEach. A tabela criada necessita também do nome do país. É necessário adicionar mais essa coluna através de um componente Derived Column com as seguintes propriedades:

Aba Propriedades
Properties Name: ColunasAdicionais
Derived Column Transformation Editor

Derived Column Name: Pais
Derived Column: <add as new column>
Expression: REPLACE(RIGHT(Arquivo,LEN(Arquivo) – FINDSTRING(Arquivo,"\\",2)),".jpg","")
Data Type: string [DT_STR]
Length: 50

A gravação do conteúdo do arquivo (e não somente o seu nome) deve ser feita através de um componente Import Column que deve ter as seguintes propriedades configuradas:

Aba Propriedades
Component Properties Name: ImportaImagem
Input Columns Marcar apenas a coluna Arquivo
Input and Output Properties Import Column Output
    Criar uma nova column (Add Column)
    Name: Imagem
    Data Type: image [DT_IMAGE]
    (Anotar o LineageID (no meu caso é 432))

Import Column Input (Arquivo)
    Custom Properties
    – Marcar a propriedade FileDataColumnID com o valor do LineageID
    – Marcar a propriedade ExpectBOM para false

Uma vez que o fluxo de extração e transformação das imagens esteja pronto basta apenas carregá-las para o SQL Server. Para isso é necessário uma conexão de destino (OLEDB Destination) configurada para o banco e instância corretos. O Data Flow deve ficar semelhante à figura abaixo:

A execução do pacote faz a carga das imagens e uma consulta no SQL Server Management Studio demonstra que a mesma foi realizada, pois, a coluna Imagem está no formato hexadecimal.

Agora que a importação de imagens foi realizada com êxito pelo SSIS, é necessário adicionar uma tarefa para efetuar a exportação dessas imagens para o File System. Para não reimportar as imagens, um novo pacote deverá ser criado (chamei-o de ExpImagens). Esse pacote terá apenas uma tarefa do tipo Data Flow Task com o nome de ExportaImagens e a propriedade Description como Exporta as imagens do banco de dados.

Dentro do DataFlow, deve haver uma conexão ao banco de dados SQL Server através do OleDB Source Connection. Essa conexão deve ser configurada para acessar o banco de dados de Imagem na instância correta. A exportação exigirá que uma das colunas seja escolhida como nome dos arquivos de imagens. Para evitar que as imagens sejam sobrescritas é necessário mudar o nome do arquivo a ser utilizado como referência. Ao invés de alterar na base a coluna Arquivo para refletir o novo local, será utilizada uma transformação do tipo Derived Column conforme a especificação abaixo:

Aba Propriedades
Properties Name: LocalDestino
Derived Column Transformation Editor

Derived Column Name: Destino
Derived Column: <add as new column>
Expression: "G:\\Imagens\\" + [Pais] + "_Exportado.jpg"

Agora que o nome do arquivo de destino já está formado, é possível exportar as imagens através de uma transformação do tipo Export Column com as seguintes propriedades:

Aba Propriedades
Columns Extract Column: Imagem
File Path Column: Destino

Agora basta executar o pacote e conferir as imagens na pasta especificada. Haverá tanto a imagem original quanto a imagem exportada (possui o sufixo _Exportado no nome).

Esse não é um exemplo trivial de utilização do SSIS e possivelmente alguns terão dificuldades em seguir esse exemplo. A solução desse projeto SSIS (apenas na versão 2005) está disponível no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/browse.aspx/ProjetosSQLServer?uc=1&isFromRichUpload=1

Alguns ajustes no pacote são bem vindos. O diretório G:\Imagens está fixo no Script Task do Control Flow, no Connection Manager Flat File e na Derived Column antes da exportação. É bem possível que nem sempre se tenha um caminho fixo, ou ainda, que esse caminho seja conhecido antes da elaboração do pacote. É possível tornar esse parâmetro mais dinâmico com o uso de Expressions e variáveis (ou ainda recursos de Package Configurations). O exemplo pressupõe também que os arquivos estão apenas em uma pasta em algum driver raiz e por isso a função FINDSTRING funcionou corretamente para gerar o nome do país a partir do nome do arquivo. Se os arquivos ficarem em um nível de subpastas maior, a coluna país não será importada corretamente. É mais uma limitação que deve ser contornada.

Acho a solução do SSIS a mais recomendada para esse tipo de situação. Além de não onerar o servidor de banco de dados e de arquivos diretamente (supondo que o SSIS não estará em nenhum desses servidores), essa solução é mais expansível. Utilizei o exemplo com SQL Server, mas isso não significa que esse procedimento não possa ser utilizado com outros SGBDs já que o SSIS não é uma ferramenta exclusiva para SQL Server.

[ ]s,

Gustavo

Como importar e exportar imagens entre o SQL Server e o File System ? – Parte I

Bom Dia Pessoal,

Em projetos que envolvam o armazenamento de imagens, é natural que haja uma discussão muito comum entre desenvolvedores, DBAs, arquitetos, etc sobre o melhor local para armazenamento de imagens. É sem dúvida uma discussão muito pertinente, pois, não há uma solução ideal para todos os casos. Armazenar imagens no banco de dados como um BLOB, administrá-la no File System ou ainda utilizar um banco de dados não relacional são as alternativas mais comuns para essa atividade. Todas possuem vantagens, desvantagens e alguns contratempos associados. Em determinadas situações, a escolha de colocar as imagens no FileSystem pode parecer mais interessante que uma coluna VarBinary (ou Image), mas pode ser que com o tempo a melhor escolha se reverta. É factível que um projeto inicie o armazenamento em colunas do tipo Blob (Binary Large Objects) e posteriormente seja necessário revertê-las para o File System. O tipo de dados FILESTREAM do SQL Server 2008 torna esse processo muito transparente, mas como fazer para converter imagens armazenadas no banco de dados para o File System e vice versa quando esse tipo de dados não estiver sendo utilizado ? Como fazer para converter os diversos registros em imagens ou ainda carregar diversas imagens para o banco de dados ?

Para demonstrar como importar e exportar as imagens entre o SQL Server e o File System, utilizarei as imagens das bandeiras de alguns países (Alemanha, Brasil, China, Espanha, Estados Unidos e Inglaterra). As imagens podem ser obtidas facilmente na Internet, mas se necessário, disponibilizei as mesmas no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20090610%7C_Imagens.zip

Antes de prosseguir, também criarei um banco de dados, um usuário e uma tabela para armazenar as imagens conforme o script abaixo:

— Cria um banco de dados
CREATE DATABASE Imagens

— Cria um login para inserir as imagens
EXEC sp_addlogin ‘UsrImg’,‘pwdImg’

— Concede privilégios para o usuário
USE Imagens
GO
EXEC
sp_grantdbaccess ‘UsrImg’,‘UsrImg’
GO

— Cria uma tabela para armazenar as bandeiras
CREATE TABLE Bandeiras (Pais VARCHAR(50),
    Arquivo VARCHAR(100), Imagem IMAGE NOT NULL)

— Adiciona um valor Default (necessário para o TextCopy)
ALTER TABLE Bandeiras ADD CONSTRAINT
    DF_Imagem DEFAULT 0x0 FOR Imagem

— Concede as devidas permissões ao usuário UsrImg
GRANT SELECT, INSERT, UPDATE ON Bandeiras TO UsrImg

Esse script cria uma coluna do tipo IMAGE com o valor padrão 0x0 que é um valor binário. O tipo de dados IMAGE foi utilizado porque no SQL Server 2000 não está disponível o VARBINARY(MAX). As stored procedures sp_addlogin e sp_grantdbaccess também foram utilizadas, pois, no SQL Server 2000 os comandos CREATE LOGIN e CREATE USER não estão disponíveis. Em alguns aplicativos e APIs não é suportado um Blob com valor nulo e para evitar problemas é utilizado o valor default 0x0. Após a criação da tabela é chegada a hora de inserir os registros (as imagens serão inseridas em etapa posterior). Está sendo admitido que as imagens estão no diretório G:\Imagens.

INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Alemanha’,‘G:\Imagens\Alemanha.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Brasil’,‘G:\Imagens\Brasil.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘China’,‘G:\Imagens\China.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Espanha’,‘G:\Imagens\Espanha.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Estados Unidos’,‘G:\Imagens\EstadosUnidos.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Inglaterra’,‘G:\Imagens\Inglaterra.jpg’)

Importando imagens com o TextCopy

O TextCopy é um utilitário de linha de comando disponível no SQL Server 2000 para importar / exportar imagens sendo encontrado na pasta BINN da instância em questão (normalmente C:\Program Files\Microsoft SQL Server\MSSQL\Binn). Embora seja um executável simples, não é possível simplesmente copiá-lo. As instruções abaixo fazem o upload das imagens para o banco de dados. Elas devem ser executadas em um prompt de comando em um servidor onde o SQL Server 2000 esteja instalado (sugiro utilizar uma BAT).

E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\Alemanha.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Alemanha’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\Brasil.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Brasil’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\China.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘China’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\Espanha.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Espanha’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\EstadosUnidos.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Estados Unidos’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\Inglaterra.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Inglaterra’"

Alguns parâmetros do batch acima merecem ser explicados.

  • O nome do utilitário "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" está entre aspas duplas, pois, o nome do diretório possui espaços
  • O parâmetro /S informa o nome do servidor (no caso uma instância nomeada no servidor local)
  • O parâmetro /I informa o fluxo que trata-se de uma importação (Input) para o SQL Server
  • O parâmetro /D informa o nome do banco de dados (Imagens)
  • O parâmetro /T informa o nome da tabela (Bandeiras)
  • O parâmetro /C informa o nome da coluna (Imagem)
  • O parâmetro /F informa o nome do arquivo a ser carregado para a coluna Blob
  • O parâmetro –U corresponde ao usuário utilizado para conexão e o –P a senha utilizada (não é possível utilizar autenticação integrada)
  • O parâmetro –W estipula uma cláusula WHERE para encontrar o registro onde o arquivo irá ser carregado (apenas um registro por vez)

Esse conjunto de seis linhas fez o upload das imagens para o respectivo pais. Uma consulta à tabela Bandeiras pode mostrar que a coluna Imagem (Blob) foi alterada do valor 0x0 para alguma representação em hexadecimal. O script abaixo gera todas as instruções de exportação:

DECLARE
    @PathExe VARCHAR(80), @Srv VARCHAR(20), @BD VARCHAR(20),
    @Tab VARCHAR(20), @Col VARCHAR(20), @Usr VARCHAR(20),
    @Pwd VARCHAR(20), @Where VARCHAR(50), @cmd VARCHAR(200)

SET @PathExe = ‘"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe"’
SET @Srv = ‘.\SQL2005’
SET @BD = ‘Imagens’
SET @Tab = ‘Bandeiras’
SET @Col = ‘Imagem’
SET @Usr = ‘UsrImg’
SET @Pwd = ‘pwdImg’
SET @Where = ‘"WHERE Pais = ”?2”"’

SET @cmd = @PathExe + ‘ /S ‘ + @Srv + ‘ /I /D ‘ + @BD + ‘ /T ‘ + @Tab + ‘ /C ‘
SET @cmd = @cmd + @Col + ‘ /F ?1 /U ‘ + @Usr + ‘ /P ‘ + @Pwd + ‘ /W ‘ + @Where

SELECT REPLACE(REPLACE(@cmd,‘?1’,Arquivo),‘?2’,Pais) FROM Bandeiras

Exportando imagens com o TextCopy

Da mesma forma que o TextCopy possui o parâmetro /I para determinar que o fluxo se trata de uma importação (I – Input para o SQL Server) é possível definir o parâmetro /O determinado o fluxo de exportação (O – Output do SQL Server). Os mesmos comandos do batch anterior exportar as imagens para uma outra pasta.

E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\Alemanha.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Alemanha’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\Brasil.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Brasil’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\China.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘China’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\Espanha.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Espanha’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\EstadosUnidos.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Estados Unidos’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\Inglaterra.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Inglaterra’"

Esse conjunto de seis linhas fez o download das imagens do banco de dados para o diretório G:\Exp (cada imagem com o nome de seu respectivo país). Opcionalmente pode-se utilizar um script para gerar os comandos (no caso não alterei o destino final para G:\Exp).

DECLARE
    @PathExe VARCHAR(80), @Srv VARCHAR(20), @BD VARCHAR(20),
    @Tab VARCHAR(20), @Col VARCHAR(20), @Usr VARCHAR(20),
    @Pwd VARCHAR(20), @Where VARCHAR(50), @cmd VARCHAR(200)

SET @PathExe = ‘"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe"’
SET @Srv = ‘.\SQL2005’
SET @BD = ‘Imagens’
SET @Tab = ‘Bandeiras’
SET @Col = ‘Imagem’
SET @Usr = ‘UsrImg’
SET @Pwd = ‘pwdImg’
SET @Where = ‘"WHERE Pais = ”?2”"’

SET @cmd = @PathExe + ‘ /S ‘ + @Srv + ‘ /O /D ‘ + @BD + ‘ /T ‘ + @Tab + ‘ /C ‘
SET @cmd = @cmd + @Col + ‘ /F ?1 /U ‘ + @Usr + ‘ /P ‘ + @Pwd + ‘ /W ‘ + @Where

SELECT REPLACE(REPLACE(@cmd,‘?1’,Arquivo),‘?2’,Pais) FROM Bandeiras

Importando imagens com o comando OPENROWSET

A importação de dados baseada no TextCopy é um pouco rudimentar, pois, depende de um executável externo. Além de representar a necessidade de instalação do SQL Server (não é possível simplesmente copiar o executável e utilizá-lo), o fato de impossibilitar o uso da autenticação integrada é um desincentivo à sua utilização em conjunto com arquivos bat e outros executáveis. A partir do SQL Server 2005 é possível utilizar a instrução OPENROWSET para importar BLOBs para o banco de dados garantindo um pouco mais flexibilidade. O exemplo abaixo mostra como fazer isso:

— Verifica os registros
SELECT Pais, Arquivo, Imagem FROM Bandeiras

— "Zera" as imagens do banco
UPDATE Bandeiras SET Imagem = 0x0

— Carrega as imagens para uma tabela a parte
DECLARE @Bandeiras TABLE (Pais VARCHAR(50), Imagem IMAGE)

— Insere a Bandeira da Alemanha
INSERT INTO @Bandeiras
SELECT ‘Alemanha’, * FROM OPENROWSET(BULK N’C:\Imagens\Alemanha.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira do Brasil
INSERT INTO @Bandeiras
SELECT ‘Brasil’, * FROM OPENROWSET(BULK N’C:\Imagens\Brasil.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira da China
INSERT INTO @Bandeiras
SELECT ‘China’, * FROM OPENROWSET(BULK N’C:\Imagens\China.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira da Espanha
INSERT INTO @Bandeiras
SELECT ‘Espanha’, * FROM OPENROWSET(BULK N’C:\Imagens\Espanha.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira dos Estados Unidos
INSERT INTO @Bandeiras
SELECT ‘Estados Unidos’, * FROM OPENROWSET(BULK N’C:\Imagens\EstadosUnidos.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira da Inglaterra
INSERT INTO @Bandeiras
SELECT ‘Inglaterra’, * FROM OPENROWSET(BULK N’C:\Imagens\Inglaterra.jpg’, SINGLE_BLOB) As Document

— Atualiza a tabela de Bandeiras com base no País
UPDATE Bandeiras SET Imagem = tmp.Imagem
FROM Bandeiras As B
INNER JOIN @Bandeiras As tmp ON B.Pais = tmp.Pais

— Verifica os registros
SELECT Pais, Arquivo, Imagem FROM Bandeiras

Infelizmente o SQL Server 2005 não disponibiliza uma forma de exportar imagens via TSQL (o OPENROWSET apenas exporta). O TextCopy pode ser utilizado com o SQL Server 2005, mas será necessário que o TextCopy seja executado a partir de um Host que possua o SQL Server 2000, pois, esse utilitário não está no 2005. Embora o 2005 tenha flexibilizado a importação de imagens com o OPENROWSET as imagens tem de ser importadas uma por vez e é necessário conhecer previamente os arquivos a serem importados.

No próximo artigo demonstrarei algumas soluções do SQL Server mais robustas para transferir as imagens do File System para o SQL Server e vice-versa. Aos que pensarem em utilizar o TextCopy com a xp_cmdshell cabe um aviso. Enquanto o TextCopy faz uma importação ou exportação recursos do SQL Server estão sendo consumidos. Além dos recursos que a xp_cmdshell utiliza serem um pouco mais escassos que o Buffer Pool tradicional, acho incoerente pensar que uma consulta está sendo rodada mais lentamente porque o SQL Server (e não o servidor de aplicação) está importando arquivos. Não é interessante utilizar esse tipo de mecanismo para importar / exportar grandes quantidades de imagens. Se esse for o caso, o ideal é procurar as APIs de aplicação para fazê-lo (embora as soluções que apresentarei no próximo artigo também o façam). Alguns links úteis sobre o assunto:

How To Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/kb/258038/en-us

How To Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET
http://support.microsoft.com/kb/308042/en-us

[ ]s,

Gustavo

Usando o SSIS para importar várias planilhas de um mesmo arquivo Excel para o SQL Server

Boa Noite Pessoal,

Essa semana vou ministrar uma turma de Integration Services 2008 (SSIS). Aproveitando para revisar o material e ensaiando alguns scripts extras, é nítido o quanto ele é superior ao seu antecessor o Data Transformation Services (DTS). Estive conversando com um colega em um dos clientes que presto consultoria sobre o SSIS já que ele o utiliza bastante. Um dos desafios comuns que ele presencia (e estou certo que muitos outros) é a importação de planilhas excel.

Obviamente, importar os dados de uma planilha excel para um banco SQL Server (supondo que a planilha esteja estruturada) é sem dúvida algo muito simples de se fazer (tanto que existe um Wizard disponível para isso). O desafio não é a importação em si, mas o  fato de haver várias planilhas em um mesmo arquivo. Imagine um arquivo XLS (ou XLSX) com a relação de empregados de cada departamento. Supondo que exista um número de departamentos desconhecido, é fácil importar esses empregados, mas como fazer para que o pacote leia cada planilha de forma dinâmica ? E se fosse uma planilha com as cotações de vários ativos ? Como fazer para criar um pacote que importe os dados de cada ativo ? Pode-se criar um pacote que leia as planilhas de forma estática, mas e se novos departamentos (ou ativos) forem retirados ? E se novos surgirem ? Nesse caso, inevitavelmente o pacote irá apresentar erros e (ou) não irá importar todos os dados necessários.

Para demonstrar como fazer essa importação de forma dinâmica, utilizarei uma planilha fictícia com os dados de alguns produtos de alguns fornecedores. Essa planilha está disponível no link ao final do artigo juntamente com os projetos SSIS. A planilha possui a relação de produtos adquiridos de três fornecedores (Saraiva, Submarino e Cultura). O objetivo é importar as compras registradas dos três fornecedores dinamicamente, ou seja, o pacote SSIS não deve saber previamente quem são ou quanto são os fornecedores.

O script abaixo cria um banco de dados e uma tabela para receber os dados dos fornecedores no arquivo XLS.

— Cria o banco de dados
CREATE DATABASE Produtos

— Muda o contexto do banco de dados
USE Produtos;

— Cria a tabela de produtos
CREATE TABLE ProdutosAdquiridos (
    Produto VARCHAR(150), Categoria VARCHAR(50),
    Quantidade TINYINT, Preco SMALLMONEY,
    DataCompra SMALLDATETIME, Fornecedor VARCHAR(50))

O próximo passo é a criação de um pacote SSIS para a importação dos dados das planilhas no arquivo XLS. Os passos para criação do pacote são bem simples (um projeto SSIS no BIDS). Como haverá iteração pelas planilhas é necessário utilizar um container do tipo ForEachLoop. As seguintes propriedades desse container devem ser configuradas:

Aba Propriedades
General Name: LoopXls
Description: Realiza um loop para ler os nomes das planilhas
Collection Enumerator: Foreach ADO.NET Schema Rowset Enumerator
Connection: New Connection
    Connection
        Provider: Microsoft Jet 4.0 OLE DB Provider
        Database file name: Localize o arquivo Xls (é xls e não access)
        User name: Deixe em branco
        Password: Deixe em branco
    All
        Extended Properties: Excel 8.0;HDR=Yes
Schema: Tables
Variable Mappings Variable: New Variable
    Container: LoopXls (ou ForEach Loop Container)
    Name: PlanilhaXLS
    Value Type: String
    Value: Submarino$
Index: 2

O valor "Excel 8.0;HDR=Yes" é necessária para que o Provider saiba que se trata de um arquivo Excel e HDR significa que a primeira linha (HEADER) possui o nome das colunas. O valor "Submarino$" é necessário apenas para configurar a migração de dados já que existe uma planilha chamada "Submarino". Posteriormente ele não tem nenhum efeito na execução do pacote. Depois que o pacote estiver salvo, nem é necessário que exista um fornecedor "Submarino".

Dentro do container é preciso inserir uma tarefa DataFlow para migrar os dados das planilhas. No exemplo, a propriedade Name foi configurada para "Fornecedor" e a propriedade Description foi configurada para "Importa Dados". A figura abaixo mostra a exibição do container LoopXls e a tarefa DataFlow (apenas em Control Flow).

Após a configuração do container e na tarefa em Control Flow, é necessário configurar a tarefa "Fornecedor" em Data Flow. A primeira "Data Flow Source" desse "Data Flow" é o Excel Source. As seguintes propriedades devem ser configuradas:

Aba Propriedades
Connection Manager

OLE DB connection manager (New)
    Excel File Path: Localize o arquivo Xls
Data Access Mode: Table name or view name variable
Variable name: User::PlanilhaXLS

Properties Name: Planilha Excel

Como as planilhas não possuem uma coluna com o nome do fornecedor, não será possível importar diretamente o nome do fornecedor da mesma forma que as demais colunas. A informação do nome do fornecedor está no nome da planilha (no arquivo Excel) e no nome da variável (no SSIS). Para que a importação do nome do fornecedor possa ser feita, é preciso criar uma coluna com o valor da variável (sem o "$"). Isso pode ser feito com a Data Transformation Flow "Derived Column" que como saída da tarefa anterior deve possuir as seguintes propriedades:

Aba Propriedades
Derived Column Transformation Editor

Derived Column Name: Fornecedor
Derived Column: <add as new column>
Expression: REPLACE(@[User::PlanilhaXLS],"$","")
Data Type: string [DT_STR]
Length: 50
Code Page: 152 (ANSI – Latin I)

Properties Name: Coluna Fornecedor

Como existem certas diferenças entre os tipos do Excel e os tipos do SQL Server é necessário fazer algumas conversões antes de propriamente importar os dados. A Data Flow Transformation "Data Convertion" pode equalizar essas diferenças e permitir a importação. As seguintes propriedades devem ser configuradas:

Aba Propriedades
Data Conversion Transformation Editor

Input Column: Produto
Output Alias: ProdutoConvertido
Data Type: string [DT_STR]
Length: 150
Code Page: 1252 (ANSI – Latin I)

Input Column: Categoria
Output Alias: CategoriaConvertida
Data Type: string [DT_STR]
Length: 50
Code Page: 1252 (ANSI – Latin I)

Properties Name: Conversão de Colunas

Após a configuração das conversões necessárias é possível exportar os dados para um banco de dados SQL Server através de uma Data Flow Destination "OLE DB Destination":

Aba Propriedades
Connection Manager

OLE DB Connection Manager (Clique em New duas vezes)
    Provider: Native OLE DB\SQL Native Client
    Server Name: Digite o nome do servidor SQL
    Logon on to the server: Configure a autenticação
    Select or enter a database name: Produtos
Data Access mode: Table or View
Name of the table or the view:[dbo].[ProdutosAdquiridos]

Mappings

Input ColumnDestination Column
ProdutoConvertido – Produto
CategoriaConvertida – Categoria
Quantidade – Quantidade
Preco – Preco
DataCompra – DataCompra
Fornecedor – Fornecedor

Se tudo tiver sido configurado conforme o esperado, o Data Flow deve assemelhar-se à figura abaixo:

Agora que o pacote está devidamente configurado, basta executá-lo, conectar-se ao servidor SQL e verificar o resultado das importações. Caso algum problema tenha ocorrido, disponibilizo o projeto SSIS juntamente com a planilha para acompanhamento no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20090531%7C_ImportarXLS.zip

Agora é hora de dormir, pois, a aula é amanhã cedo. Aos que desejarem algo ainda mais avançado (vários arquivos e várias planilhas), sugiro a leitura do link abaixo:

How to: Loop through Excel Files and Tables by Using a Foreach Loop Container
http://technet.microsoft.com/en-us/library/ms345182.aspx

[ ]s,

Gustavo

Como importar arquivos XML para o SQL Server – Parte IV

Bom Dia Pessoal,

Chegando ao final da nossa série "Como importar arquivos XML" irei demonstrar uma outra alternativa para validar e importar arquivos XML para o SQL Server. No último artigo demonstrei como fazer essa importação através do CLR que juntamente com o comando OPENROWSET e o SSIS compõe as principais soluções para importar arquivos desse tipo.

Não é intenção minha esgotar todas as alternativas sobre esse assunto (certamente existem várias), mas para que a série fique de fato completa, hoje mostrarei como montar uma pequena aplicação em .NET para fazer essa carga. No passado os preços das ferramentas de ETL eram demasiadamente elevados (não que hoje sejam baratas) e quase sempre os SGBDs não forneciam um suporte vasto para cargas de arquivos (ainda não o fornecem, pois, não é esse seu principal objetivo). Nesse época era bastante comum a criação de pequenos aplicativos para realizar a carga de dados seja uma simples leitura de um arquivo texto até uma complexa validação de planilhas eletrônicas, arquivos DBase, etc. Hoje essa não é mais a prática dominante (nem tampouco a melhor na maioria dos casos), mas construir pequenos aplicativos ainda é uma alternativa a se considerar. Vou explorar essa possibilidade para carregar dados XML.

Seria muito simples transformar os códigos em CLR para uma aplicação .NET, mas dessa vez demonstrarei algumas possibilidades bem mais interessantes além de algumas classes que são bastante úteis nesse tipo de carga. Para tornar o aplicativo "agendável", não irei codificar uma aplicação desktop, mas sim em linha de comando. Isso possibilita utilizá-lo através de tarefas agendadas, chamá-los em arquivos .bat, etc mas por favor não coloquem a chamada desse aplicativo em uma xp_cmdshell (isso pode degradar sensivelmente o desempenho do SQL Server).

Irei utilizar os mesmos arquivos XML e XSD do artigo anterior. Caso eles não estejam disponíveis, recomendo dar uma lida no artigo para obtê-los.

Antes de postar o código, mostrarei as tarefas genéricas que a aplicação .NET irá realizar.

Leitura do arquivo de configuração

Criei um arquivo de configuração para tornar a parametrização do aplicativo mais fácil, o arquivo de configuração está localizado em G:\CLR\Config.xml e tem a seguinte estrutura:

<?xml version="1.0" encoding="iso-8859-1"?>
<Configuracoes>
 <Diretorio>G:\XML\CLR\</Diretorio>
 <ArquivoXSD>G:\XML\CLR\Filmes.xsd</ArquivoXSD>
 <ServidorSQL>SQLTest</ServidorSQL>
 <BancoSQL>Banco</BancoSQL>
</Configuracoes>

O objetivo desse arquivo é indicar o diretório onde os arquivos XML estão localizados, o local do arquivo de validação (XSD), o nome do servidor SQL onde os dados serão descarregados e o banco de dados nesse servidor onde os dados serão descarregados. Por padrão estou utilizando a autenticação integrada.

Criação das tabelas necessárias

Uma vez que o arquivo XSD descreva a estrutura de um arquivo XML, podemos utilizá-lo para gerar as tabelas necessárias para a carga. Esse é um ponto interessante, pois, as tabelas necessárias serão criadas a partir do XSD e não teremos que conhecer previamente os detalhes do arquivo XML para criar tabelas compatíveis. Com base no XSD, as tabelas serão criadas (algo semelhante foi feito quando fizemos o mapeamento usando o SSIS).

Carga dos arquivos XML

Todos os arquivos XML presentes no diretório especificado no arquivo de configuração serão validados conforme o arquivo XSD e carregados para o servidor e o banco especificados no arquivo de configuração.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Schema;
using System.Xml.XPath;

namespace CargaXML
{
  class Program
  {
    static void Main(string[] args)
    {
      // Captura o local do arquivo de configuracao
      string ArquivoConf = args[0];

      // Captura os parâmetros especificados no XML
      XmlDocument xDoc = new XmlDocument();
      xDoc.Load(ArquivoConf);

      // Coloca os parâmetros em variáveis
      string Diretorio = xDoc.SelectSingleNode(
        "/Configuracoes/Diretorio").InnerText;

      string ArquivoXSD = xDoc.SelectSingleNode(
        "/Configuracoes/ArquivoXSD").InnerText;

      string ServidorSQL = xDoc.SelectSingleNode(
        "/Configuracoes/ServidorSQL").InnerText;

      string BancoSQL = xDoc.SelectSingleNode(
        "/Configuracoes/BancoSQL").InnerText;

      Program p = new Program();

      // Tenta fazer a importação
      try
      {
        // Cria as tabelas necessárias
        p.CriarTabelasSQL(ArquivoXSD, ServidorSQL, BancoSQL);

        // Para cada arquivo Xml encontrado no diretório
        foreach (string Arq in p.Arquivos(Diretorio))
        {
          // Faz a carga do arquivo
          p.CarregarArquivoXML(Arq, ArquivoXSD, ServidorSQL, BancoSQL);
        }

        Console.WriteLine("Importação Finalizada");
        Console.WriteLine("Aperte alguma tecla para finalizar");
        Console.ReadLine();
      }
      catch (Exception e)
      {
        Console.WriteLine("Ocorreu um erro inesperado");
        Console.WriteLine(e.Message);
        Console.WriteLine("Aperte alguma tecla para finalizar");
        Console.ReadLine();
      }
    }

    public List<string> Arquivos (string Diretorio)
    {
      DirectoryInfo di = new DirectoryInfo(Diretorio);
      List<string> ArquivosRet = new List<string>();

      foreach (FileInfo f in di.GetFiles("*.xml"))
      {
        ArquivosRet.Add(f.FullName);
      }

      return ArquivosRet;
    }

    public string MTipDados (string TipoDados)
    {
      // Declara uma variável para mostrar o tipo final
      string TipoDadosSQL = "";

      switch (TipoDados)
      {
        case "System.String":
          TipoDadosSQL = "VARCHAR(100)";
          break;

        case "System.Int32":
          TipoDadosSQL = "INT";
          break;

        case "System.DateTime":
          TipoDadosSQL = "SMALLDATETIME";
          break;

        default:
          TipoDadosSQL = "VARCHAR(MAX)";
          break;
      }

      return TipoDadosSQL;
    }

    public void CriarTabelasSQL(string ArquivoXSD,
      string ServidorSQL, string BancoSQL)
    {
      // Cria uma conexão com o SQL Server
      SqlConnection cn = new SqlConnection();
      cn.ConnectionString = "Integrated Security=true;Server=" +
        ServidorSQL + ";Database=" + BancoSQL;
      cn.Open();

      // Carrega o XSD para um DataSet
      DataSet ds = new DataSet();
      ds.ReadXmlSchema(ArquivoXSD);

      // Para cada tabela existente no DataSet
      foreach (DataTable dt in ds.Tables)
      {
        // Cria uma variável para armazenar o comando
        StringBuilder strSQL = new StringBuilder(@"CREATE TABLE ");       

        // Adiciona o nome da tabela ao comando
        strSQL.Append(dt.TableName);
        strSQL.Append(" (ArquivoXML VARCHAR(50)");

        // Para cada coluna encontrada na uma tabela
        foreach (DataColumn dc in dt.Columns)
        {
          // Verifica o tipo de dados
          strSQL.Append(", ");
          strSQL.Append(dc.ColumnName);
          strSQL.Append(" ");
          strSQL.Append(MTipDados(dc.DataType.ToString()));
        }

        // Adiciona o parêntese
        strSQL.Append(")");

        // Dispara o comando
        SqlCommand cmd = cn.CreateCommand();
        cmd.CommandText = strSQL.ToString();
        cmd.ExecuteNonQuery();
      }

      // Fecha e elimina a conexão
      cn.Close();
      cn.Dispose();
    }

    public void CarregarArquivoXML(
        string ArquivoXML, string ArquivoXSD,
        string ServidorSQL, string BancoSQL)
    {
      // Carrega o esquema para montar o DataSet
      DataSet ds = new DataSet();
      ds.ReadXmlSchema(ArquivoXSD);

      // Abre uma conexão para enviar os dados
      SqlConnection cn = new SqlConnection();
      cn.ConnectionString = "Integrated Security=true;Server=" +
        ServidorSQL + ";Database=" + BancoSQL;
      cn.Open();

      // Carrega o arquivo XML para o DataSet
      ds.ReadXml(ArquivoXML, XmlReadMode.ReadSchema);

      // Para cada tabela encontrada no DataSet
      foreach (DataTable dt in ds.Tables)
      {
        // Adiciona uma coluna para o nome do arquivo
        DataColumn dc = new DataColumn("ArquivoXML",
          Type.GetType("System.String"), "", MappingType.Attribute);

        dc.DefaultValue = (string)ArquivoXML;
        dt.Columns.Add(dc);

        // Cria um DataAdapter para enviar os dados
        SqlDataAdapter da = new SqlDataAdapter(
          "SELECT * FROM " + dt.TableName, cn);

        SqlCommandBuilder scb = new SqlCommandBuilder(da);
        da.Update(ds, dt.TableName);

        Console.WriteLine("Tabela " + dt.TableName +
          " importada com êxito");
      }

      // Fecha e elimina a conexão
      cn.Close();
      cn.Dispose();

      Console.WriteLine("Arquivo " + ArquivoXML + " importado");
    }
  }
}

Caso não o Visual Studio Studio não esteja instalado, basta seguir os passos abaixo para compilar o executável:

  • Copie e cole o código em um arquivo .cs (Ex: G:\XML\cargaxml.cs)
  • Abra um prompt de comando
  • Navegue até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /out:cargaxml.exe G:\XML\cargaxml.cs (supondo que o arquivo esteja em G:\XML\)
  • Opcionalmente mover o arquivo cargaxml.exe para outro diretório (Ex: G:\XML\)
  • Executar o arquivo cargaxml.exe localizado no diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou o utilizado anteriormente)

Antes de executá-lo, vale a pena comentar alguns dos métodos do aplicativo:

  • Arquivos: Esse método recebe como parâmetro o nome de um diretório e retorna uma lista com todos os arquivos xml encontrados no diretório (não considera subdiretórios)
  • MTipDados: O XSD possui seus tipos de dados que sofrem uma conversão para os tipos do C#. Esses tipos não são exatamente os mesmos tipos do SQL Server. Esse método faz a troca dos tipos do C# para os compatíveis mais próximos no SQL Server. Contemplei apenas os tipos String, Int32 e DateTime. Outros tipos poderiam ser utilizados, mas não os coloquei porque nos arquivos utilizados só existem esses tipos de dados.
  • CriarTabelasSQL: Esse método carrega o esquema no arquivo XSD para um DataSet convertendo a estrutura XML para uma estrutura relacional. Como o XML é semi-estruturado, é possível (e provável) que um arquivo XML gere mais de uma tabela no DataSet. Para cada tabela presente no DataSet, é feito uma varredura em todas as colunas do DataSet para montar dinamicamente o comando de CREATE TABLE e executá-lo posteriormente. Adicionalmente, foi incluida uma coluna chamada ArquivoXML para incluir o nome do arquivo XML utilizado (veremos a utilidade dessa coluna posteriormente).
  • CarregarArquivoXML: Esse método carrega uma arquivo XML especificado para o DataSet. Adicionalmente para cada tabela dentro do DataSet é adicionada uma coluna contendo o nome do arquivo XML que está sendo carregado. Esse passo é necessário para que a tabela do DataSet possa ser mapeada diretamente para a tabela do banco de dados.

Como o diretório onde os arquivos estão localizados pode variar, o aplicativo dispõe de uma forma de configurá-los. Coloque o arquivo de configuração (config.xml) em um diretório apropriado e altere o elemento Diretorio para o diretório onde os arquivos XML (filmes01.xml, filmes02.xml, filmes03.xml). Faça as alterações necessárias para o elemento ArquivoXSD para mostrar o caminho completo do arquivo XSD. Posteriormente basta abrir um prompt de comando, navegar até a pasta onde o executável cargaxml.exe está presente e executar cargaxml.exe CaminhoCompletoDoArquivoDeConfiguracao.

Após rodar o executável, podemos ver que os dados foram importados com êxito. Para conferir se de fatos os dados foram carregados, podemos nos conectar ao servidor SQL Server e ao banco informados no arquivo de configuração. Foram criadas duas tabelas (Genero e Filme) com a estrutura abaixo

SELECT
    TABLE_NAME As Tabela, COLUMN_NAME As Coluna,
    ORDINAL_POSITION As Ordem, DATA_TYPE As Tipo,
    CHARACTER_MAXIMUM_LENGTH As Tamanho
FROM INFORMATION_SCHEMA.COLUMNS

Tabela

Coluna

Ordem

Tipo

Tamanho

Genero

ArquivoXML

1

varchar

50

Genero

Tipo

2

varchar

100

Genero

Genero_Id

3

int

NULL

Filme

ArquivoXML

1

varchar

50

Filme

DataPrevista

2

smalldatetime

NULL

Filme

Filme_text

3

varchar

100

Filme

Genero_Id

4

int

NULL

No arquivo XML temos os nós Tipo, DataPrevista e Filme. Durante a leitura dos dados, foi adicionada propositalmente uma coluna ArquivoXML para guardar o nome do arquivo XML a ser importado. A coluna Filme foi complementada com o sufixo "_text" porque "Filme" é o único nó do documento XML que não está em forma de atributo. Como a consulta XPath para retornar valores de elementos inclui a navegação (/text()), por padrão esse sufixo é incluso. Curiosamente apareceu a coluna Genero_ID na tabela Genero e na tabela Filme mesmo sem que a tenhamos especificado. Ela parece ser "desejada" já que liga os filmes aos gêneros, mas é preciso investigá-la um pouco mais para não haver um "erro" de interpretação.

Como o XML é uma representação hierárquica e o DataSet é uma representação relacional, é necessário utilizar alguns artifícios para que esse mapeamento possa ser feito. O arquivo xml especifica um gênero e os filmes daquele gênero são elementos apresentados logo após o gênero. Se essa estrutura em XML irá derivar duas tabelas (Genero e Filme) como saber a qual gênero pertence determinado filme se um arquivo pode conter vários gêneros e vários filmes ? Para tornar esse mapeamento possível, o framework .NET inclui silenciosamente essa coluna nas tabelas do DataSet e faz o devido relacionamento.

Embora possa parecer que a coluna Genero_ID seja de fato uma chave primária em Genero e uma chave estrangeira em Filme, é preciso ter muita cautela em assumir que isso seja verdadeiro (pois quase sempre não é). Vejamos uma simples consulta na tabela Genero:

SELECT ArquivoXML, Tipo, Genero_ID FROM Genero

Essa consulta produz o seguinte resultado:

ArquivoXML

Tipo

Genero_ID

G:\XML\CLR\Filmes01.xml

Ação

0

G:\XML\CLR\Filmes01.xml

Animação

1

G:\XML\CLR\Filmes02.xml

Comédia

0

G:\XML\CLR\Filmes02.xml

Romance

1

G:\XML\CLR\Filmes02.xml

Documentário

2

G:\XML\CLR\Filmes03.xml

Drama

0

G:\XML\CLR\Filmes03.xml

Ficção

1

G:\XML\CLR\Filmes03.xml

Policial

2

G:\XML\CLR\Filmes03.xml

Suspense

3

G:\XML\CLR\Filmes03.xml

Terror

4

Se for observado, a coluna Genero_ID não é um identificador único como poderia parecer. Os IDs 0 e 1 se repetem três vezes cada e o ID 2 duas vezes enquanto apenas os IDs 3 e 4 são exclusivos. Isso ocorre porque cada arquivo XML possui seus próprios gêneros e o framework .NET irá manter o relacionamento entre gêneros e filmes através do Genero_Id mas apenas para um arquivo XML por vez. Quando o próximo arquivo XML for carregado, o Genero_ID será zerado novamente. Isso significa que ele sozinho não pode ser utilizado como identificador já que ele é único por arquivo (esse mesmo comportamento ocorre com o SSIS).

Se o Genero_ID é único por arquivo e o nome do arquivo é conhecido, pode-se fazer um JOIN para obter a lista completa de filmes. Ex:

SELECT
    G.ArquivoXML, G.Tipo, F.Filme_text As Filme, DataPrevista
FROM
    Genero As G INNER JOIN Filme As F ON
        G.Genero_ID = F.Genero_ID AND
        G.ArquivoXML = F.ArquivoXML

Essa consulta possui o seguinte resultado (abreviado):

Arquivo XML

Gênero

Filme

Data Prevista

G:\XML\CLR\Filmes01.xml

Ação

Avatar

18/12/2009

G:\XML\CLR\Filmes01.xml

Ação

G.I. Joe: A Origem de Cobra

07/08/2009

G:\XML\CLR\Filmes01.xml

Ação

Street Fighter: The Legend of Chun-Li

13/03/2009

G:\XML\CLR\Filmes01.xml

Ação

Transformers 2

17/07/2009

G:\XML\CLR\Filmes01.xml

Animação

Coraline e o Mundo Secreto

13/02/2009

G:\XML\CLR\Filmes01.xml

Animação

Monstros vs. Alienígenas

03/04/2009

G:\XML\CLR\Filmes02.xml

Comédia

A Pantera Cor-de-Rosa 2

20/02/2009

G:\XML\CLR\Filmes02.xml

Comédia

Os Piratas do Rock

29/05/2009

G:\XML\CLR\Filmes02.xml

Romance

Ele Não Está Tão a Fim de Você

10/04/2009

G:\XML\CLR\Filmes02.xml

Romance

The Proposal

07/08/2009

G:\XML\CLR\Filmes02.xml

Documentário

Mamonas, o Documentário

06/03/2009

G:\XML\CLR\Filmes03.xml

Drama

Efeito Borboleta 3

15/05/2009

G:\XML\CLR\Filmes03.xml

Drama

Veronika Decide Morrer

01/05/2009

G:\XML\CLR\Filmes03.xml

Ficção

District 9

09/10/2009

G:\XML\CLR\Filmes03.xml

Ficção

The Box

17/04/2009

G:\XML\CLR\Filmes03.xml

Policial

Anjos e Demônios

15/05/2009

G:\XML\CLR\Filmes03.xml

Policial

Public Enemies

03/07/2009

G:\XML\CLR\Filmes03.xml

Suspense

Alma Perdida

27/02/2009

G:\XML\CLR\Filmes03.xml

Terror

Dia dos Namorados Macabro 3D

13/03/2009

G:\XML\CLR\Filmes03.xml

Terror

Sexta-Feira 13

20/02/2009

Se a mesma consulta for feita visualmente contra os arquivos XML, ou ainda se a solução baseada em CLR for utilizada (pois possui os mesmos arquivos), os resultados serão exatamente os mesmos, ou seja, a carga foi realizada com êxito.

O ponto forte dessa solução em relação as demais apresentadas é que ela não depende da criação prévia das tabelas no banco de dados. Não é necessário sequer conhecer o XSD para que a carga seja realizada. O próprio aplicativo encarrega-se de interpretar o arquivo XSD e derivar as tabelas necessárias para montar uma estrutura relacional equivalente. Ao realizar um teste com os arquivos XML de ofertas de produtos utilizado na solução do SSIS, a solução mostrou que a importação ocorre sem problemas.

Uma vez que o aplicativo não demande o conhecimento prévio das estruturas (seja relacional ou XML) ele está mais apto para a importação de documentos do que as soluções anteriores. Ainda assim ele não é um aplicativo completo. O tratamento de erro é precário, o mapeamento dos tipos do C# para o SQL Server está incompleto e não é permitido outra autenticação que não a integrada ao Windows. Isso foi proposital, pois, a idéia era demonstrar como fazer um pequeno aplicativo e não uma solução completa de ETL. Com essa alternativa, demonstro algumas das soluções comuns para lidar com a importação de arquivos XML para o SQL Server. Espero que importar os dados em XML para o SQL Server não seja mais uma dor de cabeça.

[ ]s,

Gustavo

Como importar arquivos XML para o SQL Server – Parte III

Olá Pessoal,

Dando continuidade a exportação de arquivos XML para o SQL Server, hoje demonstrarei mais uma forma de como fazer isso utilizando os recursos aplicados ao SQL Server 2005 e 2008. Já mostramos como fazer isso através de um código TSQL simples e através de uma solução mais robusta utilizando o SSIS. A próxima alternativa utiliza um recurso disponível nessas versões que é o SQLCLR (Common Language Runtime).

Já falei do SQLCLR em alguns artigos anteriores como Concatenando registros e Matrizes no SQL Server 2005 – Parte III. Há uma vasta bibliografia sobre assunto além de muitos artigos técnicos aprofundados. Caso haja interesse, há um artigo muito bom sobre esse assunto nos links abaixo:

Como e quando programar em CLR no Microsoft SQL Server 2005
http://www.microsoft.com/brasil/msdn/Tecnologias/sql/CLRSQLServer2005.mspx

O MSDN Experience também possui alguns vídeos sobre SQLCLR que são interessantes. Há mais informações sobre o MSDN Experience no link abaixo:

MSDN Experience SQL Server
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!158.entry

A utilização do SQLCLR provê uma excelente extensão para a parte de programação de banco de dados. Uma vez que as classes .NET estão disponíveis para utilização plena e integrada ao SQL Server, será possível construir stored procedures poderosas para realizar o trabalho de extração de dados. Poderíamos utilizar um function para recuperar o conteúdo do XML e posteriormente utilizar um comando TSQL para gravá-lo nas tabelas necessárias, mas essa solução seria muito semelhante à utilização do comando OPENROWSET apresentado em "Como importar arquivos XML para o SQL Server – Parte I" só que com muito mais esforço. Mostrarei algumas outras alternativas com o CLR.

Antes de demonstrar o código .NET que irá providenciar essa importação é necessário um exemplo para ser utilizado nas cargas. A Web não possui algumas listas de exemplo do nosso dia a dia, então pesquisando um pouco a respeito, consegui encontrar uma lista de filmes previstos para esse ano de 2009. Interessante que alguns eu não sabia nem ao menos que seria lançados (fiquei ansioso agora). Os seguintes arquivos XML serão utilizados como exemplo. Caso necessário altere os diretórios conforme a necessidade.

Arquivo 1 – G:\XML\CLR\Filmes01.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Filmes xmlns="
http://filmes.br/filmes">
 <Genero Tipo="Ação">
  <Filme DataPrevista="2009-03-20">Anjos da Noite: A Revolução</Filme>
  <Filme DataPrevista="2009-12-18">Avatar</Filme>
  <Filme DataPrevista="2009-08-14">G-Force</Filme>
  <Filme DataPrevista="2009-08-07">G.I. Joe: A Origem de Cobra</Filme>
  <Filme DataPrevista="2009-05-08">Star Trek</Filme>
  <Filme DataPrevista="2009-03-13">Street Fighter: The Legend of Chun-Li</Filme>
  <Filme DataPrevista="2009-07-17">Transformers 2</Filme>
  <Filme DataPrevista="2009-05-22">Uma Noite no Museu 2</Filme>
  <Filme DataPrevista="2009-06-05">Velozes e Furiosos 4</Filme>
  <Filme DataPrevista="2009-03-06">Watchmen – O Filme</Filme>
  <Filme DataPrevista="2009-04-30">X-Men Origins: Wolverine</Filme>
  <Filme DataPrevista="2009-04-09">Dragonball</Filme>
  <Filme DataPrevista="2009-06-05">O Exterminador do Futuro: A Salvação</Filme>
 </Genero>
 <Genero Tipo="Animação">
  <Filme DataPrevista="2009-02-13">Coraline e o Mundo Secreto</Filme>
  <Filme DataPrevista="2009-04-03">Monstros vs. Alienígenas</Filme>
 </Genero>
</Filmes>

Arquivo 2 – G:\XML\CLR\Filmes02.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Filmes xmlns="http://filmes.br/filmes">
 <Genero Tipo="Comédia">
  <Filme DataPrevista="2009-02-20">A Pantera Cor-de-Rosa 2</Filme>
  <Filme DataPrevista="2009-04-24">Eu Te Amo, Cara</Filme>
  <Filme DataPrevista="2009-02-06">Noivas em Guerra</Filme>
  <Filme DataPrevista="2009-06-12">NowhereLand</Filme>
  <Filme DataPrevista="2009-05-29">Os Piratas do Rock</Filme>
  <Filme DataPrevista="2009-02-20">Um Hotel Bom pra Cachorro</Filme>
 </Genero>
 <Genero Tipo="Romance">
  <Filme DataPrevista="2009-06-05">A Mulher Invisível</Filme>
  <Filme DataPrevista="2009-04-10">Delírios de Consumo de Becky Bloom</Filme>
  <Filme DataPrevista="2009-04-10">Ele Não Está Tão a Fim de Você</Filme>
  <Filme DataPrevista="2009-08-07">The Proposal</Filme>
 </Genero>
 <Genero Tipo="Documentário">
  <Filme DataPrevista="2009-03-06">Mamonas, o Documentário</Filme>
 </Genero>
</Filmes>

Arquivo 3 – G:\XML\CLR\Filmes03.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Filmes xmlns="http://filmes.br/filmes">
 <Genero Tipo="Drama">
  <Filme DataPrevista="2009-05-15">Efeito Borboleta 3</Filme>
  <Filme DataPrevista="2009-04-10">Hachiko: A Dog’s Story</Filme>
  <Filme DataPrevista="2009-05-01">Veronika Decide Morrer</Filme>
 </Genero>
 <Genero Tipo="Ficção">
  <Filme DataPrevista="2009-10-09">District 9</Filme>
  <Filme DataPrevista="2009-04-17">The Box</Filme>
 </Genero>
 <Genero Tipo="Policial">
  <Filme DataPrevista="2009-05-15">Anjos e Demônios</Filme>
  <Filme DataPrevista="2009-07-03">Public Enemies</Filme>
  <Filme DataPrevista="2009-04-24">State of Play</Filme>
 </Genero>
 <Genero Tipo="Suspense">
  <Filme DataPrevista="2009-02-27">Alma Perdida</Filme>
  <Filme DataPrevista="2009-04-17">Duplicidade</Filme>
  <Filme DataPrevista="2009-04-03">O Lobisomem</Filme>
 </Genero>
 <Genero Tipo="Terror">
  <Filme DataPrevista="2009-03-13">Dia dos Namorados Macabro 3D</Filme>
  <Filme DataPrevista="2009-05-29">O Mistério das Duas Irmãs</Filme>
  <Filme DataPrevista="2009-02-20">Sexta-Feira 13</Filme>
 </Genero>
</Filmes>

Para validar os arquivos com a lista de filmes, o schema abaixo será utilizado

Arquivo de validação – G:\XML\CLR\Filmes.xsd

<?xml version="1.0" encoding="iso-8859-1"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" targetNamespace="http://filmes.br/filmes">
 <xs:element name="Filmes">
  <xs:complexType>
   <xs:sequence>
    <xs:element maxOccurs="5" name="Genero">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="15" name="Filme">
        <xs:complexType>
         <xs:simpleContent>
          <xs:extension base="xs:string">
           <xs:attribute name="DataPrevista" type="xs:date" use="required"/>
          </xs:extension>
         </xs:simpleContent>
        </xs:complexType>
       </xs:element>
      </xs:sequence>
      <xs:attribute name="Tipo" type="xs:string" use="required"/>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

Dessa vez há algumas diferenças em relação aos arquivos XML e XSD anteriores. Nos arquivos XML há um atributo xmlns no root e no arquivo XSD há um atributo targetNamespace ambos apontando para um endereço (http://filmes.br/filmes) existe ainda um atributo chamado elementFormDefault com o valor qualified. Não entrarei nos detalhes do que significam esses atributos, mas por hora basta saber que para o Framework .NET validar arquivos XML é necessário que os arquivos XML tenham o atributo xmlns (namespace padrão) e o arquivo xsd tenha a tag targetNamespace que é o namespace a ser validado e que os elementos sejam declarados como qualificados (ainda que haja um namespace default). Poderíamos utilizar também o atributo xsi:nonamespaceschemalocation, mas preferi utilizar os anteriores. Vale a pena lembrar que o link é apenas um nome e que ele não necessita existir realmente.

O próximo passo é criar uma stored procedure utilizando as classes para se trabalhar com o CLR e o SQL Server. Como será feita uma extração de dados em XML, será necessário utilizar os namedspaces para trabalhar com o XML. O script abaixo é introdutório para entendermos o que a stored procedure se propõe a fazer. O código está repleto de comentários auto-explicativos.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.XPath;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void UspCarregarXml(string ArquivoXML)
  {
    // Captura o conteúdo do arquivo XML
    XmlDocument xd = new XmlDocument();
    xd.Load(ArquivoXML);

    // Cria uma referência para o root do documento
    XmlElement xmlElem = xd.DocumentElement;

    // Faz os ajustes para trabalhar com Namedspace
    XmlNamespaceManager xnm = new XmlNamespaceManager(xd.NameTable);
    xnm.AddNamespace("f", @"
http://filmes.br/filmes&quot;);

    // Para cada gênero encontrado no XML
    foreach (XmlNode xmlGenero in xmlElem.SelectNodes(@"/f:Filmes/f:Genero", xnm))
    {
      // Captura o gênero
      string strGenero = xmlGenero.Attributes[0].Value;

      // Para cada filme encontrado no gênero
      foreach (XmlNode xmlFilme in xmlGenero.SelectNodes(@"f:Filme", xnm))
      {
        // Captura o data prevista do filme
        string strDataFilme = xmlFilme.Attributes[0].Value;
        string strFilme = xmlFilme.InnerText;

        // Gera os comandos de INSERT
        string strSQL = @"INSERT INTO Filmes (Genero, DataPrevista, Nome)" +
          " VALUES (‘[0]’,'[1]’,'[2]’)";

        strSQL = strSQL.Replace(@"[0]", strGenero);
        strSQL = strSQL.Replace(@"[1]", strDataFilme);
        strSQL = strSQL.Replace(@"[2]", strFilme);

        // Dispara a saída para o SQL em formato texto (estilo PRINT)
        SqlContext.Pipe.Send(strSQL);
      }
    }
  }
}

Como não necessariamente o Visual Studio está disponível, mostrarei como compilar esse código seu utilizá-lo. Basta seguir os seguintes passos:

  • Copiar e colar o código no bloco de notas
  • Salvar o arquivo como UspGeraInsertsXML.cs em algum diretório (ex: G:\XML\CLR\)
  • Abrir um prompt de comando
  • Navegar até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /target:library G:\XML\CLR\UspGeraInsertsXML.cs (supondo que o arquivo esteja em G:\XML\CLR\)
  • Localizar o arquivo UspGeraInsertsXML.dll na pasta utilizada no prompt
  • Mover a DLL para um local mais apropriado (ex: G:\XML\CLR\)

O próximo passo é registrar o Assembly no SQL Server para que seus métodos possam ser utilizados (no caso a stored procedures).

— Habilita o uso do CLR na instância do SQL Server
EXEC sp_configure ‘clr enabled’, 1

— Força que as alterações tenham efeito sem a necessidade de reiniciar o serviço
RECONFIGURE WITH OVERRIDE

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY ImportarXML FROM ‘G:\XML\CLR\UspGeraInsertsXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS

Após tentar registrar o ASSEMBLY, uma mensagem de erro pode aparecer

Msg 10327, Level 14, State 1, Line 2
CREATE ASSEMBLY for assembly ‘UspGeraInsertsXML’ failed
    because assembly ‘UspGeraInsertsXML’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.
The assembly is authorized when either of the following is true:
   the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on;
   or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
   If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server.
   If not, use sp_changedbowner to fix the problem.

Normalmente um assembly é utilizado com o nível de permissão SAFE, ou seja, ele não acessará recursos externos ao SQL Server. Como será necessário importar alguns arquivos XML, será necessário acesso a recursos externos ao SQL Server e nesse caso a segurança fica um pouco mais comprometida senão há confiança no Assembly. Como o Assembly tem o seu código conhecido, será autorizada sua utilização através da configuração da propriedade Trustworthy do banco de dados.

— Altera a propriedade Trustworthy para ON
ALTER DATABASE BANCO SET TRUSTWORTHY ON

Agora o Assembly poderá ser registrado sem problemas (essa não é a melhor prática, mas evita a necessidade de se criar chaves e certificados para registrar o Assembly).

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY CarregarXML FROM ‘G:\XML\CLR\UspGeraInsertsXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS

— Cria uma stored procedure baseada no Assembly
CREATE PROCEDURE UspCarregarXML
    @ArquivoXML NVARCHAR(128)
AS
    EXTERNAL NAME
[CarregarXML].[StoredProcedures].UspCarregarXml

— Executa a Stored Procedure
EXEC UspCarregarXML @ArquivoXML = ‘G:\XML\CLR\Filmes02.xml’

Esse exemplo foi apenas uma "pincelada" no que o CLR pode ajudar em relação a importação de arquivos XML. Esse é um exemplo muito simples. O código em questão não contempla a validação dos documentos XML através de um XML Schema, não nos permite escolher a tabela de destino, e não carrega efetivamente o documento XML (apenas lista os comandos de INSERT). O próximo exemplo é um pouco mais elaborado.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Schema;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void UspImportarXml(
    string ArquivoXML, string ArquivoXSD,
    string TabelaDestino, string ColunasDestino)
  {
    // Cria um documento XML com base no Arquivo XML
    XmlDocument xd = new XmlDocument();

    // Cria uma conexão com o banco de dados usando o contexto atual
    SqlConnection cn = new SqlConnection("Context Connection=True");

    try
    {
      // Cria os objetos necessários para validação
      XmlReaderSettings xrs = new XmlReaderSettings();
      xrs.Schemas.Add(@"
http://filmes.br/filmes&quot;, ArquivoXSD);
      xrs.ValidationType = ValidationType.Schema;

      // Carrega o XML segundos os critérios de validação do XSD
      XmlReader xr = XmlReader.Create(ArquivoXML, xrs);
      xd.Load(xr);

      // Cria uma referência para o root do documento
      XmlElement xmlElem = xd.DocumentElement;

      // Faz os ajustes para trabalhar com Namedspace
      XmlNamespaceManager xnm = new XmlNamespaceManager(xd.NameTable);
      xnm.AddNamespace("f", @"
http://filmes.br/filmes&quot;);

      // Abre uma conexão com o banco usando o contexto atual
      cn.Open();

      // Instancia um comando para inserir os registros
      SqlCommand cmd = cn.CreateCommand();

      // Para cada gênero encontrado no XML
      foreach (XmlNode xmlGenero in xmlElem.SelectNodes(@"/f:Filmes/f:Genero", xnm))
      {
        // Captura o gênero
        string strGenero = xmlGenero.Attributes[0].Value;

        // Para cada filme encontrado no gênero
        foreach (XmlNode xmlFilme in xmlGenero.SelectNodes(@"f:Filme", xnm))
        {
          // Captura o data prevista do filme
          string strDataFilme = xmlFilme.Attributes[0].Value;
          string strFilme = xmlFilme.InnerText;

          // Gera os comandos de INSERT
          string strSQL = @"INSERT INTO " + TabelaDestino + "(" +
              ColunasDestino + ")" + " VALUES (‘[0]’,'[1]’,'[2]’)";

          strSQL = strSQL.Replace(@"[0]", strGenero);
          strSQL = strSQL.Replace(@"[1]", strDataFilme);
          strSQL = strSQL.Replace(@"[2]", strFilme);

          // Insere os registros no banco de dados
          cmd.CommandText = strSQL;
          cmd.ExecuteNonQuery();
        }
      }

      string strTotalRegistros = "Foram inseridos " +
        xmlElem.SelectNodes(@"/f:Filmes/f:Genero/f:Filme", xnm).Count.ToString() +
        " registro(s)";

      SqlContext.Pipe.Send(strTotalRegistros);
    }

    catch (Exception e)
    {
      SqlContext.Pipe.Send("Ocorreram problemas na importação do documento XML");
      SqlContext.Pipe.Send("Erro: " + e.Message);
      SqlContext.Pipe.Send("Origem: " + e.Source);
    }

    finally
    {
      // Fecha e elimina a conexão
      cn.Close();
      cn.Dispose();
    }
  }
}

Não irei entrar em detalhes do código (o mesmo está repleto de comentários). Para compilar o código na ausência do Visual Studio basta seguir os seguintes passos:

  • Copiar e colar o código no bloco de notas
  • Salvar o arquivo como UspImportarXML.cs em algum diretório (ex: G:\XML\CLR\)
  • Abrir um prompt de comando
  • Navegar até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /target:library G:\XML\CLR\UspImportarXML.cs (supondo que o arquivo esteja em G:\XML\CLR\)
  • Localizar o arquivo UspGeraInsertsXML.dll na pasta utilizada no prompt
  • Mover a DLL para um local mais apropriado (ex: G:\XML\CLR\)

O próximo passo é registrar o Assembly e criar a stored procedure conforme script abaixo (pressupondo que o Trustworth já está ativo):

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY ImportarXML
FROM ‘G:\XML\CLR\UspImportarXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS;

— Cria a stored procedure conforme o código
CREATE PROCEDURE UspImportarXml
    @ArquivoXML NVARCHAR(150),
    @ArquivoXSD NVARCHAR(150),
    @TabelaDestino NVARCHAR(50),
    @ColunasDestino NVARCHAR(100)
AS EXTERNAL NAME [ImportarXML].StoredProcedures.UspImportarXml

Antes de propriamente executar a stored procedure, é necessário criar uma tabela para receber os resultados.

— Declara uma tabela
CREATE TABLE Filmes (Genero VARCHAR(50),
    Data SMALLDATETIME, Nome VARCHAR(100))

— Importa os registros em XML validando-os
EXEC UspImportarXml
    @ArquivoXML = ‘G:\XML\CLR\Filmes01.xml’,
    @ArquivoXSD = ‘G:\XML\CLR\Filmes.xsd’,
    @TabelaDestino = ‘Filmes’,
    @ColunasDestino = ‘Genero, Data, Nome’

— Verifica o conteúdo da tabela
SELECT Genero, Data, Nome FROM Filmes

— Exclui a tabela
DROP TABLE Filmes

Um teste interessante para verificar se o XML está de fato "validando" o arquivo XML com base no arquivo XSD é mudar o XSD para provocar um problema de validação. Como o XSD especifica a tag <xs:element maxOccurs="5" name="Genero"> em outras palavras em cada arquivo XML podem haver apenas 5 gêneros no máximo. O arquivo Filme03.xml possui exatamente 5 gêneros (Drama, Ficção, Policial, Suspense e Terror). Se o elemento maxOccurs for reduzido para 4, o código abaixo irá gerar um erro.

— Declara uma tabela
CREATE TABLE Filmes (Genero VARCHAR(50),
    Data SMALLDATETIME, Nome VARCHAR(100))

— Importa os registros em XML validando-os
EXEC UspImportarXml
    @ArquivoXML = ‘G:\XML\CLR\Filmes03.xml’,
    @ArquivoXSD = ‘G:\XML\CLR\Filmes.xsd’,
    @TabelaDestino = ‘Filmes’,
    @ColunasDestino = ‘Genero, Data, Nome’

Após executar esse código, a mensagem de erro abaixo é exibida:

Ocorreram problemas na importação do documento XML
Erro: The element ‘Filmes’ in namespace ‘http://filmes.br/filmes&#8217; has invalid child element ‘Genero’ in namespace ‘http://filmes.br/filmes&#8217;.
Origem: System.Xml

Isso ocorre porque após mudar o atributo maxOccurs para 4, o número máximo de gêneros permitidos por arquivo será igual a 4. Como o arquivo 3 possui 5 gêneros, o último gênero é um elemento inesperado (invalid child element) dentro do elemento Filmes produzindo o erro. Isso mostra que os arquivos XML estão de fato sendo validados antes da importação. Se o maxOccurs voltara para 5, o código irá gravar os registros sem erros.

A sugestão pelo CLR é interessante uma vez que amplia bastante o poder do TSQL. Os códigos CLR demonstraram uma simples forma de carregar, mas de forma nenhuma esgotam as possibilidades (não coloquei um contexto transacional e nem proteções contra código SQL Injection por exemplo). Sugestões de melhoria incluem utilizar o método GetFiles de uma classe Directory para listar todos os arquivos do diretório e fazer a carga por diretório e não por arquivo ou quem sabe tentar incluir apenas os registros válidos para o Schema e rejeitar os inválidos. Tudo isso é possível com o CLR, mas fica como "dever de casa" para quem estiver interessado em investir nessa alternativa.

Como limitações, devemos lembrar que o CLR utilizará recursos do SQL Server. O CLR utiliza uma área de memória especial (e bem restrita) do SQL Server e não está incluída na área de memória do Buffer Pool. Essa área de memória especial é inferior a 2GB e é utilizada para manter planos de execução, propriedades de conexão, bloqueios, etc. Processar grandes documentos XML através do CLR pode levar a um rápido esgotamento dessa área provocando fortes contenções e prejudicando o desempenho do SQL Server. É recomendável uma certa cautela, pois, é muito tentador codificar em CLR.

Aos que desejarem utilizar arquivos em compartilhamento de rede não esqueçam que é necessário que a conta de serviço que roda o SQL Server tenha permissões de leitura no compartilhamento onde os arquivos estão localizados. Caso usuários não sysadmin sejam utilizados pode ser necessária a criação de uma credencial já que esses arquivos representam recursos externos ao SQL Server.

[ ]s,

Gustavo

Como importar arquivos XML para o SQL Server – Parte II

Bom Dia Pessoal,

No último artigo demonstrei uma forma de importar e validar arquivos XML para o SQL Server 2005 e 2008 através de comandos TSQL. É uma alternativa interessante, visto que o processo de carga pode ser automatizado através de simples comandos TSQL. Embora o SQL Server tenha melhorado significativamente o suporte a BLOBs e possibilitado a importação da maneira que foi apresentada anteriormente, não acho uma boa prática embutir dentro do Engine do SQL Server mecanismos de importação e validação de arquivos XML. A leitura de arquivos XML muito grandes ou em quantidade significativa pode sacrificar recursos de memória e disco e talvez não seja interessante comprometê-los principalmente se os arquivos forem carregados em um horário de forte concorrência.

Uma outra alternativa é a utilização do SSIS que é uma ferramenta mais preparada para lidar com cargas de dados e possui um bom suporte a importação de arquivos XML. Para demonstrar como fazer isso, utilizarei três arquivos XML que possuem ofertas de câmeras digitais. Os arquivos estão com vários elementos e atributos organizados de forma hierárquica. Os caminhos e o nome dos arquivos também são expostos, mas é possível alterá-los livremente desde que o projeto contemple os novos nomes de caminhos.

Arquivo 1 – G:\XML\Oferta01.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Oferta>
 <Produtos Ofertante="Submarino" Data="2009-01-24">
  <Produto>
   <Nome>Câmera Digital 5MP LCD 2</Nome>
   <Marca>Nikon</Marca>
   <Modelo>L10</Modelo>
   <Garantia>12</Garantia>
   <Preco>299.00</Preco>
  </Produto>
  <Produto>
   <Nome>Câmera Digital 8.2MP Easy Share</Nome>
   <Marca>Nikon</Marca>
   <Modelo>C813</Modelo>
   <Garantia>12</Garantia>
   <Preco>549.00</Preco>
  </Produto>
 </Produtos>
</Oferta>

Arquivo 2 – G:\XML\Oferta02.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Oferta>
 <Produtos Ofertante="Kabum" Data="2009-01-22">
  <Produto>
   <Nome>Câmera Digital Cyber-shot 7.2</Nome>
   <Marca>Sony</Marca>
   <Modelo>DSC-W110</Modelo>
   <Garantia>12</Garantia>
   <Preco>648.90</Preco>
  </Produto>
 </Produtos>
</Oferta>

Arquivo 3 – G:\XML\Oferta03.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Oferta>
 <Produtos Ofertante="Wal Mart" Data="2009-01-26">
  <Produto>
   <Nome>Camera Digital 7.1 MP</Nome>
   <Marca>Canon</Marca>
   <Modelo>A470</Modelo>
   <Garantia>12</Garantia>
   <Preco>499.00</Preco>
  </Produto>
  <Produto>
   <Nome>Câmera Digital 9.1MP</Nome>
   <Marca>Panasonic</Marca>
   <Modelo>DMC TZ5PL K</Modelo>
   <Garantia>12</Garantia>
   <Preco>1299.00</Preco>
  </Produto>
  <Produto>
   <Nome>Câmera Digital 10.0 Megapixels</Nome>
   <Marca>FujiFilm</Marca>
   <Modelo>S1000</Modelo>
   <Garantia>12</Garantia>
   <Preco>1349.90</Preco>
  </Produto>
 </Produtos>
</Oferta>

Para que esses dados possam ser carregados e validados é necessário um arquivo que especifique o esquema dos documentos XML.

Arquivo de Validação – G:\XML\Ofertas.xsd

<?xml version="1.0"?>
<xs:schema xmlns:xs="
http://www.w3.org/2001/XMLSchema">
<xs:element name="Oferta">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="Produtos">
     <xs:complexType>
      <xs:sequence>
       <xs:element minOccurs="1" maxOccurs="3" name="Produto">
        <xs:complexType>
         <xs:sequence>
          <xs:element name="Nome" type="xs:string"/>
          <xs:element name="Marca" type="xs:string"/>
          <xs:element name="Modelo" type="xs:string"/>
          <xs:element name="Garantia" type="xs:integer"/>
          <xs:element name="Preco" type="xs:decimal"/>
         </xs:sequence>
        </xs:complexType>
       </xs:element>
      </xs:sequence>
      <xs:attribute name="Ofertante" type="xs:string" use="required"/>
      <xs:attribute name="Data" type="xs:date" use="required"/>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
</xs:element>
</xs:schema>

Para que a carga dos arquivos XML possa ser realizada é necessário primeiro criar as tabelas de destino para receber os dados do XML. Essa etapa deve ser feita antes da carga de dados. Para efeitos de simplicidade já que o objetivo é extrair informações do XML (e não prover um roteiro completo de carga) criarei uma única tabela desnormalizada. As colunas textuais estão como NVARCHAR, pois, o XML trabalha os dados em UNICODE. Se as colunas forem do tipo VARCHAR ou CHAR, um erro de execução ocorrerá durante o pacote.

CREATE TABLE Ofertas (
    Ofertante NVARCHAR(50), Data SMALLDATETIME,
    NomeProduto NVARCHAR(80), Marca NVARCHAR(30),
    Modelo NVARCHAR(20), Garantia TINYINT,
    Preco SMALLMONEY
    )

Eu poderia utilizar o SSIS do SQL Server 2008 na demonstração, mas para manter a compatibilidade, utilizei o SSIS do SQL Server 2005 para a tarefa de importar os arquivos XML para dentro do SQL Server. Vale a pena lembrar que para esse exemplo não há diferenças entre o SSIS 2005 e o 2008. O primeiro passo naturalmente é criar um novo projeto no SSIS através do BIDS (Business Intelligence Development Studio) do tipo Integration Services Project.

Como a importação será de vários arquivos, é necessário utilizar um Container do tipo ForEachLoopContainer, pois, faremos um loop até que todos os arquivos XML sejam carregados através de uma tarefa Data Flow. As seguintes propriedades devem estar preenchidas com os respectivos valores (feche e abra as propriedades do objeto antes de definir Variable Mapping):

Objeto

General

Collection

Variable Mapping

ForEachLoop Container

Name: VarrerArquivosXML

Enumerator: For Each File Enumerator
Folder: G:\XML
Files: *.xml
Retrieve File Name: Fully Qualified

Variable: User::ArquivoXML
Index: 0
Crie um nova variável (New Variable)

    Container: VarrerArquivosXML
    Name: ArquivoXML
    NameSpace: User
    Value Type: string
Data Flow

Name: ImportarArquivosXML

O resultado final deve ser algo semelhante a figura abaixo:

A parte de ControlFlow está finalizada. Já possuímos um container que irá varrer todos os arquivos .xml de uma determinada pasta e o nome do arquivo ficará disponível em uma variável chamada User::ArquivoXML. O próximo passo é montar a parte de Data Flow.

Como a fonte de dados é XML, é necessário utilizar o XML Source para capturar os dados. As seguintes propriedades na guia Connection Manager devem ser configuradas (opcionalmente troque a propriedade Name para Produtos em Properties):

  • Data Access Mode: XML File From Variable
  • Variable Name: User::ArquivoXML
  • XSD Location: G:\XML\Ofertas.xsd

Como o XML é hierárquico, ao clicar na guia Columns, no momento de mapear os dados aparecerão duas saídas (Output Name) conforme a figura abaixo:

Isso ocorre porque o XML em questão possui múltiplos elementos e atributos e portanto múltiplas saídas são geradas. Existem basicamente dois conjuntos de resultados. O primeiro é "Produto" que contém as características do produto (Nome, Marca, Modelo, Garantia e Preço). O segundo é "Produto" que contém o nome do ofertante e a data da oferta. Como existe apenas uma tabela para receber esses dados será necessário combinar essas saídas para carregar a tabela de Ofertas.

Em ambas as saídas (na própria guia Columns), há uma coluna chamada Produtos_ID que não está presente no XML. Essa coluna é criada pelo SSIS é através dela que é feito o Link entre as saídas "Produto" e "Produtos". Utilizaremos essa coluna logo em seguida para fornecer uma única saída através da transformação MERGE JOIN.

Antes de prosseguir há uma tarefa que se faz necessária em relação à transformação MERGE JOIN. Essa transformação requer que os dados estejam ordenados para funcionar. Para que possamos ordenar as saídas "Produto" e "Produtos" é necessário seguir os seguintes passos:

  • Clicar sobre produtos com o botão direito e em seguida (Show Advanced Editor)
  • Ir até a aba Input and Output Properties
  • Clicar sobre a saída Produto e marcar a propriedade IsSorted como True
  • Expandir a saída Produto, Output Columns e em seguida clicar Produtos_ID
  • Marcar a propriedade SortKeyPosition como 1
  • Repetir o mesmo processo para a saída "Produtos"

As figuras abaixo podem ser úteis para realizar o processo

Configuração da Propriedade IsSorted

Configuração da Propriedade SortKeyPosition

O próximo passo é adicionar a transformação MERGE JOIN para realizar a junção das saídas "Produto" e "Produtos". Após adicionar a transformação MERGE JOIN é necessário ligar as saídas da origem XML Source (ou Produtos) para a transformação MERGE JOIN através da seta verde em XML Source (Produtos). Como são duas saídas, a seta terá de ser ligada duas vezes.

Ao fazer a ligação entre XML Source (Produtos) e MERGE JOIN aparecerá uma caixa de diálogo com as opções OUTPUT e INPUT. Escolha como OUTPUT a opção "Produto" e como INPUT a opção "Merge Join Right Input". Faça uma nova ligação entre Xml Source (Produtos) e MERGE JOIN (nada acontecerá, mas ficarão duas setas verdes). Posteriormente dê dois clique sobre MERGE JOIN. Uma tela muito similar ao Query Designer aparecerá:

Marque as colunas de "Produtos" e em seguida de "Produto" para que todas as colunas do XML possam ser contempladas. Troque o Output Alias de Nome para NomeProduto, pois, esse é o nome da coluna na tabela (essa tarefa não é obrigatória, mas diminui necessidades de mapeamento). Posteriormente basta adicionar uma fonte de dados de destino (OleDB Destination), configurá-la para conectar-se ao SQL Server (no banco e tabela criados) e ligar a transformação MERGE JOIN na fonte que conecta-se ao SQL Server. Algo parecido com a figura abaixo:

Existem um aviso (Warning) em OLEDB Source Destination (eu renomeie para SQL Server) relacionado ao tamanho das colunas "Truncation may occur due…". Essa mensagem aparece porque os dados que foram definidos no esquema (Ofertas.xsd) define apenas que algumas colunas são do tipo xs:string sem delimitação (situação típica de esquemas que foram gerados através de ferramentas). Com algumas alterações no esquema esse aviso desaparecerá (embora ele não impede que a carga seja executada). Se o pacote for executado, a tabela Ofertas será populada e poderá ser consultada após a carga.

A carga de documentos XML através do SSIS pode parecer um pouco mais trabalhosa, mas vale a pena lembrar que as capacidades são bem maiores já que se trata de uma ferramenta adaptada para carga de dados. É possível carregar documentos XML não só para bases SQL Server, como ORACLE, DB2, etc. Com a diversidade de tarefas existentes nas abas Control Flow e Data Flow há a possibilidade de excluir os arquivos após processamento (recomendável para os casos em que a importação ocorrer com êxito), de comparar o conteúdo do arquivo XML com o existente no banco de dados, combinar os dados XML com outras fontes antes de carregá-los, etc. Essas possibilidades tornam o SSIS uma excelente alternativa para importar arquivos XML para o SQL Server.

Embora o SSIS tenha muitos recursos, tenho apenas duas ressalvas quanto a sua utilização para importação de dados a partir de documentos XML. A primeira ressalva é que o esquema (Oferta.xsd) não valida de fato os documentos XML a serem importados. Ele serve para mapear a estrutura XML em Rowsets para posterior tranformação ou carga, mas a validação não ocorre efetivamente. No arquivo em questão, colocou-se a restrição maxOccurs = 3 para elementos do tipo produto. O arquivo Ofertas03.xml tem a relação de três produtos. Se a restrição for diminuída para 2, por exemplo, o arquivo Oferta03.xml deveria apresentar erros, pois, viola o esquema definido, mas isso não ocorre. A segunda ressalva (e talvez mais grave) é que a importação de arquivos xml mais complexos necessita do operador MERGE JOIN para combinar os rowsets. Como o operador MERGE JOIN está limitado a combinar duas fontes de dados (Left e Right), caso o XML produza mais de dois rowsets, o operador MERGE JOIN não poderá ser utilizado para extrair todo o conteúdo do XML. Nesse caso será necessário prover os dados em um formato com menos rowsets ou aplicar uma transformação prévia do tipo XSLT. Embora o SSIS possa fazê-lo, isso, pode limitar seu desempenho para arquivos XML muito grandes.

Para os iniciantes em SSIS e aqueles que tiveram pouco contato com essa ferramenta, alguns pontos desse artigo podem ficar um pouco confusos. Para facilitar o entendimento, disponibilizei o projeto e os arquivos necessários em minha pasta pública. Há duas versões do pacote (uma para 2005 e uma para 2008). Talvez seja necessário fazer os devidos ajustes nos diretórios. Nos pacotes em anexo, o XML Schema utilizado já contempla o tamanho máximo e o Warning será ocultado. Os interessados podem navegá-la ou clicar no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/ImportarXMLSSIS.zip

[ ]s,

Gustavo

Como importar arquivos XML para o SQL Server – Parte I

Olá Pessoal,

Há uma certa dúvida que presencio já algum tempo e com uma certa freqüência. Eu nunca havia parado para abordá-la da forma que fato rotineiramente, mas recentemente um de meus ex-alunos (na verdade é uma aluna) dos cursos de SQL Server me abordou sobre essa dúvida. A dúvida consistia em como importar arquivos XML para o SQL Server 2000. Havia dois tipos de arquivos (xml e xsd). Os arquivos XML consistem nos dados a serem importados e os arquivos xsd (Xml Schema Definition) consistem nos descritores dos arquivos XML utilizados para validação.

O SQL Server 2000 tem um suporte a XML bem limitado e isso é perfeitamente compreensível. Estamos falando de uma release de quase uma década atrás e na ocasião do lançamento o XML estava bastante distante de ser o que é hoje. Alguns padrões como XPath, Xml Schema e XQuery (indispensáveis para trabalhar com XML hoje) tornaram-se recomendação oficial praticamente no ano 2000 e nessa época o SQL Server 2000 já estava "pronto" (o primeiro Beta data de outubro de 1999). Não era de se esperar que o suporte no SQL Server 2000 contemplasse todos esses padrões (A XQuery e a XPath 2.0 só foram finalizadas em 2007).

Embora o SQL Server 2000 não disponibilize nenhum meio nativo (ou pelo menos mais simples) de importar arquivos XML, existem algumas alternativas. Pode-se utilizar a tarefa Parallel Data Pump do DTS (não disponível graficamente), partir para a stored procedure sp_xml_preparedocument ou utilizar as stored procedures sp_OACreate para instanciar componentes COM+ e utilizar bibliotecas mais preparadas para o XML. Nenhuma dessas alternativas é fácil de trabalhar e algumas delas limitam muito o tamanho do XML em questão além de não serem nada performáticas. Há outras alternativas relacionadas ao ADO e ao XML Bulk Load, mas também não são muito triviais de se trabalhar.

Uma das funcionalidades mais poderosas que o SQL Server disponibilizou a partir do SQL Server 2005 na minha opinião foi um suporte melhor para o XML. Digo um suporte melhor, porque embora o SQL Server 2005 seja muito superior ao 2000 no que tange a XML, ele ainda não conseguiu oferecer todo o suporte necessário pelas mesmas razões do SQL Server 2000 (data de lançamento anterior ao amadurecimento dos padrões). Em todo caso, o SQL Server 2005 já cobre muitas das necessidades comuns que envolvem o XML e na versão 2008 esse suporte foi ainda mais trabalhado e de fato está muito próximo das possibilidades oferecidas pelo W3C.

Conversando com minha ex-aluna, ela me disse que possui o SQL Server 2005 em seu ambiente. Combinei com ela de demonstrar como fazer a importação de dados em XML para o 2005, pois, se ela tiver êxito, poderá exportar os dados em formato tabular do SQL Server 2005 para o 2000 usando o DTS ou o SSIS. Me dedicarei nos próximos artigos a demonstrar que mecanismos o SQL Server 2005 e o SQL Server 2008 possuem para importar arquivos XML.

Antes de prosseguirmos, utilizarei um arquivo XML de estrutura bem simples localizado em C:\CategoriasDados.xml (o caminho pode ser alterado se necessário).

<?xml version="1.0" encoding="ISO-8859-1"?>
<Categorias>
  <Categoria Nome="Informática e Acessórios">
    <Produto Nome="MP5 Player" Marca="Image" Valor="369.90"/>
    <Produto Nome="Notebook NR350 Core 2 Duo" Marca="Sony" Valor="2999.99"/>
    <Produto Nome="Macbook Core 2 Duo" Marca="Apple Brasil" Valor="3999.99"/>
    <Produto Nome="Roteador Wireless WRT54G-LA" Marca="Linksys" Valor="199.00"/>
  </Categoria>
  <Categoria Nome="CDs">
    <Produto Nome="Seal – Soul" Marca="Warner" Valor="38.90"/>
    <Produto Nome="Sem Limite: Roupa Nova- Duplo" Marca="Universal" Valor="16.90"/>
    <Produto Nome="Originals: Bob Marley" Marca="Neo" Valor="12.90"/>
  </Categoria>
  <Categoria Nome="Vinhos, Bebidas e Comidas">
    <Produto Nome="Chocolate Recheado c/ Capuccino 120g" Marca="Kron" Valor="9.90"/>
    <Produto Nome="Whisky 18 Anos" Marca="Buchanans" Valor="449.00"/>
    <Produto Nome="Vinho Tinto Afincado" Marca="Moet Hennessy" Valor="269.00"/>
    <Produto Nome="Salada de Alho Espanhola" Marca="Falani" Valor="3.90"/>
  </Categoria>
</Categorias>

O XSD para validação a ser utilizado é descrito abaixo e está localizado em C:\CategoriasValidacao.xsd (o caminho pode ser alterado se necessário).:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="
http://www.w3.org/2001/XMLSchema">
  <!– Criação do tipo produto –>
  <xsd:complexType name="tProduto">
    <xsd:attribute name="Nome" type="xsd:string" use="required"/>
    <xsd:attribute name="Marca" type="xsd:string" use="required"/>
    <xsd:attribute name="Valor" type="xsd:decimal" use="required"/>
  </xsd:complexType>
  <!– Criação do tipo Categoria –>
  <xsd:complexType name="tCategoria">
    <xsd:sequence>
      <xsd:element name="Produto" type="tProduto" minOccurs="1" maxOccurs="4"/>
    </xsd:sequence>
    <xsd:attribute name="Nome" type="xsd:string" use="required"/>
  </xsd:complexType>
  <!– Criação do tipo Categorias –>
  <xsd:complexType name="tCategorias">
    <xsd:sequence>
      <xsd:element name="Categoria" type="tCategoria" minOccurs="1" maxOccurs="3"/>
    </xsd:sequence>
  </xsd:complexType>
  <!– Declaraçao de um elemento de Categorias –>
  <xsd:element name="Categorias" type="tCategorias"/>
</xsd:schema>

O suporte a carga de BLOBs (incluindo o XML) foi bastante melhorado nas versões 2005 e 2008. Anteriormente, para carregar um blob diretamente via Query era praticamente impossível. A única alternativa era utilizar o utilitário TextCopy.exe ou confiar nas APIs da aplicação para fazer isso. Já no SQL Server 2005 e 2008 contamos com a instrução OPENROWSET com suporte a importação de BLOBs. Embora o comando OPENROWSET seja bloqueado por padrão não é necessário habilitá-lo para que a importação de BLOBs possa funcionar. O comando abaixo faz a leitura do arquivo C:\CategoriasDados.xml

SELECT BulkColumn FROM
OPENROWSET
(BULK N‘C:\CategoriasDados.xml’, SINGLE_BLOB)
AS Arquivo

O código exibido pela instrução é binário e embora a extensão seja XML, o SQL Server não está a par disso. É necessário converter o valor para um tipo válido. A conversão pode ser em XML ou VARCHAR(MAX). Como estamos falando de XML, a conversão para XML é mais indicada. O comando abaixo faz a leitura corretamente.

SELECT CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK N‘C:\CategoriasDados.xml’, SINGLE_BLOB)
AS Arquivo

Antes de pensarmos em carregar alguma tabela com o conteúdo do XML, devemos verificar se o mesmo é válido. Para isso, é necessário importar o conteúdo do XSD e criar um esquema de validação através de objetos XML Schema Collection. O script abaixo, importa o conteúdo do XSD (que também é um XML) e cria um esquema de validação.

— Importa o XSD para uma variável
DECLARE @XSD XML
SET @XSD = (
    SELECT CAST(BulkColumn AS XML)
    FROM OPENROWSET(BULK N‘C:\CategoriasValidacao.xsd’, SINGLE_BLOB)
    AS Arquivo)

— Cria um novo schema de validação
CREATE XML SCHEMA COLLECTION xsdProdutos AS @XSD

Agora que há um esquema de validação de XML, poderemos visualizar se o arquivo XML é ou não válido através do objeto XML Schema Collection. Para tornar o exemplo um pouco mais interessante, vamos editar o XML para provocar propositalmente um erro de validação. A primeira tag <Categoria> deve ser substituídas por <Cat> deixando o XML da seguinte forma:

<?xml version="1.0" encoding="ISO-8859-1"?>
<Categorias>
  <Cat Nome="Informática e Acessórios">
    <Produto Nome="MP5 Player" Marca="Image" Valor="369.90"/>
    <Produto Nome="Notebook NR350 Core 2 Duo" Marca="Sony" Valor="2999.99"/>
    <Produto Nome="Macbook Core 2 Duo" Marca="Apple Brasil" Valor="3999.99"/>
    <Produto Nome="Roteador Wireless WRT54G-LA" Marca="Linksys" Valor="199.00"/>
  </Cat>
  <Categoria Nome="CDs">
    <Produto Nome="Seal – Soul" Marca="Warner" Valor="38.90"/>
    <Produto Nome="Sem Limite: Roupa Nova- Duplo" Marca="Universal" Valor="16.90"/>
    <Produto Nome="Originals: Bob Marley" Marca="Neo" Valor="12.90"/>
  </Categoria>
  <Categoria Nome="Vinhos, Bebidas e Comidas">
    <Produto Nome="Chocolate Recheado c/ Capuccino 120g" Marca="Kron" Valor="9.90"/>
    <Produto Nome="Whisky 18 Anos" Marca="Buchanans" Valor="449.00"/>
    <Produto Nome="Vinho Tinto Afincado" Marca="Moet Hennessy" Valor="269.00"/>
    <Produto Nome="Salada de Alho Espanhola" Marca="Falani" Valor="3.90"/>
  </Categoria>
</Categorias>

O script abaixo tenta importar os dados e verificar se o  XML é válido de acordo com a XML Schema Colletion xsdProdutos definida com base no arquivo XSD.

— Importa o XML para uma variável fazendo validação
DECLARE @XML XML (xsdProdutos)
SET @XML = (
    SELECT CAST(BulkColumn AS XML)
    FROM OPENROWSET(BULK N‘C:\CategoriasDados.xml’, SINGLE_BLOB)
    AS Arquivo)

Após rodar essa instrução, um erro de validação é gerado:

Msg 6965, Level 16, State 1, Line 3
XML Validation: Invalid content. Expected element(s):Categoria where element ‘Cat’ was specified. Location: /*:Categorias[1]/*:Cat[1]

Esse erro é apresentado porque no esquema de validação, informou-se que o elemento "Categorias" deve possuir elemento "Categoria" e o elemento Cat não foi especificado fazendo com que o conteúdo do documento XML fosse considerado inválido. Se o XML for alterado para sua forma original, o script anterior será executado sem problemas.

Agora que a validação do XML foi realizada o passo final é desserializar o XML e convertê-lo para um formato relacional (Shredding). No SQL Server 2005 e 2008 poderíamos fazer esse trabalho através do OPENXML já que no 2005 e 2008 esse método também suporta o tipo XML. Entretanto, acho que o método Nodes é mais eficiente por dispensar o uso de ponteiros que o OPENXML utiliza, além de ser mais fácil. O script abaixo faz a recuperação das categorias e de seus produtos.

— Importa o XSD para uma variável fazendo validação
DECLARE @XML XML (xsdProdutos)
SET @XML = (
    SELECT CAST(BulkColumn AS XML)
    FROM OPENROWSET(BULK N‘C:\CategoriasDados.xml’, SINGLE_BLOB)
    AS Arquivo)

— Mostrar as categorias
SELECT Cats.Cat.value(‘@Nome’,‘nvarchar(80)’) AS Categorias
FROM @XML.nodes(‘/Categorias/Categoria’) AS Cats(Cat)

— Mostrar os produtos e as categorias
SELECT
    Cats.Cat.value(‘string(../@Nome)’,‘nvarchar(80)’) AS Categoria,
    Cats.Cat.value(‘@Nome’,‘nvarchar(80)’) AS Nome,
    Cats.Cat.value(‘@Marca’,‘nvarchar(30)’) AS Marca,
    Cats.Cat.value(‘@Valor’,‘smallmoney’) AS Valor
FROM @XML.nodes(‘/Categorias/Categoria/Produto’) AS Cats(Cat)

Uma vez que seja possível extrair os dados a partir do XML, bastaria uma simples instrução INSERT para copiar esses dados para suas tabelas de destino (possivelmente uma tabela de categorias e uma tabela de produtos). No caso da tabela de produtos, um JOIN talvez seja necessário para retornar o código da categoria e não o seu nome. Alguns mais familiarizados com a XQuery podem perguntar qual a finalidade da função string definida na recuperação do nome da categoria. Ela é necessário porque o XML foi tipado com base no esquema xsdProdutos e caso a mesma seja omitida a instrução não funciona. A função string poderia ser omitida somente se o XML não fosse tipado.

As instruções TSQL utilizadas também podem utilizar diretórios de rede desde que a conta de serviço do SQL Server tenha permissões de leitura no diretório desejado. A utilização de diretórios na rede e compartilhamentos evita que os arquivos tenham de ser descarregados diretamente no servidor do SQL Server. Para usuários que não sejam sysadmin pode ser necessário criar uma credencial, pois, arquivos XML são recursos externos ao SQL Server.

Essa solução baseou-se no tipo de dados XML e nos comandos TSQL. As limitações desse método estarão diretamente relacionadas à limitação desse tipo de dados. A principal refere-se ao fato de que o tipo de dados XML não suporta um tamanho maior do que 2GB. Isso significa que se o arquivo XML em questão tiver mais de 2GB, essa alternativa não irá funcionar. Felizmente, é muito difícil que um arquivo XML a ser carregado tenha tamanho superior a 2GB e caso isso ocorra, deverá ser solicitado que esse arquivo seja repartido em arquivos menores.

Essa alternativa baseada em TSQL deve ser utilizada quando houver a intenção de persistir os dados do arquivo XML no banco de dados. Ela não deve ser utilizada para validar arquivos XML que não serão persistidos ou que não serão úteis para o banco de dados. Se o objetivo for apenas validar arquivos XML e não persistir, a validação deve ser codificada em uma aplicação a parte e o SQL Server não deve ser utilizado para isso.

Bem, acho que com isso finalizo a primeira alternativa para importar e validar dados XML. Não irei expor todas as alternativas possíveis, mas em breve apresentarei mais algumas.

[ ]s,

Gustavo

Como executar scripts SQL pelo SSIS

Boa Tarde,

Hoje passeando no fórum MSDN e na parte de BI vi uma thread que achei interessante. O título da thread era "Executar arquivos .sql pelo Integration Services" postada em http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=3668924&SiteID=21. Achei interessante essa dúvida. Algumas vezes após uma carga de dados ser realizada pode ser necessário rodar alguns scripts. Normalmente poderíamos fazer isso pelo próprio pacote, mas nem sempre essa é a melhor saída (pode ser que os scripts estejam compartilhados com outras aplicações).

O interessante também é que na thread original a necessidade não era devido a uma carga, mas sim a uma substituição na implementação. Originalmente os scripts eram rodados através de um cursor com a xp_cmdshell (possivelmente dentro de uma SP) e o grande problema é que na execução dos comandos era necessário especificar um login e senha. O fato do login e senha estarem informados, representava um risco a segurança.

Acho que trocar o login e senha por um -E no SQLCMD para ajudar a autenticação integrada talvez fosse suficiente para eliminar o problema da segurança, mas deixando essa necessidade de lado, acho que retirar os cursores e a xp_cmdshell é um ótimo negócio. O ideal mesmo seria construir um aplicativo a parte para rodar esses scripts. Ainda assim um pacote SSIS pode ser um boa escolha. Vejamos então como rodar um conjunto de scripts através de um pacote SSIS.

O primeiro passo é criar os scripts. Criei uma pasta (D:\Scripts), criei 4 arquivos (.sql) de scripts para teste conforme abaixo.

Script 1 (20080726_01.sql)

— Cria o banco de dados
IF EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME = ‘SCRIPT’)
    DROP DATABASE SCRIPT
CREATE DATABASE SCRIPT
GO
PRINT ‘Banco de dados criado com sucesso’

Script 2 (20080726_02.sql)

— Criação de uma tabela e um Schema
USE SCRIPT
GO
CREATE SCHEMA S
GO
CREATE TABLE S.T (Codigo INT)
GO
PRINT ‘Schema e tabela criados com sucesso’

Script 3 (20080726_03.sql)

— Popula a tabela
USE SCRIPT;
INSERT INTO S.T (CODIGO) VALUES (1)
INSERT INTO S.T (CODIGO) VALUES (2)
INSERT INTO S.T (CODIGO) VALUES (3)
INSERT INTO S.T (CODIGO) VALUES (4)
INSERT INTO S.T (CODIGO) VALUES (5)

PRINT ‘Registros inseridos com sucesso’

Script 4 (20080726_04.sql)

— Realiza uma consulta
USE SCRIPT;
SELECT CODIGO FROM S.T

Uma vez que os scripts tenham sido criados na pasta, iremos criar um pacote SSIS para rodar esses scripts. Abra um novo projeto SSIS, coloque um Container For Each Loop e dentro desse container ponha duas tarefas (Script Task e Execute Process Task) conforme a figura abaixo. Para que o pacote fique mais "legível" mudei o nome das tarefas:

Adicione as seguintes variáveis no escopo do Container ForEachLoop:

No Container ForEachLoop realize as seguintes alterações:

  • No Painel Collection, altere a propriedade Enumerator para Foreach File Enumerator
  • No Painel Collection, altere a propriedade folder para o diretório scripts
  • No Painel Collection, altere a propriedade Files para .sql
  • No Painel Collection, altere a propriedade Retrieve File Name para Full qualified
  • No Painel Variable Mappings, mapeie a variável NomeArquivoEntrada para o Index 0

Na tarefa Script Task faça as seguintes alterações:

  • No Painel Script, altere a propriedade ReadOnlyVariables para NomeArquivoEntrada,TemplateSQLCMD,Servidor
  • No Painel Script, altere a propriedade ReadWriteVariables para NomeArquivoSaida,InstrucaoSQLCMD
  • Na Painel Script, clique sobre Design Script e cole o script abaixo dentro da função Main()

‘Declara as variáveis
Dim NomeArquivoEntrada As String, NomeArquivoSaida As String
Dim Servidor As String, TemplateSQLCMD As String, InstrucaoSQLCMD As String

‘Coloca o arquivo de entrada igual ao de saída mudando a extensão
NomeArquivoEntrada = Dts.Variables("NomeArquivoEntrada").Value.ToString()
NomeArquivoSaida = Replace(NomeArquivoEntrada, ".sql", ".log")

‘Monta a instrução SQLCMD
Servidor = Dts.Variables("Servidor").Value.ToString()
TemplateSQLCMD = Dts.Variables("TemplateSQLCMD").Value.ToString()
InstrucaoSQLCMD = Replace(TemplateSQLCMD, "Servidor", Servidor)
InstrucaoSQLCMD = Replace(InstrucaoSQLCMD, "ArqEnt", NomeArquivoEntrada)
InstrucaoSQLCMD = Replace(InstrucaoSQLCMD, "ArqSai", NomeArquivoSaida)

‘Coloca o valor da instrução na variável InstrucaoSQLCMD
Dts.Variables("InstrucaoSQLCMD").Value = InstrucaoSQLCMD

‘Finaliza o Script
Dts.TaskResult = Dts.Results.Success

Na tarefa Execute Process Task faça as seguintes alterações:

  • No Painel Expressions, configure a propriedade Arguments para a variável InstrucaoSQLCMD
  • No Painel Process, altere a propriedade Windows Style igual a Hidden
  • No Painel Process, altere a propriedade Executable, localize o SQLCMD (normalmente em C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE)

O SQLCMD é o utilitário para executar scripts TSQL. Alguns parâmetros são utilizados nesse exemplo:

  • -E: faz a conexão através de Windows Authentication
  • -i: define um arquivo de entrada contendo o script a ser executado
  • -o: define um arquivo de saída com o retorno do script
  • -S: define o nome do servidor (no meu caso é .\SQL2005)

O que o pacote irá realizar é ler cada arquivo no diretório de scripts com a extensão .sql, conectar-se no servidor definido na variável Servidor, executar o script sql e gerar um arquivo de log com o mesmo nome do arquivo sql. O SQLCMD executou os scripts através da autenticação integrada (-E) mas caso seja necessário especificar um login e uma senha, basta trocar o -E por -U login -P Senha.

Esse mesmo pacote poderia utilizar a tarefa Execute SQL Task ao invés de Execute Process Task. Isso seria interessante, pois, bastaria trocar o provider e teríamos essa mesma solução funcionando em outros SGBDs. No entanto teríamos um pouco mais de dificuldades na hora de capturar a saída do script.

Mostrei apenas uma forma de fazer mas não quer dizer que é a única e tampouco a melhor. Caso tenha ficado alguma dúvida, disponibilizei um arquivo contendo os scripts e o projeto SSIS. Aos interessados, clique no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/SSIS.zip

[ ]s,

Gustavo