Como executar scripts SQL pelo SSIS

Boa Tarde,

Hoje passeando no fórum MSDN e na parte de BI vi uma thread que achei interessante. O título da thread era "Executar arquivos .sql pelo Integration Services" postada em http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=3668924&SiteID=21. Achei interessante essa dúvida. Algumas vezes após uma carga de dados ser realizada pode ser necessário rodar alguns scripts. Normalmente poderíamos fazer isso pelo próprio pacote, mas nem sempre essa é a melhor saída (pode ser que os scripts estejam compartilhados com outras aplicações).

O interessante também é que na thread original a necessidade não era devido a uma carga, mas sim a uma substituição na implementação. Originalmente os scripts eram rodados através de um cursor com a xp_cmdshell (possivelmente dentro de uma SP) e o grande problema é que na execução dos comandos era necessário especificar um login e senha. O fato do login e senha estarem informados, representava um risco a segurança.

Acho que trocar o login e senha por um -E no SQLCMD para ajudar a autenticação integrada talvez fosse suficiente para eliminar o problema da segurança, mas deixando essa necessidade de lado, acho que retirar os cursores e a xp_cmdshell é um ótimo negócio. O ideal mesmo seria construir um aplicativo a parte para rodar esses scripts. Ainda assim um pacote SSIS pode ser um boa escolha. Vejamos então como rodar um conjunto de scripts através de um pacote SSIS.

O primeiro passo é criar os scripts. Criei uma pasta (D:\Scripts), criei 4 arquivos (.sql) de scripts para teste conforme abaixo.

Script 1 (20080726_01.sql)

— Cria o banco de dados
IF EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME = ‘SCRIPT’)
    DROP DATABASE SCRIPT
CREATE DATABASE SCRIPT
GO
PRINT ‘Banco de dados criado com sucesso’

Script 2 (20080726_02.sql)

— Criação de uma tabela e um Schema
USE SCRIPT
GO
CREATE SCHEMA S
GO
CREATE TABLE S.T (Codigo INT)
GO
PRINT ‘Schema e tabela criados com sucesso’

Script 3 (20080726_03.sql)

— Popula a tabela
USE SCRIPT;
INSERT INTO S.T (CODIGO) VALUES (1)
INSERT INTO S.T (CODIGO) VALUES (2)
INSERT INTO S.T (CODIGO) VALUES (3)
INSERT INTO S.T (CODIGO) VALUES (4)
INSERT INTO S.T (CODIGO) VALUES (5)

PRINT ‘Registros inseridos com sucesso’

Script 4 (20080726_04.sql)

— Realiza uma consulta
USE SCRIPT;
SELECT CODIGO FROM S.T

Uma vez que os scripts tenham sido criados na pasta, iremos criar um pacote SSIS para rodar esses scripts. Abra um novo projeto SSIS, coloque um Container For Each Loop e dentro desse container ponha duas tarefas (Script Task e Execute Process Task) conforme a figura abaixo. Para que o pacote fique mais "legível" mudei o nome das tarefas:

Adicione as seguintes variáveis no escopo do Container ForEachLoop:

No Container ForEachLoop realize as seguintes alterações:

  • No Painel Collection, altere a propriedade Enumerator para Foreach File Enumerator
  • No Painel Collection, altere a propriedade folder para o diretório scripts
  • No Painel Collection, altere a propriedade Files para .sql
  • No Painel Collection, altere a propriedade Retrieve File Name para Full qualified
  • No Painel Variable Mappings, mapeie a variável NomeArquivoEntrada para o Index 0

Na tarefa Script Task faça as seguintes alterações:

  • No Painel Script, altere a propriedade ReadOnlyVariables para NomeArquivoEntrada,TemplateSQLCMD,Servidor
  • No Painel Script, altere a propriedade ReadWriteVariables para NomeArquivoSaida,InstrucaoSQLCMD
  • Na Painel Script, clique sobre Design Script e cole o script abaixo dentro da função Main()

‘Declara as variáveis
Dim NomeArquivoEntrada As String, NomeArquivoSaida As String
Dim Servidor As String, TemplateSQLCMD As String, InstrucaoSQLCMD As String

‘Coloca o arquivo de entrada igual ao de saída mudando a extensão
NomeArquivoEntrada = Dts.Variables("NomeArquivoEntrada").Value.ToString()
NomeArquivoSaida = Replace(NomeArquivoEntrada, ".sql", ".log")

‘Monta a instrução SQLCMD
Servidor = Dts.Variables("Servidor").Value.ToString()
TemplateSQLCMD = Dts.Variables("TemplateSQLCMD").Value.ToString()
InstrucaoSQLCMD = Replace(TemplateSQLCMD, "Servidor", Servidor)
InstrucaoSQLCMD = Replace(InstrucaoSQLCMD, "ArqEnt", NomeArquivoEntrada)
InstrucaoSQLCMD = Replace(InstrucaoSQLCMD, "ArqSai", NomeArquivoSaida)

‘Coloca o valor da instrução na variável InstrucaoSQLCMD
Dts.Variables("InstrucaoSQLCMD").Value = InstrucaoSQLCMD

‘Finaliza o Script
Dts.TaskResult = Dts.Results.Success

Na tarefa Execute Process Task faça as seguintes alterações:

  • No Painel Expressions, configure a propriedade Arguments para a variável InstrucaoSQLCMD
  • No Painel Process, altere a propriedade Windows Style igual a Hidden
  • No Painel Process, altere a propriedade Executable, localize o SQLCMD (normalmente em C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE)

O SQLCMD é o utilitário para executar scripts TSQL. Alguns parâmetros são utilizados nesse exemplo:

  • -E: faz a conexão através de Windows Authentication
  • -i: define um arquivo de entrada contendo o script a ser executado
  • -o: define um arquivo de saída com o retorno do script
  • -S: define o nome do servidor (no meu caso é .\SQL2005)

O que o pacote irá realizar é ler cada arquivo no diretório de scripts com a extensão .sql, conectar-se no servidor definido na variável Servidor, executar o script sql e gerar um arquivo de log com o mesmo nome do arquivo sql. O SQLCMD executou os scripts através da autenticação integrada (-E) mas caso seja necessário especificar um login e uma senha, basta trocar o -E por -U login -P Senha.

Esse mesmo pacote poderia utilizar a tarefa Execute SQL Task ao invés de Execute Process Task. Isso seria interessante, pois, bastaria trocar o provider e teríamos essa mesma solução funcionando em outros SGBDs. No entanto teríamos um pouco mais de dificuldades na hora de capturar a saída do script.

Mostrei apenas uma forma de fazer mas não quer dizer que é a única e tampouco a melhor. Caso tenha ficado alguma dúvida, disponibilizei um arquivo contendo os scripts e o projeto SSIS. Aos interessados, clique no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/SSIS.zip

[ ]s,

Gustavo

2 Respostas para “Como executar scripts SQL pelo SSIS

  1. Muito bom seu artigo.. vai me resolver um problemao.. so uma duvida, no meu visual studio 2008 ou no sql server 05 nao acho a instancia de criar um novo SSIS… qual sera o problema?

  2. Olá Sem Nome,Possivelmente você esqueceu de instalar o BIDS (Business Developement Studio) através do CD de instalação do SQL Server.Abs,

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s