Showing posts with label rename filles in SSIS. Show all posts
Showing posts with label rename filles in SSIS. Show all posts

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.