June 19, 2011 at 1:10 pm
I have a folder in D drive called Log, I would need to copy all files in the log folder to anther folder in D drive called Archive.
How I can do that via a ssis script task?
Thanks,
June 19, 2011 at 3:36 pm
You could do this work with a Script Task but it will be a whole lot more code than using PowerShell. Here is a one line PowerShell command to copy all files in one directory older than 60 days to another directory.
ls -Path D:\logfiles\*.log |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Copy-Item -Destination D:\logfiles\archive\
Here is one possible solution for you that would have the log file copy process be separate from whatever else you were planning to do in SSIS:
1) save the above command in a file with a .ps1 extension (a PowerShell script)
2) schedule the script to run through PowerShell periodically using SQL Agent or Windows Task Scheduler
If you need the file copy operation to be coupled with what you're planning to do in SSIS then here is another possible solution:
1) save the above command in a file with a .ps1 extension (a PowerShell script)
2) execute the PowerShell script using an Execute Process task from within your SSIS package
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 6:58 am
Script tasks in SSIS work with C# and VB.Net. If you don't know these languages, I advise you to steer clear of the Script Task and use the File System task to copy (or move, which deletes the file at the source) the file instead.
But I believe the command in VB.Net is literally COPYFILE. (I'd have to double-check).
June 20, 2011 at 7:56 am
Hi Brandie, It would be ideal if SSIS contained component(s) to do this kind of work without having to use a Script Task or involving PowerShell, I would actually prefer it, however I am failing to see how that could be done. How did you envision satisfying the requirement to only copy files older than 60 days making use of a File System Task, but without making use of a Script Task?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 9:07 am
opc.three,
I think you're mixing this post up with another thread. There is no requirement for 60 days in the OP that I can see. The OP says "all files."
June 20, 2011 at 9:23 am
err...the title of the thread is:
"copy files created in a folder two Months ago to another folder"
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 9:37 am
Then it's my bad.
June 22, 2011 at 9:43 am
I tried to follow Vbscript but fails:
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
' ** evaluate file for copy **
Call evaluateFile()
End Sub
Private Sub evaluateFile()
Try
Dim filePath As String = Dts.Variables.Item("User::FileName").Value.ToString
Dim evalDate As DateTime = Now.AddMonths(1)
Dim filedest As String = Dts.Variables.Item("User::MoveFileloc").Value.ToString
'Dim fileCreateDate As Date = IO.File.GetLastWriteTime(filePath)
If CDate(IO.File.GetLastWriteTime(filePath).ToShortDateString) < evalDate Then
IO.File.Copy(filePath, filedest)
End If
Dts.TaskResult = Dts.Results.Success
Catch
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class
Can somebody take a look it and tell me where it is wrong.
thanks,
June 22, 2011 at 9:52 am
I just made the connection to this other post of yours:
http://qa.sqlservercentral.com/Forums/Topic1100808-149-1.aspx
Since .NET is not your forté why not use the one-line PowerShell script I provided?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2011 at 10:32 am
I'm with opc.three. Can you implement PowerShell in your environment?
If not, post the errors (exact error messages) along with the script.
Do these file names have dates on them? Can you put dates on them? It would make your life so much easier, if you could.
And do new files come into this directory at all or are these all two months or older?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply