If a piece of software is described in any way, shape or form as being "enterprise", it's a safe bet that you don't actually want to use it. As a general rule, "enterprise" software packages mix the Inner-Platform Effect with trying to be all things to all customers, with thousands upon thousands of lines of legacy code that can't be touched because at least one customer depends on those quirks. There doesn't tend to be much competition in the "enterprise" space, so none of the vendors actually put any thought into making their products good. That's what salesbeasts and lawyers are for.

Kristoph M supports a deployment of Initech's data warehouse system. Since this system is a mix of stored procedures and SSIS packages, Kristoph can actually read a good portion of the code which makes the product work. They just choose not to. And that's usually a good choice.

But one day, while debugging, Kristoph decided that they needed a simple answer to a simple question: "For a SQLAgent Job, how do you create a backup of the database with the day appended to the filename?"

SQLAgent is SQL Server's scheduling system, used for triggering tasks. SSIS is SQL Server's "drag and drop" dataflow tool, designed to let users draw data pipelines to handle extract-transform-load tasks.

In this case, the SQLAgent job's first step was to launch an SSIS package. Already, we're in questionable territory. SSIS is, as stated, an ETL tool. Yes, you can use it to extract data, it's not really meant as a replacement for an actual database backup.

The good news is that this SSIS package doesn't actually do anything to backup the database. Instead, it contains a single task, and it isn't a data flow task, it's a "Visual Basic Script Task". Yes, SSIS lets you run a stripped down Visual Basic dialect in its context. What does this task do?

    Public Sub Main()
        '
        ' Add your code here
        '
        Dim sToday As Date = Now

        Dim sDay As String = sToday.Day.ToString
        If CInt(sDay) < 10 Then sDay = "0" & sDay
        Dim sMonth As String = MonthName(Month(sToday), True)
        Dim sYear As String = Year(sToday).ToString
        Dim sPara1 As String = sDay '& sMonth & sYear

        Dim sPath As String = "D:\Initech\DailyProcess\"

        Using fso As StreamWriter = New StreamWriter(sPath & "runBackupBatch.bat")
            fso.WriteLine(sPath & "DailyExtractBackup.bat " & sPara1)
            fso.Close()
        End Using
        Dts.TaskResult = ScriptResults.Success
    End Sub

This figures out the current day, and then writes out a runBackupBatch.bat file with contents like this:

D:\Initech\DailyProcess\DailyExtractBackup.bat 02

Once that step is completed, the SQLAgent job continues, and runs the runBackupBatch.bat, which in turn runs DailyExtractBackup.bat, which does this:

D:\Initech\DailyProcess\DailyExtractBackup.bat
@echo off
@echo Dumping DailyExtract database...
osql -E -Slocalhost -oD:\Initech\DailyProcess\DailyExtractDump.log -Q"backup database DailyExtract to DISK='D:\Initech\MSSQL\Backup\DailyExtractDump%1.bak' with INIT"
if errorlevel 1 goto dumperror

REM Check for SQL Errors
findstr "Msg" D:\Initech\DailyProcess\DailyExtractDump.log
if not errorlevel 1 goto dumperror

:OK
@echo All Done!!
exit 0

:dumperror
@echo Error dumping database.
exit 1

The osql call is about the first reasonable step in this process. That actually does the backup using SQL server's backup tools. Then again, the mechanism to see if there were any errors in the logfile is troubling. findstr sets the errorlevel to 1 if Msg is not found in the log file. So, if Msg is not not found in the logfile, we'll go to dumperror.

After reading through this process, Kristoph decide it was best to take a step outside, get some air, and stop thinking about the other horrible things that might be lurking in Initech's data warehouse product.