How to create create a Folder dynamically by Today date then Copy and Rename a file from Source using File System Task during Runtime in SSIS
We can do this by using the File System Tasks as follows :
Phase-1 : Create a Source File Connection :
Here we have to Create a Connection for the Source File which we want copy.
Phase-2 : Create 4 Variables ( 1 Static and 3 Dynamic Variables ) as follows :
DestPath
DestiVar
DestFile
RenamDestFile
1.DestPath : This is a Static Variable , to which we will assign the Destination Path ,to where we copy the File.
DestPath = "C:\Users\Tamatam\Desktop\SSIS\Dest1\"
2.DestiVar : This is a Dynamic Variable , to which the value will be defined using the Expression Builder, to Create a Folder with Current Date, in DestPath.
@[User::DestPath] +((DT_WSTR, 5)DATEPART("dd", GETDATE() ))+"-"+((DT_WSTR, 5)DATEPART("mm", GETDATE() ))+"-"+((DT_WSTR, 5)DATEPART("yyyy", GETDATE() ))+"\\"
The Expression result is as follows..
DestiVar = C:\Users\Tamatam\Desktop\SSIS\Dest1\25-2-2017\
Phase-5 : Add a third File System Task to Rename the file in Destination Folder which Copied in above step :
Final Phase-6 : Now the Package with all the Tasks is ready :
Keep all the Task in one Sequence container and run as follows..........
Dynamic Folder Created in Destination with Today Date :
We can do this by using the File System Tasks as follows :
Phase-1 : Create a Source File Connection :
Here we have to Create a Connection for the Source File which we want copy.
DestPath
DestiVar
DestFile
RenamDestFile
1.DestPath : This is a Static Variable , to which we will assign the Destination Path ,to where we copy the File.
DestPath = "C:\Users\Tamatam\Desktop\SSIS\Dest1\"
2.DestiVar : This is a Dynamic Variable , to which the value will be defined using the Expression Builder, to Create a Folder with Current Date, in DestPath.
@[User::DestPath] +((DT_WSTR, 5)DATEPART("dd", GETDATE() ))+"-"+((DT_WSTR, 5)DATEPART("mm", GETDATE() ))+"-"+((DT_WSTR, 5)DATEPART("yyyy", GETDATE() ))+"\\"
The Expression result is as follows..
DestiVar = C:\Users\Tamatam\Desktop\SSIS\Dest1\25-2-2017\
3.DestFile: This is a Dynamic Variable , to which the value will be defined using the Expression Builder, to store the Destination File ( in the Destination created in above step using the variable DestiVar) which is the file coming from Source.
@[User::DestiVar]+"Dataset.xlsx"
The Expression result is as follows..
DestFile = C:\Users\Tamatam\Desktop\SSIS\Dest1\25-2-2017\Dataset.xlsx
4.RenamDestFile: This is a Dynamic Variable , to which the value will be defined using the Expression Builder, to rename the Destination File ( in the Destination created in above step using the variable DestiFile).
@[User::DestiVar]+ SUBSTRING( (DT_WSTR, 30) GETDATE(),1,10 )+".xlsx"
The Expression result is as follows..
RenamDestFile= C:\Users\Tamatam\Desktop\SSIS\Dest1\25-2-2017\2017-02-25.xlsx
Now we have to Design the Control Flow with File System Tasks from the next Phase as follows....
---------------------------------------------------------------------------------------------------------------
Phase-3 : Add a first File System Task to Create Directory/Folder in Destination:
Set the Properties in File System Task Editor as follows...
Phase-4 : Add a second File System Task to Copy File from Source to Destination Folder created in above step :
Connect with above task1 then set the Properties in File System Task Editor as follows...
Phase-5 : Add a third File System Task to Rename the file in Destination Folder which Copied in above step :
Connect with above task2 then set the Properties in File System Task Editor as follows...
Final Phase-6 : Now the Package with all the Tasks is ready :
Keep all the Task in one Sequence container and run as follows..........
Output :
Dynamic Folder Created in Destination with Today Date :
Renamed the file "Dataset.xlsx" in Destination>Date Folder with Today Date :
Other Notes :
(DATEPART("dw", GETDATE() ) == 1 ? "Sunday" : (DATEPART("dw", GETDATE() ) == 2 ? "Monday" : (DATEPART("dw", GETDATE() ) == 3 ? "Tuesday" : (DATEPART("dw", GETDATE() ) == 4 ? "Wednesday" : (DATEPART("dw", GETDATE() ) == 5 ? "Thursday" : (DATEPART("dw", GETDATE() ) == 6 ? "Friday" : "Saturday"))))) )
Expression will result the WeekDay Name as "Saturday"
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------