But there is a workaround with little bit of stage setting.
Suppose an excel file is to be created on a daily basis and the name of the file should be the current date (20100110, 20100111 etc.)
First it is required to create a template excel file (same as the file required to be created daily). Then a file system task to copy this template file to the destination folder (folder where the new file is to be created). Then use another file system task to rename the copied file with system date.
Step1: Create a template excel file (Sample.xls) in C:\ABC folder
Step2: Create 3 variables (string type and package scoped) named :
Dest with value as :C:\XYZ (folder where new file is to be created)
Final with "Evaluate as Expression" property set as TRUE. then set the
expression as: "C:\\XYZ\\" +REPLACE(SUBSTRING( (DT_WSTR,30)GETDATE(),1,10),"-","")+ ".xls"
Rename with value as : C:\XYZ\Sample.xls
Step3: Take a File System Task and open the File System Task editor.
Select Operation as "Copy File"
Set IsDestination path variable to TRUE and select Dest from drop down box as variable.
Keep IsSourcePath variable as FALSE and create a connecion manager for C:\ABC\Sample.xls
Step4: Take one more File System Task and open the File System Task editor.
Select Operation as "Rename File"
Set IsDestination path variable to TRUE and select Final from drop down box as variable.
Keep IsSourcePath variable as TRUE and select Rename from drop down box as variable.
Select the 2nd File System Task, hit F4 (to go to its properties) and set "Delay Validation" as TRUE. The new excel file with current date as its name can be verified in folder C:\XYZ
I am having issues with the: Rename with value as : C:\XYZ\Sample.xls
ReplyDeleteI am using UNC names is that an issue? I keep getting invalid character in path
I used the above and it really helped!!
ReplyDeleteThank You,
Sungeeta
Get stuff... really works... very helpfull...
ReplyDeleteThis was very helpful and straightforward. Thanks.
ReplyDeletethank you!!!
ReplyDelete