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

4 Respostas para “Como importar e exportar imagens entre o SQL Server e o File System ? – Parte II

  1. Grande Gustavo, muito útil esse post e bem escrito. Muito obrigado pela ajuda (mesmo que indireta).Nayron

  2. Olá Nayron,Obrigado pelo feedback. Não sei como o ajudou, mas que bom que o artigo o ajudou.[ ]s,

  3. Seu exemplo está somente na versão do VB 2005 Não tem como você deixar na versão 2010 , tentei converter o projeto mas dá erro.

    • Oi Klezer,

      Eu já pensei em convertê-lo pro Visual Studio 2010. O problema é que toda hora sai um Visual Studio novo e aí estamos sujeitos a esses problemas (por isso também mostrei o processo visualmente). Vou tentar efetuar a conversão para o 2008 e depois para o 2010. Quando puder publico a nova versão.

      [ ]s,

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s