T-SQL Tips - Movendo pacotes SSIS entre servidores

/*
      Olá Senhores, Senhoras e Senhoritas DBAs!!!!
     
      Recentemente tive a necessidade de migrar mais de duzendos pacotes de Integration entre um servidor e o outro. A experiencia que no inicio parecia bem dolorosa (dado que os pacotes estavam em solutions diferentes), se mostrou bem interessante quando resolvi utilizar o dtutil! 
      Um aplicativo tipo prompt que serve justamente para gerenciar esse tipo de situação.
     
      Depois de brigar um pouco com a Syntaxe, cheguei a uma solução que gostaria muito de compartilhar com vocês.
     
      Espero que te Ajude!! E vamos por a mão na massa!
     
     
     
      Essa solução está dividida em x partes
     
      1 - Definir os comandos necessários do dtutil.
      2 - Criar consultas para gerar os comandos no estado do servidor atual.
      3 - Colocar tudo em um bom e velho .BAT e executar.
     


--Parte 1 - O comando


      Particularmente achei a syntaxe do dtutil bem chatinha (como voce pode ver no link do books online abaixo). E após uma certa briga chegamos as
      seguintes syntaxes
     
      a) Comando para recriar a estrutura de pastas e subpastas no servidor destino
     
      dtutil /FC SQL;"/PastaPai/";"SubPasta" /SourceS ServidorDestino
     
           
      b) Comando para copiar os pacotes do servidor origem para o servidor destino
     
      dtutil /SQL "PastaPai/SubPasta/Pacote" /SourceS ServidorOrigem /COPY SQL;"PastaPai/SubPasta/Pacote" /DestS ServidorDestino
     
     
BOOKS ONLINE: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_5techref/html/6c7975ff-acec-4e6e-82e5-a641e3a98afe.htm
--Parte 2 - Consultas para gerar os comandos

      a) A primeira consulta é uma query recursiva que usa as tabelas do MSDB para criar na ordem as pastas e subpastas.
           
      ATENÇÃO: As tabelas Data Collector e Maintenance Plans são padrão em todas as instalações. Por isso remova-as da lista gerada.
     
      Obs: Para entender maiores informações sobre consultas recursivas, leia esse post aqui.

*/   

      use msdb
      DECLARE @ServidorDestino varchar(100) = 'Coloque aqui a instancia Destino'
     
      ;WITH CTE_Recursiva (parentfolder,foldername,folderid,Level)
      as
      (
      SELECT
            CAST('/' as sysname) parentfolder,
            foldername folder,
            folderid ,
            0 Level

      FROM
            sysssispackagefolders
      WHERE
            parentfolderid = '00000000-0000-0000-0000-000000000000'
      UNION ALL
      SELECT
            CAST(C.parentfolder + C.foldername +'/'  as sysname)   parentfolder,
            f.foldername folder,
            f.folderid ,
            C.Level + 1 Level
      FROM
            sysssispackagefolders f
      INNER JOIN CTE_Recursiva C on C.folderid = f.parentfolderid     
      )
      SELECT
            'dtutil /FC SQL;"' + parentfolder + '";"' + foldername + '" /SourceS ' + @ServidorDestino  Comando
      FROM
            CTE_Recursiva
      ORDER BY
            Level,
            parentfolder           


/*
           
            b) A segunda consulta (tambem recursiva) gera os comandos que copiam os pacotes entre um servidor e outro.
           
            ATENÇÃO: Da mesma maneira que a consulta anterior, tome cuidado para remover os pacotes da Data Collector e Maintenance Plans (nesse ultimo só se voce quiser)
           
*/



GO
use msdb
DECLARE @ServidorOrigem varchar(21) = 'Coloque aqui a instancia de Origem'
DECLARE @ServidorDestino varchar(21) = 'Coloque aqui a instancia Destino'

;WITH CTE_Recursiva (Package,FolderID)
AS
(
SELECT
      CAST(name as sysname) Package,
      folderid
FROM
      sysssispackages
UNION ALL
SELECT
      CAST(f.foldername + '/' + C.Package as sysname) Package ,
      f.parentfolderid folderid
FROM
      sysssispackagefolders f
INNER JOIN CTE_Recursiva C on C.folderid = f.folderid           
)
SELECT
      'dtutil /SQL "' +
      RIGHT(Package,LEN(Package) - 1)
      + '" /SourceS ' + @ServidorOrigem + ' /COPY SQL;"'
      + RIGHT(Package,LEN(Package) - 1) 
      + '" /DestS ' + @ServidorDestino Comando
FROM CTE_Recursiva
WHERE FolderID IS NULL
ORDER BY
      Comando
     
/*

--Parte 3 - Gerar o BAT.

      E pronto caro leitor, mude a saida dos comandos para Texto e copie esses comandos em um arquivo .BAT.
     
      E é só executar!



      Espero que tenha te ajudado, porque isso me economizou algumas horas de import/export mecanico e como diria Super Sam: - Time is money!!
     
      Se tiver uma critica, duvida ou sugestão por favor me escreva no fjantunes@gmail.com, twite no felipe_jose@store.com.br ou me mande perguntas
      no http://www.formspring.me/agoradba
     
      Abraços!!!
     
      Felipe Antunes
      Keen on SQL Server!!!!

*/