Saturday, February 25, 2017

SSIS File SystemTask to dynamically create a Folder, Copy and Rename a File

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\


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..
RenamDestFileC:\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 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.