Settings . bat running a . dstx

Question

I am running a file . dtsx using a load bat and I am wanting to make some adjustments:

1. Legibly mount the file name date.
2. Adjust in log file to tell which package name . dtsx was executed
3. Decrease code to enter date and time in log file name.

To generate the error log file name the following code is used:

ECHO ON
:: DATA_HORA
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
set year=%date:~-4%
set month=%date:~3,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
set day=%date:~0,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%

SET data=%year%%month%%day%_%hour%%min%%secs%

SET outputfile=%VAR_LOGS_DIR%CARGA_SSIS_%data%.log

"%VAR_DTSEXEC_DIR%" /F "%VAR_PACKAGE_DIR%\NOME_PACOTE.dtsx" /CONNECTION %CN_S_TRA% /CONNECTION %CN_T_STG%  /REPORTING E  >> %outputfile%
IF ERRORLEVEL 1 GOTO END 

The generated log gets the correct name: CARGA_SSIS_20170321_103029, but looking p/ this code that generates the name indicates that it has much better ways to assemble it...

Mount a log in the file that says which package name . dtsx was executed:

I need it to enter in the log what the package name is. dtsx that was executed to result in success/error and that some attributes that are not useful were removed, an example of how I wanted it to stay is as follows:

OF THE ORIGINAL:

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3487.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  10:30:29
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  10:30:29
Finished: 10:31:47
Elapsed:  77.75 seconds

Started:  10:31:50
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  10:31:50
Finished: 10:32:47
Elapsed:  77.75 seconds

Started:  10:32:47
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  10:32:47
Finished: 10:34:43
Elapsed:  77.75 seconds

FOR LOG WITH FILENAME OF EXECUTED . DTSX PACKAGES

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3487.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Name: NOME_DO_PACOTE1.DTSX
Started:  10:30:29
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  10:30:29
Finished: 10:31:47
Elapsed:  77.75 seconds

Name: NOME_DO_PACOTE2.DTSX
Started:  10:31:50
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  10:31:50
Finished: 10:32:47
Elapsed:  77.75 seconds

Name: NOME_DO_PACOTE3.DTSX
Started:  10:32:47
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  10:32:47
Finished: 10:34:43
Elapsed:  77.75 seconds

Answer 1

I can help you with your third question, to decrease the code that generates the log file name. Can replace by:

:: Recebe a data com formatação padrão:
set data=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
:: caso tiver espaço, troca para 0.
set data=%data: =0%

^{Source: Format date and time in a Windows batch script}

Answer 2

---

About how to do to get data/hora in the best way and make use to compose the layout
in substring/variables/filename, I suggest you consult these two answers
So en/EN Q224040 SO-EN Q12635541, plus this one post /EN

---

@echo off && setlocal enabledelayedexpansion && title <nul & title .\%~nx0

color 80 && for /f ^tokens^=^1^delims^=^. %%i in ('
%__APPDIR__%wbem\wmic.exe OS Get localdatetime^|findstr [0-9]
')do set "_dt=%%~i" && call set "_data=!_dt:~0,4!!_dt:~4,2!!_dt:~6,2!_!_dt:~8,6!"

rem :: _Var !_dt! ==  20200124002435
rem :: ---------------------------------------------
rem :: !_dt:~0,4! ==  Ano 2020 
rem :: !_dt:~4,2! ==  Mes     01
rem :: !_dt:~6,2! ==  Dia       24
rem :: _          ==  Separador   _ 
rem :: !_dt:~8,6! ==  Hora (24/HH) 002435
rem :: -----------------------------------------------
rem :: Layout/Formato variavel _data: 20200124_012435 
rem :: !outputfile! == CARGA_SSIS_20200124_002435.log
rem :: -----------------------------------------------

SET "outputfile=!VAR_LOGS_DIR!CARGA_SSIS_!_data!.log"
echo/_dt=!_dt! # _data=!_data! # outputfile=!outputfile!

"%__APPDIR__%timeout.exe" -1

("!VAR_DTSEXEC_DIR!" /F "!VAR_PACKAGE_DIR!\NOME_PACOTE.dtsx" /CONNECTION !CN_S_TRA! /CONNECTION !CN_T_STG! /REPORTING E
)>>"!outputfile!" && (echo/ Comando executado sem erros^^!) || (start "" notepad.exe "!outputfile!")

endlocal && goto :END