SQL Server: Script para particionar conteúdo de arquivo

Companheiros DBAs, boa tarde

Hoje vou compartilhar mais um script com vocês.

Um amigo me perguntou esta semana sobre a possibilidade de criar um script para automatizar uma atividade diária. Basicamente ele queria particionar o conteúdo de um arquivo em vários arquivos, como profissional da área da ‘arte’ (TI), prontamente disse que seria possível, a partir daí abri o meu SSMS (SQL Server Management Studio) para quebrar a cabeça.

Exemplo:

Original.txt

1111111111

2222222222

Arquivo1.txt

1111111111

Arquivo2.txt

2222222222

Após algumas poucas horas de ‘queima neurônios’ e simulações consegui criar uma stored procedure automatizando a atividade do meu amigo, possibilitando passar como parâmetros o caminho do arquivo fonte, o nome do arquivo com sua extensão, o caminho de destino dos arquivos particionados e o número de linhas para cada arquivo.

Para usar a procedure será necessário criar uma table que será o repositório das informações do arquivo fonte, eis abaixo o script:

CREATE TABLE [dbo].[Particiona]([Informacao] [varchar](10) NULL, [Contador] [int] IDENTITY(1,1) NOT NULL) ON [PRIMARY]

Na criação da stored procedure considere modificar as informações marcadas, database e servidor para os nomes utilizados no seu ambiente, abaixo a stored procedure:

CREATE PROCEDURE [dbo].[spParticionaArquivo]

@Fonte   VARCHAR(500), --Caminho UNC (\\host\compartilhamento\) do arquivo original

@Arquivo VARCHAR(500), --Nome do arquivo (com extensão)

@Destino VARCHAR(500), --Caminho (\\host\compartilhamento\) onde os arquivos particionados serão armazenado

@Corte INT                  --Quantidade de linhas por arquivo

AS

TRUNCATE TABLE Particiona --Limpa objeto repositório das informações

DECLARE @Total INT, @Contador INT, @Parte INT, @BCPQUERY VARCHAR(8000), @SQL VARCHAR(8000)

SET @BCPQUERY = 'bcp "database.dbo.Particiona" IN "'+@Fonte+''+@Arquivo+'" -T -S"servidor" -T -f"'+@Fonte+'Format.FMT"' --Importa

SET @SQL = 'xp_cmdshell '''+@BCPQUERY+''''                         

EXEC(@SQL) --Executa

SELECT @Total = COUNT(0) FROM Particiona

SET @Contador = 1

SET @Parte = 1

WHILE @Contador < @total

BEGIN

           

SET @BCPQUERY = 'bcp "SELECT MSISDN FROM database.dbo.ParticionaBroadcast WHERE Contador >= '+CAST(@contador AS VARCHAR(10))+' AND Contador <= '+CAST(@contador + (@Corte - 1) AS VARCHAR(10))+'" QUERYOUT "'+CAST(@Destino AS VARCHAR(500))+'\'+CAST(@Parte AS VARCHAR(10))+'.TXT" -T -S"servidor" -c'

SET @SQL = 'xp_cmdshell '''+@BCPQUERY+''''                         

EXEC(@SQL) --Executa

SET @Contador = @Contador + @Corte

SET @Parte = @Parte + 1

END

Observações: A stored procedure só executa se a feature cmdshell estiver habilitada na sua instância, e o usuário faz parte da server role sysadmin. O bcp de importação usa um arquivo com o layout do arquivo fonte, assim, também é necessário criar no mesmo diretório informado para o parâmetro @Fonte um arquivo com o conteúdo abaixo:

8.0
1
1 SQLCHAR 0 1 "\r\n" 1 col1 Latin1_General_CI_AI