SQL Server - Como comprimir y descomprimir archivos y directorios usando 7-zip y xp_cmdshell o SQLCLR (C#)

¡Hola, chicos!

En esta publicación me gustaría compartir algunas soluciones sobre cómo comprimir y descomprimir archivos y directorios usando SQL Server. Esto es especialmente útil para crear rutinas ETL en las que necesita utilizar estos recursos mediante scripts T-SQL o incluso para comprimir archivos de copia de seguridad generados en la versión Express (que no admite copias de seguridad comprimidas).

Si está interesado en el procesamiento de archivos mediante SQL Server, consulte algunas publicaciones más sobre este tema:
– SQL Server: operaciones de archivos usando xp_cmdshell (cómo enumerar, leer, escribir, copiar, eliminar y mover archivos)
– Cómo comprimir directorios en archivos ZIP usando 7zip y CMD
– SQL Server: cómo exportar datos de bases de datos a archivos de texto (CLR, OLE, BCP)
– SQL Server: cómo importar archivos de texto a la base de datos (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)
– SQL Server: cómo enumerar, leer, escribir, copiar, eliminar y mover archivos con CLR (C#)
– Operaciones de archivos usando OLE Automation en SQL Server
– SQL Server: cómo exportar e importar archivos con datos tabulares (Ej.: CSV) usando CLR (C#)
– Importación de archivos CSV a la base de datos de SQL Server
– SQL Server: cómo integrarse con FTP y enumerar, cargar y descargar archivos usando CLR (C#)

Como en algunas alternativas usaré 7-zip, puedes personalizar aún más los ejemplos mencionados aquí consultando el documentación binaria oficial, muy completo y ejemplificado.

Alternativa n.° 1: usar 7-zip y xp_cmdshell

Ver contenido

Una forma sencilla de implementar esta función es utilizar el binario 7-zip y el comando xp_cmdshell en SQL Server. Para utilizar esta función, deberá ser miembro del rol de administrador de sistemas.

Para activar xp_cmdshell, simplemente ejecute el siguiente comando:

sp_configure 'advanced options', 1
RECONFIGURE
GO

sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

Requisito previo: 7 cremalleras
Para poder utilizar esta función, deberá descargar los binarios 7-zip en este enlace. Deberá descargar la versión “7-Zip Extra”: versión de consola independiente, 7z DLL, complemento para Far Manager”, que viene en formato .7z.

Si no desea instalar 7-zip en su entorno para poder abrir este archivo, crearé los archivos relacionados con el versión de consola independiente (versión 18.05) a continuación:
versión x64 (recomendado) | versión x86

Después de descargar los archivos, descomprímalos en el directorio que elija. Para los ejemplos de este artículo, elegí el directorio “C:\Binn\”.

Cómo comprimir archivos y directorios

Para comprimir archivos y directorios usando 7-zip y xp_cmdshell, puede usar el Procedimiento almacenado a continuación, lo que facilitará su uso en el día a día.

USE [dirceuresende]
GO

CREATE PROCEDURE dbo.stpCompacta_Arquivo (
    @caminho VARCHAR(500),
    @filtro VARCHAR(500),
    @arquivoCompactado VARCHAR(500),
    @nivelCompactacao INT = 5,
    @recursivo BIT = 0,
    @senha VARCHAR(100) = NULL
)
AS 
BEGIN
    

    DECLARE @caminhoBinario VARCHAR(255) = 'C:\Binn\7za.exe'
    DECLARE @argumentos VARCHAR(MAX) = 'a -tzip -mx' + CAST(@nivelCompactacao AS VARCHAR(2)) + ' "' + @arquivoCompactado + '"' + (CASE WHEN @recursivo = 1 THEN ' -r' ELSE '' END) + (CASE WHEN NULLIF(LTRIM(RTRIM(@senha)), '') IS NOT NULL THEN ' -p' + @senha ELSE '' END) + ' "' + @caminho + '\' + @filtro + '" -mmt';
    DECLARE @Comando VARCHAR(8000) = 'call "' + @caminhoBinario + '" ' + @argumentos

    -- PRINT @Comando
    EXEC xp_cmdshell @Comando

END

Ejemplo de uso
En este ejemplo, demostraré cómo comprimir todos los archivos y subdirectorios en el directorio “C:\Temporary\” y guardarlos en el archivo “C:\Test\Arquivo.zip”, con un nivel de compresión medio (5):

EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario\', -- varchar(500)
    @filtro = '*', -- varchar(500)
    @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500)
    @nivelCompactacao = 5, -- int
    @recursivo = 1 -- bit

Resultado:

Archivo ZIP generado:

Otros ejemplos:

-- Modo básico
EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario', -- varchar(500)
    @filtro = '*.txt', -- varchar(500)
    @arquivoCompactado = 'C:\Teste\Arquivo.zip' -- varchar(500)

-- Compactando todos os arquivos que comecem com Teste%
EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario', -- varchar(500)
    @filtro = 'Teste*', -- varchar(500)
    @arquivoCompactado = 'C:\Teste\Arquivo.zip' -- varchar(500)

-- Compactando o arquivo "Teste.txt"
EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario', -- varchar(500)
    @filtro = 'Teste.txt', -- varchar(500)
    @arquivoCompactado = 'C:\Teste\Arquivo.zip' -- varchar(500)

/*

Compacta todos os arquivos com extensão .txt no diretório "C:\Temporario" (apenas na raiz, sem sub-diretórios), 
usando compactação máxima (9) e protegendo o arquivo com a senha "dirceu"

*/

EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario', -- varchar(500)
    @filtro = '*.txt', -- varchar(500)
    @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500)
    @nivelCompactacao = 9, -- int
    @recursivo = 0, -- bit
    @senha = 'dirceu' -- varchar(100)

Cómo descomprimir archivos y directorios

Usando el Procedimiento almacenado A continuación, podemos descomprimir archivos fácilmente (con y sin contraseña) en algún directorio según nuestras necesidades. Cabe mencionar que, si el directorio de destino no existe, se creará.

USE [dirceuresende]
GO

CREATE PROCEDURE dbo.stpDescompacta_Arquivo (
    @arquivoCompactado VARCHAR(500),
    @pastaDestino VARCHAR(500),
    @filtro VARCHAR(500) = NULL,
    @senha VARCHAR(100) = NULL
)
AS 
BEGIN
    

    DECLARE @caminhoBinario VARCHAR(255) = 'C:\Binn\7za.exe'
    DECLARE @argumentos VARCHAR(MAX) = 'x "' + @arquivoCompactado + '" -aoa -o"' + @pastaDestino + '" ' + ISNULL(@filtro, '') + '' + (CASE WHEN NULLIF(LTRIM(RTRIM(@senha)), '') IS NOT NULL THEN ' -p' + @senha ELSE '' END) + ' -r -mmt';
    DECLARE @Comando VARCHAR(8000) = 'call "' + @caminhoBinario + '" ' + @argumentos

    PRINT @Comando
    EXEC xp_cmdshell @Comando

END

Ejemplos de uso:

-- Exemplo básico
EXEC dbo.stpDescompacta_Arquivo
    @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500)
    @pastaDestino = 'C:\Teste 2\'

-- Descompactando arquivos com senha
EXEC dbo.stpDescompacta_Arquivo
    @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500)
    @pastaDestino = 'C:\Teste 2\', -- varchar(100)
    @senha = 'dirceu' -- varchar(100)

-- Descompactando apenas arquivos com extensão .txt
EXEC dbo.stpDescompacta_Arquivo
    @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500)
    @pastaDestino = 'C:\Teste 2\', -- varchar(100)
    @filtro = '*.txt' -- varchar(100)

-- Descompactando apenas o arquivo "Teste.txt"
EXEC dbo.stpDescompacta_Arquivo
    @arquivoCompactado = 'C:\Teste\Arquivo.zip', -- varchar(500)
    @pastaDestino = 'C:\Teste 2\', -- varchar(100)
    @filtro = 'Teste.txt' -- varchar(100)

Alternativa n.° 2: usar 7-zip y SQLCLR (C#)

Ver contenido

Usando la misma solución que la alternativa #1, pero esta vez, encapsulada en procedimientos de C# con SQLCLR. La mayor ventaja de esta solución está en relación a los permisos, que permite otorgar solo acceso de EJECUCIÓN a estos procedimientos y el usuario, sin ser miembro del rol sysadmin, puede utilizar estos recursos.

Si no sabes o no sabes qué es SQLCLR, infórmate accediendo al post Introducción a SQL CLR (Common Language Runtime) en SQL Server.

Después de descargar los archivos, descomprímalos en el directorio que elija. Para los ejemplos de este artículo, elegí el directorio “C:\Binn\”.

Código fuente de la solución

A continuación, proporcionaré el código fuente de la solución de compresión utilizando 7-zip y SQLCLR.

Clase Retorno.cs (Mostrar mensajes de alerta y error)

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace Bibliotecas.Model
{

    public static class Retorno
    {

        public static void Erro(string erro)
        {
            
            Mensagem(erro);

            throw new ApplicationException(erro);
        }


        public static void Mensagem(string mensagem)
        {

            using (var conexao = new SqlConnection("context connection=true"))
            {

                conexao.Open();

                using (var comando = new SqlCommand("IF ( (512 & @@OPTIONS) = 512 ) select 1 else select 0", conexao))
                {
                    if ((int)comando.ExecuteScalar() != 0) return;
                }

                var retorno = SqlContext.Pipe;
                retorno?.Send(mensagem.Length > 4000 ? mensagem.Substring(0, 4000) : mensagem);
            }

        }

        public static void RetornaReader(SqlDataReader dataReader)
        {
            var retorno = SqlContext.Pipe;
            retorno?.Send(dataReader);
        }
    }

    public class Ret : Exception
    {
        public Ret(string str) : base(str)
        {
        }
    }
}

stpCompacta_File.cs

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Diagnostics;
using Bibliotecas.Model;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpCompacta_Arquivo(SqlString caminho, SqlString filtro, SqlString arquivoCompactado, SqlInt32 nivelCompactacao, SqlString senha)
    {


        string[] arquivos;

        #region tratamento de entradas

        if (nivelCompactacao.Value < 0 || nivelCompactacao.Value > 9)
        {
            Retorno.Erro("Parametro inválido na variavel nivelCompactacao, use de 0 a 9 para compactar, sendo 9 o nivel mais alto (5 recomendado).");
            return;
        }


        try
        {
            if (!Directory.Exists(caminho.Value))
            {
                Retorno.Erro($"O caminho especificado '{arquivoCompactado}' não existe ou está inacessivel.");
                return;
            }
        }
        catch
        {
            Retorno.Erro($"Erro ao converter caminho especificado ('{arquivoCompactado}').");
            return;
        }


        try
        {
            arquivos = Directory.GetFiles(caminho.Value, filtro.Value);
        }
        catch
        {
            Retorno.Erro($"Erro ao listar arquivos no caminho especificado ({arquivoCompactado}).");
            return;
        }


        var objetoArquivoCompactado = new FileInfo(arquivoCompactado.Value);
        var diretorioArquivoCompactado = objetoArquivoCompactado.Directory?.ToString();

        if (diretorioArquivoCompactado == null || !Directory.Exists(diretorioArquivoCompactado))
        {
            Retorno.Erro($"O caminho do arquivo compactado especificado ({diretorioArquivoCompactado}) não existe ou inacessível.");
            return;
        }


        if (objetoArquivoCompactado.Extension.Trim() != "")
        {
            try
            {
                objetoArquivoCompactado.Delete();
            }
            catch
            {
                Retorno.Erro($"o arquivo compactado especificado ({arquivoCompactado.Value}) está sendo usado.");
                return;
            }
        }
        else
        {
            Retorno.Erro($"O arquivo compactado especificado não possui uma extensão válida ({objetoArquivoCompactado.Extension.Trim()}).");
            return;
        }



        #endregion

        try
        {

            var argumentos = $@" a -tzip -mx{nivelCompactacao.Value} ""{arquivoCompactado.Value}""" + (!senha.IsNull && !string.IsNullOrEmpty(senha.Value) ? $" -p{senha.Value} " : " ") + $@"""{caminho.Value}\{filtro.Value}"" -mmt";

            using (var scriptProc = new Process { StartInfo = { FileName = @"C:\Binn\7za.exe", UseShellExecute = false, Arguments = argumentos, RedirectStandardOutput = true, RedirectStandardError = true, StandardOutputEncoding = Encoding.GetEncoding(850), StandardErrorEncoding = Encoding.GetEncoding(850), CreateNoWindow = true } })
            {

                scriptProc.Start();

                var output = scriptProc.StandardOutput.ReadToEnd();
                var erro = scriptProc.StandardError.ReadToEnd();


                if (output.Length > 0)
                {
                    //Retorno.Mensagem(output);
                }


                if (erro.Length > 0)
                {
                    Retorno.Erro(erro);
                }

            }

            var quantidadeArquivos = arquivos.Length;

            Retorno.Mensagem("Total de: (" + quantidadeArquivos + ") arquivos foram compactados em: " + arquivoCompactado.Value);
        }
        catch (Exception e)
        {
            Retorno.Erro("Erro : " + e.Message);
        }
    }

};

Alternativa n.º 3: uso de SharpZipLib y SQLCLR (C#)

Ver contenido

Para finalizar las alternativas en esta publicación, me gustaría compartir una solución usando la biblioteca. SharpZipLib, que permite comprimir y descomprimir archivos utilizando únicamente código C#, sin depender de binarios externos como en los ejemplos anteriores (7za.exe).

Si no sabes o no sabes qué es SQLCLR, infórmate accediendo al post Introducción a SQL CLR (Common Language Runtime) en SQL Server.

Como la biblioteca SharpZipLib es bastante grande, resulta inviable que todo el código fuente esté disponible aquí en la publicación, así como la versión T-SQL. Por este motivo, proporcionaré enlaces para descargar los archivos del código fuente.

La solución completa de Visual Studio, donde puedes editar el código libremente, está disponible disponible en este enlace.

En este enlace aquí Estoy poniendo a disposición el código fuente de T-SQL para permitir la creación de objetos sin tener que instalar o abrir Visual Studio.

Para comprender mejor el parámetro @filtro de esta solución, ya que es diferente a las alternativas anteriores (que usan 7-zip), recomiendo leer el documentación oficial de la biblioteca.

Cómo comprimir archivos y directorios

-- Exemplo básico
EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario\',
    @filtro = '*',
    @arquivoCompactado = 'C:\Teste\Arquivo.zip',
    @nivelCompactacao = 5,
    @senha = ''

-- Compactando arquivos com extensão .txt e utilizando compactação máxima (9)
EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario\',
    @filtro = '*',
    @arquivoCompactado = 'C:\Teste\Arquivo.zip',
    @nivelCompactacao = 9,
    @senha = ''

-- Compactando arquivos e protegendo-os com senha
EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario\',
    @filtro = '*',
    @arquivoCompactado = 'C:\Teste\Arquivo.zip',
    @nivelCompactacao = 5,
    @senha = 'dirceu'

-- Compactando apenas o arquivo "Teste 1.txt"
EXEC dbo.stpCompacta_Arquivo
    @caminho = 'C:\Temporario\',
    @filtro = 'Teste 1.txt',
    @arquivoCompactado = 'C:\Teste\Arquivo.zip',
    @nivelCompactacao = 5,
    @senha = ''

Cómo descomprimir archivos y directorios

-- Exemplo básico
EXEC dbo.stpDescompacta_Arquivo
    @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max)
    @pastaDestino = N'C:\Teste\', -- nvarchar(max)
    @filtro = N'', -- nvarchar(max)
    @senha = N'' -- nvarchar(max)

-- Descompactando arquivos com senha
EXEC dbo.stpDescompacta_Arquivo
    @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max)
    @pastaDestino = N'C:\Teste\', -- nvarchar(max)
    @filtro = N'', -- nvarchar(max)
    @senha = N'dirceu' -- nvarchar(max)

-- Descompactando apenas arquivos com extensão .txt
EXEC dbo.stpDescompacta_Arquivo
    @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max)
    @pastaDestino = N'C:\Teste\', -- nvarchar(max)
    @filtro = N'.txt', -- nvarchar(max)
    @senha = N'' -- nvarchar(max)

-- Descompactando arquivos que começam com "Teste%"
EXEC dbo.stpDescompacta_Arquivo
    @arquivoCompactado = N'C:\Teste\Arquivo.zip', -- nvarchar(max)
    @pastaDestino = N'C:\Teste\', -- nvarchar(max)
    @filtro = N'Teste*', -- nvarchar(max)
    @senha = N'' -- nvarchar(max)

¡Eso es todo, amigos!
Espero que os haya gustado este post, un abrazo y ¡hasta la próxima!

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…