功能：

1.删除1个月前的数据

2.导出sql数据为文本文件

3.ftp上传

基础代码如下：

@echo off
:: 启用变量延迟,用 ! 括起来的变量，就是要动态捕获扩展的目标变量，如果不需要，可以继续使用 % 括变量。
SETLOCAL ENABLEDELAYEDEXPANSION

:: NOWDAY 針對中文系統日期格式：2018/03/09 周五
::SET NOWDAY=%date:~0,4%%date:~5,2%%date:~8,2%
::%date:~10,4%
:: NOWDAY 針對英文系統日期格式：Fri 03/09/2018
SET NOWDAY=%date:~10,4%%date:~4,2%%date:~7,2%
ECHO %NOWDAY%

:: 設置源路徑為當前路徑
SET SRC_DIR=C:EEftpToBigData
SET DATA_DATE_FILE=%SRC_DIR%data_dateDATA_DATE.TXT
SET LOG_FILE=%SRC_DIR%logsunload_EE.log%NOWDAY%
ECHO %SRC_DIR%
ECHO %DATA_DATE_FILE%
ECHO %LOG_FILE%

:: 讀取配置文件
for /f "tokens=1-2 delims==" %%i in (C:EEftpToBigDataconfigunload_conf_EE.ini) do (
If /i "%%i"=="db_server" SET db_server=%%j
If /i "%%i"=="db_name" SET db_name=%%j
If /i "%%i"=="db_user" SET db_user=%%j
If /i "%%i"=="db_password" SET db_password=%%j
If /i "%%i"=="unload_data_folder" SET unload_data_folder=%%j
If /i "%%i"=="log_save_days" SET log_save_days=%%j
)

rem add 20211229 delete file
echo delete file begin
cd %unload_data_folder%
set daysago=30
>dstdate.vbs echo LastDate=date()-%daysago%
>>dstdate.vbs echo FmtDate=right(year(LastDate),4) ^& right("0" ^& month(LastDate),2) ^& right("0" ^& day(LastDate),2)
>>dstdate.vbs echo wscript.echo FmtDate
for /f %%a in ('cscript /nologo dstdate.vbs') do set "dstdate=%%a"
set dstdate=%dstdate:~0,4%%dstdate:~4,2%%dstdate:~6,2%
setlocal enabledelayedexpansion
for /d %%a in (*) do (
if "%%~ta" leq "%dstdate%" (
set "folderdate=%%~na"
if "!folderdate!" leq "%dstdate%" if exist "%%a" rd /s /q "%%a"
)
)
endlocal
del /q dstdate.vbs
echo delete file end

echo %db_server%
ECHO ======Start unload data at %date%%time%======>>%LOG_FILE%
echo export start
(
::判斷是否有手動輸入會計日期, 存入本地文件DATA_DATE.TXT, %1為輸入的第一個參數
if not "%1"=="" (
echo abc
DEL /q %DATA_DATE_FILE%
ECHO %1>>%DATA_DATE_FILE%
) else (
::echo 123
bcp "SELECT CONVERT(varchar(8),GETDATE(),112)" queryout %DATA_DATE_FILE% -c -S %db_server% -U%db_user% -P%db_password% -t "0x1d" -C65001
)
echo teste
SET /p BATCH_DATE=<%DATA_DATE_FILE%

mkdir %unload_data_folder%!BATCH_DATE!

::导出数据文件
bcp "EXEC eximtrx.EXPORT_TO_BIGDATA 'EXIMDB.eximtrx.ACCT_MASTER','D_RELE_DATE'" queryout %unload_data_folder%!BATCH_DATE!ACCT_MASTER.TXT -c -S %db_server% -U%db_user% -P%db_password% -t "0x1d" -r "0x0a" -C65001 -d EXIMDB

:: 新建空文件FILE.LIST
ECHO=>%unload_data_folder%!BATCH_DATE!FILE.LIST

) >>%LOG_FILE%

ECHO ======End unload data at %date% %time%======>>%LOG_FILE%

ECHO ======Start ftp data at %date%%time%======>>%LOG_FILE%
@Echo Off
cd /d %~dp0
set localPath=C: emp
::PROD
set ftpip=192.168.108.87
set username=ftp_ee
set password=password
set ftpPath=/TFB_DATA/EE
for /f "tokens=*" %%i in ('dir/b/tc/od %localPath%') do set fn=%%i
Echo open %ftpip% >ftp.up
Echo %username%>>ftp.up
Echo %password%>>ftp.up
Echo cd %ftpPath% >>ftp.up
Echo mkdir %fn% >>ftp.up
Echo cd %fn% >>ftp.up
Echo binary>>ftp.up
Echo prompt >>ftp.up
Echo mput %localPath%%fn%*.*>>ftp.up
Echo bye>>ftp.up
FTP -s:ftp.up
del ftp.up /q
::Pause
ECHO ======end ftp data at %date%%time%======>>%LOG_FILE%