[SQL Server] Comparer la version des packages SSIS sur deux instances SQLServer
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 20/03/2020
-- Description: Comparer la version des packages SSIS sur deux instances SQLServer
-- =============================================
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat1]'))
BEGIN
DROP TABLE #Resultat1
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat2]'))
BEGIN
DROP TABLE #Resultat2
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..#CompPackage'))
BEGIN
DROP TABLE #CompPackage
END
SELECT T2.name NomProjet_PROD
,T1.[name] Nom_Package_PROD
,[package_format_version] package_format_version_PROD
,[version_major] version_major_PROD
,[version_minor] version_minor_PROD
,[version_build] version_build_PROD INTO #Resultat1
FROM LienServeurSQLServer_1.[SSISDB].[internal].[packages] T1
JOIN LienServeurSQLServer_1.[SSISDB].internal.projects T2 ON T2.project_id=T1.project_id AND T2.object_version_lsn=T1.project_version_lsn
SELECT T2.name NomProjet_Hom
,T1.[name] Nom_Package_Hom
,package_format_version [package_format_version_Hom]
,version_major [version_major_Hom]
,version_minor [version_minor_Hom]
,version_build [version_build_Hom]
INTO #Resultat2
FROM LienServeurSQLServer_2.[SSISDB].[internal].[packages] T1
JOIN LienServeurSQLServer_2.[SSISDB].internal.projects T2 ON T2.project_id=T1.project_id AND T2.object_version_lsn=T1.project_version_lsn
SELECT T1.*,T2.*
,MemeVersion=CASE WHEN package_format_version_PROD<>package_format_version_HOM
OR version_major_PROD<>version_major_HOM
OR version_minor_PROD<>version_minor_HOM
OR T1.version_build_PROD<>T2.version_build_HOM THEN 'FAUX'
ELSE 'VRAI' END
INTO #CompPackage
FROM #Resultat1 T1
JOIN #Resultat2 T2 ON T2.Nom_Package_Hom=T1.Nom_Package_PROD AND T2.NomProjet_Hom=T1.NomProjet_PROD
/*Joindre avec les dernières version des packages de la PROD déployés*/
JOIN(SELECT Nom_Package_PROD,NomProjet_PROD,MAX(version_build_PROD) version_build_PROD FROM #Resultat1 GROUP BY Nom_Package_PROD,NomProjet_PROD) T3
ON T3.Nom_Package_PROD=T1.Nom_Package_PROD AND T3.NomProjet_PROD=T1.NomProjet_PROD AND T3.version_build_PROD=T1.version_build_PROD
/*Joindre avec les dernières version des packages de la HOM déployés*/
JOIN(SELECT Nom_Package_HOM,NomProjet_HOM,MAX(version_build_HOM) version_build_HOM FROM #Resultat2 GROUP BY Nom_Package_HOM,NomProjet_HOM) T4
ON T4.Nom_Package_HOM=T2.Nom_Package_HOM AND T4.NomProjet_HOM=T2.NomProjet_HOM AND T4.version_build_HOM=T2.version_build_HOM
ORDER BY T1.NomProjet_PROD,T1.Nom_Package_PROD
SELECT * FROM #CompPackage ORDER BY [NomProjet_PROD] ASC,Nom_Package_PROD ASC