Friday 11 March 2016

How to rename files in SSIS using Script task?


Input Filenames(under my root path folder): 

DW_DATA_MAS_UNIT_03112016
DW_DATA_MAS_PARCEL_03112016
DW_DATA_TRAN_ACCOUNT_03112016
DW_DATA_TRAN_BANKDETAILS_03112016
DW_DATA_CH_ACCDETAILS_03112016

Output(in the same root path folder): I just segregated Master,Transaction and Child tables based on the keywords(MAS,TRAN,CH) present in input file.

MASTER_DW_DATA_MAS_UNIT_03112016
MASTER_DW_DATA_MAS_PARCEL_03112016
TRAN_DW_DATA_TRAN_ACCOUNT_03112016
TRAN_DW_DATA_TRAN_BANKDETAILS_03112016
CHILD_DW_DATA_CH_ACCDETAILS_03112016

Code to achive the same :

string rootfolderPath = Dts.Variables["User::SOURCE_PATH"].Value.ToString();

string[] files = Directory.GetFiles(rootfolderPath, "*.xml", SearchOption.AllDirectories);

  foreach (string s in files)
{
string trgt = "";
if(Path.GetFileName(s).contains("MAS_"))
{
targt = Path.GetDirectoryName(s) +"\\MASTER_"+ Path.GetFileName(s);
}
else if(Path.GetFileName(s).contains("TRAN_"))
{
targt = Path.GetDirectoryName(s) +"\\TRAN_"+ Path.GetFileName(s);
}
else if(Path.GetFileName(s).contains("CH_"))
{
targt = Path.GetDirectoryName(s) +"\\CHILD_"+ Path.GetFileName(s);
}

}

if (!File.Exists(targt))
{
File.Move(s, targt);
}

Explanation: 

Firstly, add a variable(to store the Root Path of the files location) and a Script Task(to rename files using C#.net).

1)
Change the variable datatype to String and assign it with a RootFolder path value.

For Ex: C:\Users\kn\Documents\NewFolder

2) 
Make the variable as ReadOnly in Script Task.

 string rootfolderPath = Dts.Variables["User::SOURCE_PATH"].Value.ToString();

Above Line copies the root path into a string variable rootfolderPath.(value is being pulled from SSIS variable)

string[] files = Directory.GetFiles(rootfolderPath, "*.xml", SearchOption.AllDirectories);

Above Line reads out all files of specified type(in root folder and sub folder directories) and store it into a array called "files".

3)
Next step is to loop through each file in an array and rename it based on the certain condition.

foreach (string s in files)
{
string trgt = "";
if(Path.GetFileName(s).contains("MAS_"))
{
targt = Path.GetDirectoryName(s) +"\\MASTER_"+ Path.GetFileName(s);
}
else if(Path.GetFileName(s).contains("TRAN_"))
{
targt = Path.GetDirectoryName(s) +"\\TRAN_"+ Path.GetFileName(s);
}
else if(Path.GetFileName(s).contains("CH_"))
{
targt = Path.GetDirectoryName(s) +"\\CHILD_"+ Path.GetFileName(s);
}
}

Example : 

Before Change : C:\Users\kn\Documents\NewFolder\DW_DATA_MAS_UNIT_03112016.txt
After  Change : C:\Users\kn\Documents\NewFolder\MASTER_DW_DATA_MAS_UNIT_03112016.txt

Note: 
Path.GetDirectoryName(C:\Users\kn\Documents\NewFolder\DW_DATA_MAS_UNIT_03112016.txt) will return the file Parent Directory i.e C:\Users\kn\Documents\NewFolder.

Path.GetFileName(C:\Users\kn\Documents\NewFolder\DW_DATA_MAS_UNIT_03112016.txt) will return file name from fully qualified path i.e DW_DATA_MAS_UNIT_03112016.txt

Variable "s" holds the existing filename and the variable "trgt" holds the new filename.

Now with the help of methods under File Class we can rename file.

if (!File.Exists(targt))
{
File.Move(s, targt);
}

Above Code will rename file by ensuring the destination location for the target filename.

Note : 
File.Exists(destination_path)
File.Move(source_path,destination_path)

Same can be done in SSIS using FOR EACH LOOP Container, File System Task and Expression Task as well.

No comments:

Post a Comment