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

MrJ 1 Reputation point
2021-02-11T13:26:45.273+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,640 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,328 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2021-02-11T16:05:02.157+00:00

    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"
    
    0 comments No comments

  2. Jim Young 1 Reputation point
    2021-02-11T16:50:03.68+00:00

    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.

    0 comments No comments

  3. Monalv-MSFT 5,881 Reputation points
    2021-02-12T09:22:58.447+00:00

    Hi @MrJ ,

    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.