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 |
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 |
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 |
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) |
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 |
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
Grande Gustavo, muito útil esse post e bem escrito. Muito obrigado pela ajuda (mesmo que indireta).Nayron
Olá Nayron,Obrigado pelo feedback. Não sei como o ajudou, mas que bom que o artigo o ajudou.[ ]s,
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,