Saturday, 25 February 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"

---------------------------------------------------------------------------------------------------------------
Happy Learning...
TAMATAM


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts