将日期从SSIS包传递到“添加日期后缀到文件名”批处理文件例程

I have an SSIS Package that passes a date param from a Package variable to a Batch file as a parameter. The intent of the Batch file is to rename a predetermine file in an predetermined location such a date suffix is added to the file name where teh date suffix is in the format "_YYYY_MM."

我有一个SSIS包,它将一个日期参数从一个Package变量传递给一个Batch文件作为参数。批处理文件的目的是在预定位置重命名预定文件,例如将日期后缀添加到文件名中,其中日期后缀的格式为“_YYYY_MM”。

The issue that I am having is that when I run the SSIS BIDS 2005 package in the IDE, the following batch file renames the file without a leading zero in the month. however, when I run it through our scheduler on our test server, the file is properly renamed so that the month has a leading zero.

我遇到的问题是,当我在IDE中运行SSIS BIDS 2005软件包时,以下批处理文件在该月重命名该文件时没有前导零。但是,当我通过测试服务器上的调度程序运行它时,文件被正确重命名,以便月份具有前导零。

Is perhaps due to a potential difference in drivers?

也许是由于司机的潜在差异?

Here is the code for the batch file:

以下是批处理文件的代码:

ECHO OFF
SET CurrentFolder=%~dp0
SET ThisFileName=%~n0

REM The following line populates shared path variables used below
CALL %CurrentFolder%\Membob_SetPaths.cmd

SET LogFile=%LogFolder%\%ThisFileName%.log
ECHO %date% %time%  - *** BOJ %CurrentFolder%%ThisFileName%.cmd *** >> %LogFile%

SET ReportingDate=%1 
ECHO Logging to: %LogFile%
ECHO ReportingDate=%ReportingDate% >> %LogFile%

REM Rename the file from MEMBOB.csv to MEMBOB_YYYY_MM.csv, using the passed ReportingDate 
REM But before we rename, delete any file that might already have that target name.

@For /F "tokens=1,2,3 delims=/ " %%A in ("%ReportingDate%") do @( 
Set Month=%%A
Set Day=%%B
Set Year=%%C
)
REM if %Month% LSS 10 Set Month=0%Month%
SET NewFileName=MEMBOB_%Year%_%Month%.csv

if exist %ExportFolder%\%NewFileName% del %ExportFolder%\%NewFileName%

REM Rename the file as described above
ECHO Rename %ExportFolder%\Membob.csv to %NewFileName% >> %LogFile%
rename %ExportFolder%\Membob.csv %NewFileName% 

ECHO %date% %time%  - *** EOJ %CurrentFolder%%ThisFileName%.cmd *** >> %LogFile%

I am really a noob with batch files.

我真的是批处理文件的菜鸟。

What I would like to do is this, if someone can help:

我想做的是,如果有人可以提供帮助:

Adjust the code so that it tacks on a leading zero if the length of the month when evaluated as a string is < 2.

如果作为字符串计算的月份长度<2,则调整代码以使其在前导零上固定。

Secondly, I would like this rename file procedure to be more generic so that I can pass in the file name full path as an additional param and have a suffix tacked onto ANY file.

其次,我希望这个重命名文件过程更通用,以便我可以将文件名完整路径作为附加参数传递,并在ANY文件上添加后缀。

Can someone with more BATCH prowess assist?

拥有更多BATCH能力的人可以提供帮助吗?

1 个解决方案

#1

Since SSIS sucks a little less than batch files, another option is to get the package to format the date with a leading zero in teh month:

由于SSIS比批处理文件少一些,所以另一种选择是让软件包格式化日期,以及月份中的前导零:

Right("0"+ (DT_WSTR, 2) MONTH( @[User::ReportingMonth]  ) ,2) + "/" + Right("0"+ (DT_WSTR, 2) DAY( @[User::ReportingMonth]  ) ,2) + "/" + (DT_WSTR, 4) YEAR( @[User::ReportingMonth]  )