Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como compactar e descompactar arquivos e diretórios utilizando 7-zip e xp_cmdshell ou SQLCLR (C#)

Post Views 8,263 views
Reading time 10 minutes

Hey guys!

Nesse post eu gostaria de compartilhar algumas soluções de como compactar e descompactar arquivos e diretórios pelo SQL Server. Isso é especialmente útil para criar rotinas de ETL onde você precisa utilizar esses recursos utilizando scripts T-SQL ou mesmo para compactar arquivos de backup gerados na versão Express (que não possui suporte a backups compactados).

Caso você tenha interesse em tratamento de arquivos pelo SQL Server, veja mais alguns posts sobre esse assunto:
SQL Server – Operações com arquivos utilizando xp_cmdshell (Como listar, ler, escrever, copiar, excluir e mover arquivos)
Como compactar diretórios em arquivos ZIP utilizando 7zip e CMD
SQL Server – Como exportar dados do banco para arquivo texto (CLR, OLE, BCP)
SQL Server – Como importar arquivos de texto para o banco (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)
SQL Server – Como listar, ler, escrever, copiar, excluir e mover arquivos com o CLR (C#)
Operações com arquivos utilizando OLE Automation no SQL Server
SQL Server – Como exportar e importar arquivos com dados tabulares (Ex: CSV) utilizando o CLR (C#)
Importando arquivos CSV para o banco de dados SQL Server
SQL Server – Como fazer uma integração com FTP e listar, enviar (upload) e baixar (download) arquivos utilizando o CLR (C#)

Como em algumas alternativas vou utilizar o 7-zip, vocês podem personalizar ainda mais os exemplos citados aqui consultando a documentação oficial do binário, bem completa e exemplificada.

Alternativa #1: Utilizando 7-zip e xp_cmdshell

Visualizar conteúdo
Uma forma simples de conseguir implementar esse recurso é utilizando o binário do 7-zip e o comando xp_cmdshell no SQL Server. Para utilizar esse recurso, você precisará ser membro da role sysadmin.

Para ativar o xp_cmdshell, basta executar o comando abaixo:

Pré-requisito: 7-zip
Para que seja possível utilizar esse recurso, você precisará baixar os binários do 7-zip neste link. Você precisará baixar a versão “7-Zip Extra: standalone console version, 7z DLL, Plugin for Far Manager”, que vem no formato .7z.

Caso você não queira instalar o 7-zip no seu ambiente para poder abrir esse arquivo, vou disponibilizar os arquivos referentes ao standalone console version (versão 18.05) abaixo:
Versão x64 (recomendável) | Versão x86

Após baixar os arquivos, descompacte-os em algum diretório à sua escolha. Para os exemplos desse artigo, escolhi o diretório “C:\Binn\”.

Como compactar arquivos e diretórios

Para compactar arquivos e diretórios utilizando 7-zip e xp_cmdshell, você pode utilizar a Stored Procedure abaixo, que vai facilitar a sua utilização no dia a dia.

Exemplo de uso
Neste exemplo, vou demonstrar como compactar todos os arquivos e sub-diretórios do diretório “C:\Temporario\”, e salvando no arquivo “C:\Teste\Arquivo.zip”, com nível de compactação médio (5):

Result:

Arquivo ZIP gerado:

Outros exemplos:

Como descompactar arquivos e diretórios

Utilizando a Stored Procedure abaixo, podemos facilmente descompactar arquivos (com e sem senha) em algum diretório conforme a nossa necessidade. Vale ressaltar que, caso o diretório de destino não exista, ele será criado.

Exemplos de uso:

Alternativa #2: Utilizando 7-zip e SQLCLR (C#)

Visualizar conteúdo
Utilizando a mesma solução da alternativa #1, mas desta vez, encapsulados em procedures C# com o SQLCLR. A maior vantagem dessa solução é com relação ao permissionamento, que permite que você conceda apenas acesso de EXECUTE nessas procedures e o usuário, sem ser membro da role sysadmin, pode utilizar esses recursos.

Caso você não conheça ou não saiba o que é o SQLCLR, saiba mais acessando o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.

Pré-requisito: 7-zip
Para que seja possível utilizar esse recurso, você precisará baixar os binários do 7-zip neste link. Você precisará baixar a versão “7-Zip Extra: standalone console version, 7z DLL, Plugin for Far Manager”, que vem no formato .7z.

Caso você não queira instalar o 7-zip no seu ambiente para poder abrir esse arquivo, vou disponibilizar os arquivos referentes ao standalone console version (versão 18.05) abaixo:
Versão x64 (recomendável) | Versão x86

Após baixar os arquivos, descompacte-os em algum diretório à sua escolha. Para os exemplos desse artigo, escolhi o diretório “C:\Binn\”.

Código-fonte da solução

Abaixo, vou disponibilizar o código-fonte da solução de compactação utilizando 7-zip e SQLCLR.

Classe Retorno.cs (Exibir mensagens de alertas e erros)

stpCompacta_Arquivo.cs

stpDescompacta_Arquivo.cs

Código-fonte T-SQL
Caso você queira criar o assembly e as Stored Procedures sem ter que mexer nada de código-fonte e sem precisar nem instalar o Visual Studio, basta utilizar o código T-SQL abaixo. Lembre-se de extrair os binários do 7-Zip no diretório “C:\Binn\” (Para alterar, você teria que editar o projeto e fazer o deploy)

E caso você queira baixar o projeto do Visual Studio 2017, para editar as Stored procedures conforme sua necessidade, clique aqui neste link.

Como compactar arquivos e diretórios

Como descompactar arquivos e diretórios

Obs: Gostaria de pontuar que identifiquei um problema durante os testes com a stpDescompacta_Arquivo do SQLCLR, que é tentar descompactar um arquivo zip que tenha senha, sem especificar a senha na hora de chamar a SP, fazendo com que o processo do 7za.exe fique parado aguardando uma interação do usuário. Se isso acontecer, nem você cancelando a execução o processo irá encerrar, apenas executando o kill no processo 7za.exe pelo gerenciador de tarefas do Windows, o que vai fazer com que a sessão que estava aguardando prossiga com o processamento.

No caso de você errar a senha, a SP irá processar normalmente e mostrará a mensagem de erro informando que a senha está errada.

Alternativa #3: Utilizando a SharpZipLib e SQLCLR (C#)

Visualizar conteúdo
Para finalizar as alternativas desse post, gostaria de compartilhar uma solução utilizando a lib SharpZipLib, que permite compactar e descompactar arquivos utilizando apenas código C#, sem depender de binários externos como nos exemplos anteriores (7za.exe).

Caso você não conheça ou não saiba o que é o SQLCLR, saiba mais acessando o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.

Como a biblioteca SharpZipLib é bem grande, acaba ficando inviável disponibilizar todo o código fonte aqui no post, além também, da versão T-SQL. Por este motivo, vou disponibilizar links para fazer o download dos arquivos de código-fonte.

A solução completa do Visual Studio, onde você poderá editar o código livremente, está disponibilizada neste link.

Neste link aqui estou disponibilizando o código-fonte T-SQL, para permitir a criação dos objetos sem precisar instalar e nem abrir o Visual Studio.

Para entender melhor o parâmetro @filtro dessa solução, uma vez que ele é diferente das alternativas anteriores (que utilizam o 7-zip), recomendo a leitura da documentação oficial da biblioteca.

Como compactar arquivos e diretórios

Como descompactar arquivos e diretórios

And that's it, folks!
Espero que tenham gostado desse post, um abraço e até a próxima!