DTExec Reporting Options

Currently, we are running a Master Package with sub-Packages that are executed as a result. We run multiple days by executing a .bat file of DTExec commands. For Example:

Code Snippet

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /SET \Package.Variables[ReportingDate].Value;"1/02/2007" > etl_20070102.log
mkdir E:\ETL\ErrorLogs\Archive\20070102
copy E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070102

Date values are incremented for as many days as we want to run. The log gives progress information and the Started, Finished, Elapsed time for the the Master package.

We are interested in manipulating the script entries to get the Start, Finished, Elapsed time for the sub-Packages that are initiated by this script. I think that I could use the Reporting option:

Code Snippet

/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]

Of course I can't find a good example to model the script. Is there anyone else using DTExec to get the run time statistics for each and every package? If so, can you forward that part of the script that accomplishes this task? BTW, we are going to implement run-time auditing to a table at some point but we are not there yet. Of course, my manager would like statistics now.

Thanks in advance.

You could get all those details by enabling package logging on each package; that is just a few clicks away. You can choose where the log information is going to be: file, table etc.

Package logging is enable/disable at the package level, so you need to edit each package.

|||

If my package itself fails to load for some reason, the logging will not happen (this is my assumption), so how do I capture that. The DTEXEC return codes just says the package failed to load. For eg when I executing one of the packages I got the return code as 1, but the console displayed the below error.

Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14
(Only the owner of DTS Package 'kk-test' or a member of the sysadmin role may create new versions of it.).
The SQL statement that was issued has failed.

I dont think there is any way for me to capture this error.

Thanks

|||

You could try redirecting the output from DTEXEC to a file.

Code Snippet

DTEXEC [your package params] > log.txt

|||

Visual SSIS package execution stats (e.g. # executions, most recent execution timestamp, avg runtime , # failures, # successes, and all by machine) is available for "free" via SQL Server Reporting services, provided one uses the stock SQL Server log provider in each package.

These run time stats can be had via the following BI project: SSIS Log Provider Reports .

The stock SQL Server log provider writes to table named sysdtslog90 via the stored procedure sp_dts_addlogentry in whatever server instance/database is pointed to by the log provider's connection manager.

Now, since the report does hit the table directly, its probably a more reasonable approach to push the logging data into a cube periodically, reporting from there, but that feature is not included.

|||

jwelch wrote:

You could try redirecting the output from DTEXEC to a file.

Code Snippet

DTEXEC [your package params] > log.txt

This works if I execute the DTEXEC directly in the command prompt, but when I execute using WScript shell in vbscript it fails with return value 6 saying "The utility encountered an internal error of syntactic or semantic errors in the command line"

Code Snippet

strShellCommand = "DTEXEC /SQL "\pkg-1 " /SERVER MBIXDEV1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E > \\serv1\logs\test.tmp"
Set objWshShell = WScript.CreateObject("WScript.Shell")

lngReturnValue = objWshShell.Run(strShellCommand , vbNormalFocus, True)

So how do I make this work? If I copy the strShellCommand value to command prompt and run it works.

Thanks

|||

See Michael's post here: http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

You probably need to run it as CMD.EXE /C DTEXEC [rest of your commandline] > log.txt

|||

Karunakaran,

We invoke a .bat file from the vbs script:

Code Snippet

IF (colFiles1.Count = 1 _
AND colFiles2.Count = 1 _
AND colFiles3.Count = 1 _
AND colFiles4.Count = 1 _
AND colFiles5.Count = 1 _
AND colFiles6.Count = 1 _
AND colFiles7.Count = 1) THEN
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "E:\\ETL\\daily_import_and_etl_with_cmd_input.bat 0807 8/7"
Wscript.Quit
END IF

The .bat file looks like this basically:

Code Snippet

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SET \Package.Variables[ReportingDate].Value;"%2/2007" > E:\ETL\ErrorLogs\Processing\etl_2007%1log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
MKDIR E:\ETL\ErrorLogs\Archive\2007%1
MOVE E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\2007%1

The vbs looks for files and then kicks off the ETL. The .bat file has the commands for the ETL.|||

Thanks John, cmd did the trick.

Unfortunately I cannot take the batch file approach because my sys admins will not allow, thanks for suggesting that though.