Deploying a Microsoft BI Solution via Script

At the end of a phase of a business intelligence project, when it has been accepted and it is time to move into production, comes the deployment on the production environment. The deployment itself should also be testable, reproducible and of course be completely debugged. The method largely depends on the customer’s IT team’s way of working. Some will insist on Installshield executables, some will prefer moving a server from acceptance to deployment, some will restore backups, some will work script based. I prefer that last approach and go into some detail on how to do this for all the typical parts of a MS BI solution: the database, integration services packages, SQL Agent jobs and the SSAS cubes.

Deploying the Database Contents via Script
The contents of the database consists of:

  • a) database objects: tables, views, indexes, stored procedures…
  • b) the contents of fixed dimensions (e.g. the time dimension), mapping tables, …
  • c) special contents for dynamic dimensions (e.g. special “unknown” elements)


a) Scripting database objects is simple: use SQL Server Management Studio. Right-click on the database, select Tasks then Generate Scripts: this launches a wizard. Do not check “script all objects”. In the options table set “Script Drops” to true and “Script Indexes” to true (it defaults to false – duh!). You will probably not want to deploy logins so make sure it is set to false. Next steps of the wizard are self-explanatory. At the end you have a script that will generate all the selected database objects. Save it as DWH_DB_CreateAll.sql.

b) Scripting table contents is less obvious.
I have adapted a T-SQL script written by Nigel Rivett (original is here) to output insert statements given a list of tables. If its output is saved as a file (an option in SQL Server Management Studio) it creates all the inserts for every record in the given table list.
It adds IDENTITY INSERT statements where necessary, correctly handles NULLs and adds enough GO statements so that the script will still execute even if it gets very long. (I have made a 40+ Mb script with it!)

Download the source of the stored procedure sp_CreateDataLoadScript here.

Example of usage:

DECLARE @databaseName varchar(128)
DECLARE @TblNames varchar(max)

SET @databaseName='DWH_DB'
SET @TblNames='Z_Packages,Z_Parameters,Z_SQLStatements,Z_Zones'

EXECUTE [DWH_DB].[dbo].[sp_CreateDataLoadScript] @databaseName, @TblNames

Its output looks like:

use DWH_DB
go
set nocount on
/* Z_Packages */
truncate table Z_Packages
 insert Z_Packages ( [PackageID],[PackageName] )  select 1,'PckgA'
 insert Z_Packages ( [PackageID],[PackageName] )  select 2,'PckgB'
...
go
/* Z_Parameters */
truncate table Z_Parameters
 insert Z_Parameters ( [ParameterName],[ParameterValue],[Comments] )  select 'PrmA','1','PrmA comment'
...

Save your output as DWH_DB_InitialContents.sql.

c) Adding special initial contents is again simple: these are mere insert statements, either created by hand or using the same procedure as in b). If for example there is a product dimension that will be fed by some source system and you want to link the unmatched sales values to a specific element you will need that element in the dimension table:

/* H_DimProduct */
truncate table H_DimProduct
 insert H_DimProduct ( [ProductID],[ProductCode],[ProductName],[RunID],[RunUpdID] )
 select -1,'UNKNOWN','Unknown',0,null
go

Save these statements in a file DWH_DB_InitialExceptions.sql.

The 3 SQL scripts thus created can be called from one batch script. Save the following as CreateAndLoadDatabaseObjects.bat:

set DBSERVER=myserver
sqlcmd -S %DBSERVER% -i DWH_DB_CreateAll.sql
sqlcmd -S %DBSERVER% -i DWH_DB_InitialContents.sql
sqlcmd -S %DBSERVER% -i DWH_DB_InitialExceptions.sql

Calling CreateAndLoadDatabaseObjects.bat will (re-)create and load all necessary database objects.

You may want to add another script: one with GRANT EXECUTE statements should your security setup require this.


Deploying SSIS packages via Script
Packaged can be deployed to the MSDB database.
The configuration to deploy in this example consist of 3 SSIS packages, PackageA, PackageB and PackageC, to be put in the MSDB folder ProjectX on the server TheServer. The sources of the SSIS packages, i.e. the dtsx-files, are located on Z:\Sources\ProjectX.

@echo off

rem CHANGE THE FOLLOWING 3 VARIABLES ACCORDING TO DEPLOYMENT NEEDS
rem
set DESTSERVER=TheServer
set DESTLOCATION=projectX
set SOURCEFOLDER=Z:\Sources\ProjectX

@echo checking existence of %DESTLOCATION%
dtutil /SOURCESERVER %DESTSERVER% /FE SQL;%DESTLOCATION%
if ERRORLEVEL 1 GOTO CREATEDEST
@echo ok
goto INSTALLPACKAGES

:CREATEDEST
dtutil /SOURCESERVER %DESTSERVER% /FC SQL;\;%DESTLOCATION%
if ERRORLEVEL 1 GOTO ERRCREATE
@echo %DESTLOCATION% created
goto INSTALLPACKAGES

:ERRCREATE
@echo could not create %DESTLOCATION%  -  batch stopped
goto DONE

:INSTALLPACKAGES

set SSISPACKAGE=packageA

@echo deploying %SSISPACKAGE% to %DESTSERVER%/%DESTLOCATION%
dtutil /FILE %SOURCEFOLDER%\%SSISPACKAGE%.dtsx /DestServer %DESTSERVER% /COPY SQL;%DESTLOCATION%\%SSISPACKAGE% /Quiet
if ERRORLEVEL 1 GOTO ERR

set SSISPACKAGE=packageB

@echo deploying %SSISPACKAGE% to %DESTSERVER%/%DESTLOCATION%
dtutil /FILE %SOURCEFOLDER%\%SSISPACKAGE%.dtsx /DestServer %DESTSERVER% /COPY SQL;%DESTLOCATION%\%SSISPACKAGE% /Quiet
if ERRORLEVEL 1 GOTO ERR

set SSISPACKAGE=packageC

@echo deploying %SSISPACKAGE% to %DESTSERVER%/%DESTLOCATION%
dtutil /FILE %SOURCEFOLDER%\%SSISPACKAGE%.dtsx /DestServer %DESTSERVER% /COPY SQL;%DESTLOCATION%\%SSISPACKAGE% /Quiet
if ERRORLEVEL 1 GOTO ERR

@echo All done
goto DONE

:ERR
@echo Error deploying package %SSISPACKAGE%  -  batch stopped.

 :D ONE
@pause

Dowload the above code here.

If you would also want to script the SQL Agent job that runs the packages, then things get trickier.
You will have to:
- create a credential to use for the proxy that will run the job (this cannot be scripted, I believe);
- use a script to create the proxy and grant it the right to launch SSIS packages:

USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'ProjectX_Writer_proxy',@credential_name=N'ProjectX_Writer',
		@enabled=1

exec sp_grant_proxy_to_subsystem @proxy_name = N'ProjectX_Writer_proxy', @subsystem_id=11

- use a script to create the SQL Agent job: start with an existing job and script that via the Management Studio. Next edit it to make it server independent. Remember that the SSIS packages contain Datasources and you probably want to overrule these in the job! Something along this line:

set @cmnd=N'/DTS "\MSDB\ProjectX\PackageA" /SERVER "' + @@servername + N'" /CONNECTION Datawarehouse;"Data Source=' + @@servername + N';Initial Catalog=ProjectXDB;Provider=SQLNCLI.1;Persist Security Info=False;Integrated Security=SSPI;Auto Translate=false;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run PackageA',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=3,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'SSIS',
		@command=@cmnd,
		@database_name=N'master',
		@flags=0,
		@proxy_name=N'ProjectX_Writer_Proxy'

The above code assumes the SSIS server and database server from which the package reads its data are the same, which is probably true in 99.99% of the cases. There are possibly multiple connections in a package, e.g. a connection to a relational database and a connection to analysis services. Review them and overrule where necessary.


Deploying SSAS database via Script
To deploy the SSAS database I use the ascmd.exe utility from the AS 2005 samples. Download it here. Here is the readme for the tool.

First script the SSAS database via the Management Studio wizard (Script database as… / CREATE To / File …) The result is an XMLA file.

Inside the XMLA file is the connection string that tells the cube(s) where to get their data from. When deploying you will probably want these to point to the correct environment. To do this, we will replace the servername and password with a ascmd variable.

Open the XMLA file and look for an entry <ConnectionString>.
Replace the server name with a variable $(AS_Server). The connection string will look something like:

Provider=SQLNCLI.1;Data Source=$(AS_Server);Integrated Security=SSPI;Initial Catalog=ProjectXDB

The next entry in the XMLA file is the impersonation info. Normally there is no password information there. Add a line with a variable that refers to the password:

ImpersonateAccount
 MyDomain\ProjectX_Reader
 $(ProjectXReaderPass)

The modified XMLA will server as input to the ascmd.exe utility.
The last step is the batch file that actually calls this utility and sets database and password (you might as well script the username).

The contents of the batch file DeployCube.bat:

set AS_SERVER=TheServer
ascmd -S %AS_SERVER% -i ProjectXCube.xmla -v AS_Server="%AS_SERVER%" DWHReaderPass="123foobar"



At the end of all this we have:
- a script that deploys the database objects and its initial contents
- a script that deploys the SSIS packages and the SQL Agent job that runs them
- a script that deploys the SSAS cubes.
Every script sets the connection strings of the objects it deploys. This is a clean, fast, repeatable and easy way to deploy a complete BI solution.


This article sums up quite a few details about deploying a Microsoft BI solution via script. If it helped you, feel free to leave a comment. ;-)

3 Comments »

Comment by Todd McDermid
2010-03-19 18:33:04

Thanks for the very detailed info – I was really searching for the last bit there about deploying SSAS databases easily (without all the data contained in it as well). Works great! I would just add that where you’ve given examples of how to modify the XMLA script – they don’t look complete. It’s probably the use of the “greater than” and “less than” symbols being interpreted and the contained tags hidden by your blog software. I can’t see the XML tags for “ImpersonationMode”, “Account”, or “Password” where they should be in your examples.

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post