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)
Abbreviations/Notes:
- SVN: Subversion (Apache, Open Source, Repository)
- UML: Enterprise Architect (EA, SparxSystems, Commercial Product, Script Generating)
UML alternatives:
- PowerDesigner (Sybase, Commercial Product)
- Visual Studio: Ultimate Edition (Architecture and Modeling) (Microsoft, Commercial Product)
- Office Visio (Microsoft, Commercial Product)
- wwwsqldesigner (Google, Open Source) or ArgoUML (Tigris, Open Source)
- CI: Continuous Integration (Jenkins, JAVA, Open Source)
- – – – optional
SVN+WinMerge+VS(Visual Studio: SSDT/BIDS – Free Product, Schema and Data Compare):
SVN+VS:
Figure 2: SNV+VS (DB and BI Projects)
Jenkins:
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:
48.312872
18.086038
Placko's database KB 