An architecture for automated team-based development and deployment of DWH/BI projects

Posted: June 1, 2013 in MSSQL
Tags:

Task: to design an architecture for automated team-based development and deployment of data warehouse and business intelligence projects by means of open source or free products


Figure 1: UML – Use Case (Enterprise Architect)

Figure 1: UML – Use Case (Enterprise Architect)

Abbreviations/Notes:
SVN+WinMerge+VS(Visual Studio: SSDT/BIDS – Free Product, Schema and Data Compare):
SVN+VS:
Figure 2: SNV+VS (DB and BI Projects)

Figure 2: SNV+VS (DB and BI Projects)

Jenkins:
Figure 3: Jenkins – Build Pileline

Figure 3: Jenkins – Build Pileline (Source: Build Pipeline Plugin's Site)

Parameters:
  • Environment (Choice): DEV, TEST, PROD
  • INCLUDE_DB_STG (Boolean value): TRUE
  • INCLUDE_DB_ODS (Boolean value): TRUE
  • INCLUDE_DB_DWH (Boolean value): TRUE
  • INCLUDE_DB_LOG (Boolean value): TRUE
  • INCLUDE_SSIS_STG (Boolean value): TRUE
  • INCLUDE_SSIS_ODS (Boolean value): TRUE
  • INCLUDE_SSIS_DWH (Boolean value): TRUE
  • INCLUDE_SSAS (Boolean value): TRUE
  • INCLUDE_SSRS (Boolean value): TRUE
Source Code Management:
Build Triggers:
  • Build periodically: H H(0-5) * * 1-5 (note: every working day during night hours)
Extra Plugins:

Build:
1. Execute windows batch command – BUILD_DEVENV.bat


"%WORKSPACE%\CI_JENKINS\BUILD_DEVENV.bat" %Environment%

1.1 BUILD_DEVENV.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
 SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
 %devenv% %solution% /build Debug
GOTO END

:TEST
 SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
 SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
 %devenv% %solution% /build Release
GOTO END

:PROD
 SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
 SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
 %devenv% %solution% /build Release
GOTO END

:UNKNOWN
GOTO END

:END

2. Execute windows batch command – DB_RDB_BACKUP.bat


"%WORKSPACE%\CI_JENKINS\DB_RDB_BACKUP.bat" %Environment%

2.1 DB_RDB_BACKUP.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_RDB_BACKUP.sql"
 SET server="server\test"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

2.2 DB_RDB_BACKUP.sql


BACKUP DATABASE [DWH_TEST_DWH] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_DWH.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_TEST_DWH-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [DWH_TEST_LOG] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_LOG.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_TEST_LOG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [DWH_TEST_ODS] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_ODS.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_TEST_ODS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [DWH_TEST_STG] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_STG.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_TEST_STG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

2.3 DB_RDB_RESTORE.bat (optional)


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_RDB_RESTORE.sql"
 SET server="server\test"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

2.4 DB_RDB_RESTORE.sql (optional)


USE [master]
ALTER DATABASE [DWH_TEST_DWH] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_DWH] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_DWH.bak' WITH  FILE = 4,  MOVE N'DWH_TEST_DWH' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_DWH.mdf',  MOVE N'DWH_TEST_DWH_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_DWH.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [DWH_TEST_DWH] SET MULTI_USER
GO

USE [master]
ALTER DATABASE [DWH_TEST_LOG] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_LOG] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_LOG.bak' WITH  FILE = 4,  MOVE N'DWH_TEST_LOG' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_LOG.mdf',  MOVE N'DWH_TEST_LOG_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_LOG.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [DWH_TEST_LOG] SET MULTI_USER
GO

USE [master]
ALTER DATABASE [DWH_TEST_ODS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_ODS] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_ODS.bak' WITH  FILE = 4,  MOVE N'DWH_TEST_ODS' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_ODS.mdf',  MOVE N'DWH_TEST_ODS_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_ODS.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [DWH_TEST_ODS] SET MULTI_USER
GO

USE [master]
ALTER DATABASE [DWH_TEST_STG] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_STG] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_STG.bak' WITH  FILE = 4,  MOVE N'DWH_TEST_STG' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_STG.mdf',  MOVE N'DWH_TEST_STG_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_STG.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [DWH_TEST_STG] SET MULTI_USER
GO

3. Execute windows batch command – DB_SSIS_BACKUP.bat


"%WORKSPACE%\CI_JENKINS\DB_SSIS_BACKUP.bat" %Environment%

3.1 DB_SSIS_BACKUP.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSIS_BACKUP.sql"
 SET server="server"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

3.2 DB_SSIS_BACKUP.sql


BACKUP DATABASE [SSISDB] TO  DISK = N'D:\MSSQL\Backup\SSISDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'SSISDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

3.3 DB_SSIS_RESTORE.bat (optional)


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSIS_RESTORE.sql"
 SET server="server"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

3.4 DB_SSIS_RESTORE.sql (optional)


USE [master]
ALTER DATABASE [SSISDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [SSISDB] FROM  DISK = N'D:\MSSQL\Backup\SSISDB.bak' WITH  FILE = 3,  MOVE N'data' TO N'D:\MSSQL\Data\SSISDB.mdf',  MOVE N'log' TO N'D:\MSSQL\Data\SSISDB.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [SSISDB] SET MULTI_USER
GO

4. Execute windows batch command – DB_SSRS_BACKUP.bat


"%WORKSPACE%\CI_JENKINS\DB_SSRS_BACKUP.bat" %Environment%

4.1 DB_SSRS_BACKUP.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSRS_BACKUP.sql"
 SET server="server\test"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

4.2 DB_SSRS_BACKUP.sql


BACKUP DATABASE [ReportServer$TEST] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServer$TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [ReportServer$TESTTempDB] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TESTTempDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServer$TESTTempDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

4.3 DB_SSRS_RESTORE.bat (optional)


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSRS_RESTORE.sql"
 SET server="server\test"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

4.4 DB_SSRS_RESTORE.sql (optional)


USE [master]
ALTER DATABASE [ReportServer$TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [ReportServer$TEST] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST_LogBackup_2013-05-25_11-54-42.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServer$TEST_LogBackup_2013-05-25_11-54-42', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [ReportServer$TEST] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST.bak' WITH  FILE = 2,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [ReportServer$TEST] SET MULTI_USER
GO

USE [master]
ALTER DATABASE [ReportServer$TESTTempDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [ReportServer$TESTTempDB] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TESTTempDB.bak' WITH  FILE = 2,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [ReportServer$TESTTempDB] SET MULTI_USER
GO

4.5 SSRS_BACKUP.bat (optional)


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ItemPath="/SSRS_TEST"
 SET ReportServerURL=http://server/ReportServer_TEST
 SET BackupFolder="C:\temp\CI_JENKINS\Backups\SSRS"
 SET ScriptFolder="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_BACKUP.rss"
 RS -i %ScriptFolder% -s %ReportServerURL% -v ItemPath="%ItemPath%" -v BackupFolder="%BackupFolder%"
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

4.6 SSRS_BACKUP.rss (optional)


Public Sub Main()
 
Try
 
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 
Dim Items as CatalogItem()
 
Dim Item as CatalogItem
 
Dim ReportName As String
 
Items = rs.ListChildren(ItemPath, false)
 
Console.Writeline()
 
Console.Writeline("Reports Back Up Started.")
 
For Each Item in Items
 
ReportName = ItemPath + "/" + Item.Name
 
Dim reportDefinition As Byte() = Nothing
 
Dim rdlReport As New System.Xml.XmlDocument
reportDefinition = rs.GetReportDefinition(ReportName)
Dim Stream As New MemoryStream(reportDefinition)
 
Dim curDate as Date = Date.Now()
Dim strDate as String = curDate.ToString("dd-MM-yyyy")
 
Dim BackupFolderNew as String = BackupFolder+"\"+strDate+"\"+ItemPath
 
If(Not System.IO.Directory.Exists(BackupFolderNew )) Then
    System.IO.Directory.CreateDirectory(BackupFolderNew)
End If
 
rdlReport.Load(Stream)
rdlReport.Save(BackupFolderNew + "\" + Item.Name +".rdl")
 
Console.Writeline("Report " + Item.Name +".rdl Backed up Successfully")
 
Next
 
Console.Writeline("Reports Back Up Completed.")
 
Console.Writeline()
 
catch e As Exception
 
Console.Writeline(e.Message)
 
End Try
 
End Sub

5. Execute windows batch command – SSAS_BACKUP.bat


"%WORKSPACE%\CI_JENKINS\SSAS_BACKUP.bat" %Environment%

5.1 SSAS_BACKUP.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ASCMD="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_ASCMD.exe"
 SET XMLA="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_BACKUP.xmla"
 SET SERVER="server\test"
 %ASCMD% -S %SERVER% -i %XMLA%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

5.2 SSAS_BACKUP.xmla


<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>SSAS</DatabaseID>
  </Object>
  <File>SSAS.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

5.3 SSAS_RESTORE.xmla (optional)


<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <File>SSAS.abf</File>
  <DatabaseName>SSAS</DatabaseName>
  <AllowOverwrite>true</AllowOverwrite>
</Restore>

6. Execute windows batch command – INCLUDE_DB_STG.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_STG.bat" %INCLUDE_DB_STG%, %Environment%

6.1 INCLUDE_DB_STG.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_STG_DEPLOY.bat" %2
GOTO END

:END

6.2 DB_STG_DEPLOY.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_STG\DB_STG_server.publish.xml"
 SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_STG\bin\Debug\DB_STG.dacpac"
 %SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

7. Execute windows batch command – INCLUDE_DB_ODS.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_ODS.bat" %INCLUDE_DB_ODS%, %Environment%

7.1 INCLUDE_DB_ODS.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_ODS_DEPLOY.bat" %2
GOTO END

:END

7.2 DB_ODS_DEPLOY.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_ODS\DB_ODS_server.publish.xml"
 SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_ODS\bin\Debug\DB_ODS.dacpac"
 %SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

8. Execute windows batch command – INCLUDE_DB_DWH.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_DWH.bat" %INCLUDE_DB_DWH%, %Environment%

8.1 INCLUDE_DB_DWH.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_DWH_DEPLOY.bat" %2
GOTO END

:END

8.2 DB_DWH_DEPLOY.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_DWH\DB_DWH_server.publish.xml"
 SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_DWH\bin\Debug\DB_DWH.dacpac"
 %SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

9. Execute windows batch command – INCLUDE_DB_LOG.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_LOG.bat" %INCLUDE_DB_LOG%, %Environment%

9.1 INCLUDE_DB_LOG.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_LOG_DEPLOY.bat" %2
GOTO END

:END

9.2 DB_LOG_DEPLOY.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_LOG\DB_LOG_server.publish.xml"
 SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_LOG\bin\Debug\DB_LOG.dacpac"
 %SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

10 Execute windows batch command – INCLUDE_SSIS_STG.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_STG.bat" %INCLUDE_SSIS_STG%, %Environment%

10.1 INCLUDE_SSIS_STG.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_STG_DEPLOY.bat" %2
GOTO END

:END

10.2 SSIS_STG_DEPLOY.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ISDeploymentWizard="ISDeploymentWizard.exe"
 SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_STG\bin\Development\SSIS_STG.ispac"
 SET server="server"
 SET SSIS="/SSISDB/TEST/SSIS_STG"
 %ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

11. Execute windows batch command – INCLUDE_SSIS_ODS.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_ODS.bat" %INCLUDE_SSIS_ODS%, %Environment%

11.1 INCLUDE_SSIS_ODS.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_ODS_DEPLOY.bat" %2
GOTO END

:END

11.2 SSIS_ODS_DEPLOY.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ISDeploymentWizard="ISDeploymentWizard.exe"
 SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_ODS\bin\Development\SSIS_ODS.ispac"
 SET server="server"
 SET SSIS="/SSISDB/TEST/SSIS_ODS"
 %ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

12. Execute windows batch command – INCLUDE_SSIS_DWH.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_DWH.bat" %INCLUDE_SSIS_DWH%, %Environment%

12.1 INCLUDE_SSIS_DWH.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_DWH_DEPLOY.bat" %2
GOTO END

:END

12.2 SSIS_DWH_DEPLOY.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ISDeploymentWizard="ISDeploymentWizard.exe"
 SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_DWH\bin\Development\SSIS_DWH.ispac"
 SET server="server"
 SET SSIS="/SSISDB/TEST/SSIS_DWH"
 %ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

13. Execute windows batch command – INCLUDE_SSAS.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_SSAS.bat" %INCLUDE_SSAS%, %Environment%

13.1 INCLUDE_SSAS.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_DEPLOY.bat" %2
GOTO END

:END

13.2 SSAS_DEPLOY.bat


IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET Deployment="C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"
 SET asdatabase="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSAS\bin\SSAS.asdatabase"
 %Deployment% %asdatabase% /s
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

14. Execute windows batch command – INCLUDE_SSRS.bat


"%WORKSPACE%\CI_JENKINS\INCLUDE_SSRS.bat" %INCLUDE_SSRS%, %Environment%

14.1 INCLUDE_SSRS.bat


IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.bat" %2
GOTO END

:END

14.2 SSRS_DEPLOY.bat


@echo off
::Script Variables
SET LOGFILE="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_TEST.log"
SET SCRIPTLOCATION=C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS



REM SET REPORTSERVER=http://server/ReportServer_TEST

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END

:TEST
 SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END

:PROD
 SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END

:UNKNOWN
 SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END

:END



SET RS=rs.exe
SET TIMEOUT=60

::Clear Log file
IF EXIST %logfile% DEL %logfile%

::Write Log Header
ECHO Reporting Services Scripter Load Log >>%LOGFILE%
ECHO. >>%LOGFILE%
ECHO Starting Load at %DATE% %TIME% >>%LOGFILE%
ECHO SCRIPTLOCATION = %SCRIPTLOCATION% >>%LOGFILE%
ECHO REPORTSERVER   = %REPORTSERVER% >>%LOGFILE%
ECHO BACKUPLOCATION = %BACKUPLOCATION% >>%LOGFILE%
ECHO SCRIPTLEVEL    = SQL2008 >>%LOGFILE%
ECHO TIMEOUT        = %TIMEOUT% >>%LOGFILE%
ECHO RS             = %rs% >>%LOGFILE%
ECHO. >>%LOGFILE%

::Run Scripts

%rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST" -v DATASOURCE="" >>%LOGFILE% 2>&1
ECHO. >>%LOGFILE%

@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_1" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%

@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_2" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%

@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_3" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%

@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_4" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%

ECHO. >>%LOGFILE%
ECHO Finished Load at %DATE% %TIME% >>%LOGFILE%
ECHO. >>%LOGFILE%

14.3 SSRS_DEPLOY.rss


Public Sub Main()
	Dim pathToReport As String = "../workspace/SSRS_TEST/"

	Dim region As String = REGIONFOLDER 
	Dim folder As String = PARENTFOLDER
	Dim name As String = RDLNAME
	Dim data As String = DATASOURCE

	Dim parent As String = region + "/" + folder
	Dim location As String = pathToReport + name + ".rdl"

	Dim overwrite As Boolean = True
	Dim reportContents As Byte() = Nothing
	Dim warnings As Warning() = Nothing

	Dim fullpath As String = parent + "/" + name

	'Common CatalogItem properties
	Dim descprop As New [Property]
	descprop.Name = "Description"
	descprop.Value = ""
	Dim hiddenprop As New [Property]
	hiddenprop.Name = "Hidden"
	hiddenprop.Value = "False"

	Dim props(1) As [Property]
	props(0) = descprop
	props(1) = hiddenprop

	'console.writeline("region:   {0}", region)
	'console.writeline("folder:   {0}", folder)
	'console.writeline("name:     {0}", name)
	'console.writeline("parent=region/folder: {0}", parent)
	'console.writeline("location=name.rdl: {0}", location)
	'console.writeline("fullpath=parent/folder/name: {0}", fullpath)

	'Read RDL definition from disk
	Try
		Dim stream As FileStream = File.OpenRead(location)
		reportContents = New [Byte](stream.Length-1) {}
		stream.Read(reportContents, 0, CInt(stream.Length))
		stream.Close()

		warnings = RS.CreateReport(name, parent, overwrite, reportContents, props)

		If Not (warnings Is Nothing) Then
			Dim warning As Warning
			For Each warning In warnings
				Console.WriteLine(Warning.Message)
			Next warning
		Else
			Console.WriteLine("Report: {0} published successfully with no warnings", name)
		End If

		'Set report DataSource references
		''Dim dataSources(0) As DataSource

		''Dim dsr0 As New DataSourceReference
		''dsr0.Reference = region + "/Data Sources/MyDataSource"
		''Dim ds0 As New DataSource
		''ds0.Item = CType(dsr0, DataSourceDefinitionOrReference)
		''ds0.Name = data 
		''dataSources(0) = ds0

		''RS.SetItemDataSources(fullpath, dataSources)

		''Console.Writeline("Report DataSources set successfully")
        ''Console.WriteLine("Report: {0} published successfully", name)

	Catch e As IOException
		Console.WriteLine(e.Message)
	Catch e As SoapException
		Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
		Console.WriteLine("Report: {0} published with error", name)
	End Try
End Sub

14.4 SSRS_TEST.log (output log)


Reporting Services Scripter Load Log 
 
Starting Load at st 22. 05. 2013 10:40:36,81 
SCRIPTLOCATION = C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS 
REPORTSERVER   = http://server/ReportServer_TEST 
BACKUPLOCATION =  
SCRIPTLEVEL    = SQL2008 
TIMEOUT        = 60 
RS             = rs.exe 
 
Report: Report_TEST published successfully with no warnings
The command completed successfully
 
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_1" -v DATASOURCE="" 
off ECHO. 
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_2" -v DATASOURCE="" 
off ECHO. 
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_3" -v DATASOURCE="" 
off ECHO. 
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_4" -v DATASOURCE="" 
off ECHO. 
 
Finished Load at st 22. 05. 2013 10:40:53,28 


Recommendations:
  • Offline Development: it allows developers independent development, debugging own code and database structures and consequently commit already functional code to online database project repository
  • Units (granularity – smaller units are better) for Development:
  • DB (table, view, stored procedure, function etc.)
  • SSIS (package etc.)
  • SSAS (cube, dimension etc.)
  • SSRS (data sets, report etc.)
  • MSBuild.exe: only database projects
  • devenv.exe: database (after installing SSDT) and business intelligence projects
Source code:
Additional references:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s