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