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