SSIS 2008: Illegal characters in path when trying to rename a file

Question

I have a task that should rename a file

I have a variable called RenameFileName

\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

When I run the task I get the error

Illegal characters in path

Am I doing anything wrong? Below are a list of the variables I have tried

\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

"\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

"\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

Please note that the following works:

\\servername\csv\BACKUP\ABC.csv

This however does not work

\\servername\csv\BACKUP\"+1+".csv

Thanks

Answer 1

In the date part of the file name, try to use DT_STR instead of DT_WSTR:

+ (DT_STR,4,1252)DATEPART("yyyy",GETDATE())
+ RIGHT("0" + (DT_STR,2,1252)DATEPART("mm",GETDATE()),2)
+ RIGHT("0" + (DT_STR,2,1252)DATEPART("dd",GETDATE()), 2) + ".csv"

Answer 2

The expression that does not work does not look a properly formed expression.

\\servername\csv\BACKUP\"+1+".csv

Are you trying to do string concatenation? If you are then this expression does not look at all valid.

Answer 3

Hi @MrJ ,

We can test the expression in the expression of variable.

Please refer to the following expressions and pictures.
Expression1:

"\\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"  

Expression2:

"\\\\servername\\csv\\BACKUP\\"+ (DT_WSTR, 10)1+".csv"  

Expression3:

"\\\\servername\\csv\\BACKUP\\ABC.csv"  

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.