May 29, 2008 at 8:46 am
Hi to All.
Situation--I have a folder D:\Folder. Inside the folder i will have 14 .txt files to load. here i have looped over that folder with foreachloop to load files to sql table.
Now, Once I load I want to move those files and also rename it in logical fashion.
---I have a subfolder called prior inside D:\Folder, and also another subfolder within prior called Archive, so everything is like this-----
D:\Folder
D:\Folder\Prior
D:\Folder\Prior\Archive
What I want to do is--- Once i load all 14 .txt files from D:\Folder, i want to move those all files to D:\Folder\Prior and also rename them acoordingly with the current date, up to seconds. Example If file name was Atlanta.txt, i want it to be Atlanta_20080507201023.txt
Also i want to move all the files in the D:\Folder\Prior to move to D:\Folder\Prior\Archive.
I know we can do it , but i am so confused with th eorder and logic of the sequence.
Any suggestions, code and inputs will be highly appreciated.
Thanks
May 29, 2008 at 8:36 pm
One possible approach is to first move the files in \Prior\ to \Archive\.
Then run your foreach loop through the files in \Folder\.
When you finish processing each file, use the saved file name to build
a variable with the file extension and a variable with the file name without the file extension. You can use these with the DatePart
function to build the output file name. Then write the output file to
\Prior\.
After all files are processed, delete any files in \Folder\.
So far, the only way I know to do the first and last steps is by calling a DOS batch script. Perhaps someone will respond with a better way.
May 29, 2008 at 10:12 pm
May 29, 2008 at 10:21 pm
Kbata thanks for ur inputs. there are several issues in this case. First i need to keep all those files in the Prior also, becoz we use third party tool called, DMExpress to compare today new files with yesterday files and differnce will be my input files for bulk inserts.
Another issue also if i run package for the first time there will not be any files in the prior folder to rename and move to archive folder, so i have to use dateadd function to rename file with prior date.
what i did was ( i know my solution is not optimized). I have bulk insert within foreach loop and at same time i copy those files to Prior folder. then after several other steps i rename those files using several date and time functions within for each loop. after that i move those files to Archive folder within for each loop, and then lastly i used another for each loop to move agian those files from Folder to Prior so that i have those files for next day to copmare with new coming files.
June 8, 2008 at 10:34 am
Gyanendra,
Would you please tell me more about how did you add date extension in file before moving to archive folder?
I am also in the same situation as yours but in my case text file comes in different time what is the best way to handle in this case run packagae every hour ? or is there any way to handle to see if file arrived?
Thanks
June 8, 2008 at 4:55 pm
One approach you may consider is to use a for each file enumerator which simply reads all the files into a control table of some sorts.
From here, you can establish what has been processed, and derive a "Files to process" result. This result you can place in a For Each ADO enumerator, from where you can do the processing that you require.
Other options include for example having a scripting task and moving your decisionmaking in here, but careful, it may become a bit top heavy on the maintenance.
Good luck
~PD
June 8, 2008 at 5:03 pm
You would most probably require some kind of scripting.
If I understand you correctly, you want to move a file into archiving with a timestamp, and then afterwards perform some kind of check to see if you have processed the file within the past hour or not.
a) Create a scripting task to derive the current timestamp, and populate this into a variable. Call me pedantic, but I would actually have quite a few variables, one for the timestamp (in the converted format), one for the current directory, one for the archive directory, one for the filename to process, one for the filename to archive, and one for the filename to check in archive. Simply put, some of the variables would go into package configurations, whereas others would be calculated dynamically at runtime.
b) Create a scripting task which calculates which file to check for. I would assume that you would be working with timeranges (i.e. if the job runs at 10 minutes after the hour every hour, the filename would be called x). Just remember the golden rule here is dynamics...
c) Create a scripting task which populates a "mustrun" boolean variable. Simply it needs to work out using a normal fileexists .net piece of code if the file to check for exists
d) From here, you can now put a precedence constraint which only runs if the expression mustrun == true.
Good luck
~PD
July 14, 2009 at 3:04 am
Hi All, I too face a similar problem. I need to copy one excel file from a folder and paste it in two different folders. How this can be achieved?
July 14, 2009 at 5:25 am
Not that similar! Two file system copy tasks would seem to meet your requirement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 14, 2009 at 5:34 am
yes i too thought of using it. But what if we need to copy same files to several folders say for eg, 10 different folders. Do we need to create 10 File system tasks?
July 14, 2009 at 5:43 am
Actually it's more elegant than that.
Create a Foreach container with 'item' enumeration and add the folders' names as items in column 0.
Then map a variable to this column.
Then plonk your file system task into the Foreach container, making use of your new variable. It will iterate round the items you have just created.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 14, 2009 at 7:35 am
Create few varaibles It will be easy for u . Like variables to hold each folder name. Actually you can copy rename and archieve just using one file system task. You have to build expressions for variables.(F4 is the key just in case) and set true for evaluation as expressions.
This gives u the time portion for the file
@ArchTime=(DT_STR, 4, 1252)DATEPART("yyyy", GetDate()) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", GetDate()), 2)+"_" + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GetDate()), 2)
Steps: - Create two variales first
1.FullArchievePathFilename (this will be ur destination)
2.FullSourcePathFileName ( this is ur source)
In the file system task Operation will be "Rename file". Make sure its not copy or move
In my case each above variables is is in expression
using following variables @[User::Drive] + @[User::FolderName]+ @[User::SourcePath] + @[User::MyFileValue] . I just return file name (without path and extension) in my for each loop and used Myfilevalue variables to hold value for each file.
FullArchievePathFileName is in expression using follwoing other variables too
@[User::Drive] + @[User::FolderName]+ @[User::ArchivePath] + @[User::MyFileValue]+ (DT_STR, 4, 1252)DATEPART("yyyy", GetDate()) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", GetDate()), 2)+"_" + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GetDate()), 2) + @[User::FileExtension]
Let me know if u need any thing more
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply