Database Reference
In-Depth Information
Listing 19-3.
Executing an SSIS File from a Network Share
CD
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn
DtExec.exe
/FILE \\RSLAPTOP2\PubsBIProdFiles\ETLProcessForDWPubsSales.dtsx
Combining the SQL Server and SSIS Deployment Code
Release managers will often create a batch file that combines the data warehouse restoration and the SSIS ETL
processing by running just one batch file. As an example, the command shell code in Listing 19-4 would be
placed in a batch file. Note how it restores the database using
sqlcmd.exe
and then performs the ETL processing
using
dtexec.exe
.
Listing 19-4.
Combining Multiple Commands into a Batch File
REM DeployPubsBISolution
REM Restore the database backup
CD "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn"
SQLCMD.exe -S RSLaptop2\SQL2012 -E -i "\\RSLAPTOP2\PubsBIProdFiles\RestoreDWPubsSales.sql"
pause
REM Run the ETL process
CD C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn
DtExec.exe /FILE \\RSLAPTOP2\PubsBIProdFiles\ETLProcessForDWPubsSales.dtsx
pause
To create the batch file, open a simple text editor such as Notepad, type in the code, and save it with a
.bat
extension (for example,
Deployment.bat
).
When the batch file is created, you can execute it by name from a command prompt or by double-clicking
the file in Windows Explorer. Either method runs the code contained inside the file as if it had been manually
typed at the Windows command prompt.
■
If you double-click the file in Windows Explorer, it temporarily opens a command prompt window, runs the
code, and immediately closes the command prompt window. By placing the
pause
command on the last line of the
batch file, the command prompt window will stay open until you click any keyboard key.
Tip
Another advantage to using a batch file in this manner is that you can use the Window's Task Scheduler
program to automatically run the file at a designated point in time without the need for human interaction. You
can find this program under Administrative Tools in the Windows Control Panel.
Deploying the SSAS Database
An SSAS backup file can be restored using XMLA code. Unfortunately, Microsoft has not provided a command
prompt utility to execute XMLA code (although there are some third-party utilities that will). Therefore, it may
seem that your only option is to manually restore the database using SQL Server Management Studio or to deploy
the SSAS project from Visual Studio as we have done in this topic.
You can, however, run XMLA code using an SSIS package that can in turn be automated using batch files.
First, create and test some XMLA code that restores the backup file on the network share (Listing 19-5).
