question

MrJ-0519 avatar image
0 Votes"
MrJ-0519 asked Monalv-msft edited

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

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




sql-server-generalsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please edit your question and add desired output.
Also, what is your starting point?

I have a variable called RenameFileName

What is its initial value?

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

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"
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

level6 avatar image
0 Votes"
level6 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @MrJ-0519 ,

We can test the expression in the expression of variable.
67422-variables.png

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"

67421-expression1.png

Expression2:

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

67392-expression2.png

Expression3:

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

67326-expression3.png

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.




expression3.png (5.4 KiB)
variables.png (15.1 KiB)
expression1.png (8.7 KiB)
expression2.png (6.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MrJ-0519 ,

May I know if you have anything to update?

Best Regards,
Mona

0 Votes 0 ·