CODEWITHSUNDEEP
sql-serverexcelssisexpressionetl
Tirath Singh
Tirath Singh

Reputation: 51

How to rename excel file name dynamically?

I'm trying to change excel file dynamically whenever I run my job on date basis.

In connection string of excel connection manager i have tried:

"C:\ReportOutput" + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()) ,2)+
RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()),2)+
(DT_WSTR,4)DATEPART("yyyy",GetDate()) +"_withdata.xls"

But it is not working. Also in connection manager I have provided the file location. I want file to create and rename dynamically every time the job runs.

Upvotes: 2

Views: 1411

Answers (2)

JIKEN
JIKEN

Reputation: 337

You need follow 2 steps to rename excel file name dynamically.

In 1st Step, Create 3 variables with String DataType

  1. OutputFolderPath: This is Static variable in which we store the OutPut Folder Name (i.e.:C:\)

  2. Date_Time_Dynamic: This is a Dynamic Variable used to Store the Date Time value (Eg : "20190909_114500" ) Calculated from GetDate() funcation using below Expression..

With Time: REPLACE(REPLACE( REPLACE(SUBSTRING((DT_WSTR,50)GETDATE(),1,19),"-",""),":","")," ","_")
Without Time: REPLACE(SUBSTRING((DT_WSTR,50)GETDATE(),1,10),"-","")

  1. OutputFileFullPath: This is an Important Dynamic Variable used in the Package to create and Output File dynamically with Date Time suffix to the Template File name done by File System Task. This file will passed dynamically to the Excel Destination connection in the Data Flow, to save the output. This variable value is Calculated using the below Expression..

    @[User::OutputFolderPath] + "ReportOutput_"+ @[User::Date_Time_Dynamic]+".xls"

e.g.: C:\ReportOutput_20190909_114500.xls - With Time
C:\ReportOutput_20190909.xls - Without Time
Note: Need to add time as suffix. So,it will always create new file at destination folder.

In 2nd Step, make the Excel connection to the Excel File is dynamic, by assigning the ExcelFilePath to a Variable from Properties > Expressions as shown below...

ExcelFilePath = @[User::OutputFileFullPath]

enter image description here

Upvotes: 2

Hadi
Hadi

Reputation: 37368

I think that the main problem is that you missed the escape character, also you can use DAY(), MONTH(), YEAR() instead of DATEPART(): 

"C:\\ReportOutput" + 
RIGHT("0" + (DT_WSTR,2)DAY(GetDate()) ,2) +
RIGHT("0" + (DT_WSTR,2)MONTH(GetDate()),2)+
(DT_WSTR,4)YEAR(GetDate()) +"_withdata.xls"

Upvotes: 3

Related Questions