HI,
I have a requirement to pick up the files that have the updateDatetime in the last hour and move these files from one folder to the other.
How can this be accomplished.
Please suggest.
Thanks
use a foreach loop. inside the loop, use a script task to look at the file info for each file to get the updatedatetime.
Thank you can you please let me know what the script task should be coded to get the updatedatetime of the files.
You can find what you need here: FileInfo Class
The property you want is:
LastWriteTime
Gets or sets the time when the current file or directory was last written to.(Inherited from FileSystemInfo.)
i used the below code in the script task but its throwing an error please suggest. This is new area for me.
Thanks,
Code used is
public void Main()
{
// TODO: Add your code here
string[] files = System.IO.Directory.GetFiles(@"\SDCDCXX02\NWH\EDI\Archive");
DataTable NewList = new DataTable();
DataColumn col = new DataColumn("FileName");
NewList.Columns.Add(col);
System.IO.FileInfo finf;
foreach (string f in files)
{
finf = new System.IO.FileInfo(f);
if (finf.LastWriteTime > DateTime.Now.AddHours(-24))
{
NewList.Rows.Add(f);
}
}
Dts.Variables["User::FileNameArray"].Value = NewList;
Dts.TaskResult = (int)ScriptResults.Success;
}
the error msg is
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
I think you omitted the first part of the error message. the part that actually states the error. what I can see is where the error happened, not what happened
How is the variable FileNameArray defined in your ssis package? What data type (I think it will have to be object).
It looks like you;re preprocessing the files to build a list of which ones to process. I'd probably it simpler.
use a for each file container. then with the loop, use a script task to do the filtering based on last write time like the code you have, returning a boolean. If true continue with the next task, otherwise go to the next file.
Thank you for the quick response.
That was the whole error message that i received.
FileNameArray is a string and when i changed that to the Sting and had a file Task to move the files picked up the variable to an other folder its not doing as the variable for the file task is looking for a string type.
Any help is greatly appreciated.
If FileNameArray is a string, you can't assign a Datatable to it. Try the other method I suggested
I should mention that if you google SSIS Process Files you'll get tons of hits on this
If all you need is to move the files - and not process them or use them as a source - then use a script task to check for the last write time and if that meets your requirements you can then 'move' the file.
Moving the file can be either a copy/delete - or it can be a rename operation. In your code, instead of have NewList.Rows.Add(f) - replace that line with File.Rename(...) which will then move the file.
If you are actually going to be processing those files - then it would be much better if you identified the process you are trying to accomplish. We could probably help you identify a better approach.
what I said!
One difference - you don't need a foreach loop at all. Just a script task that gets the directory of valid files and copies/moves them to the new location.
If that is all that needs to be done - I wouldn't use SSIS - I would just build a Powershell script to move the files. Since the O/P is using SSIS I would assume that there is another reason for this process and if the O/P would share the intent of the process we could probably come up with a much better solution.
For example - archiving a file that was just processed in a foreach container can be done using a simple file system task. But if you need to move the file to an archived folder you have to use a script task that copies and then deletes the file so the compressed setting is applied. In that script task - you need at least one input parameter (file) and then you just use File.Copy(source, destination, true); File.Delete(source).
Or - you use 2 file system tasks - one to copy the file and one to delete the source file which is probably much simpler than using a script task.
Those tasks sit inside the foreach container in SSIS and get the values from the variable(s) returned by the container.
It all depends on what the O/P is trying to accomplish apart from just copying/moving files from one location to another.
even a simple PoSH agent job would suffice
Agreed - again it depends on what the OP needs to do with the files.