December 6, 2010 at 7:49 am
Hi,
I want to export data from sqlserver to text file on daily basis.While doing this if the files are older than 5 days in the destination folder then thst files should be automatically delete.How can i do this?
Pleaseeeeeeee helpppppppppp meeeeeeeeeee.
Cheers
Abhas.
December 6, 2010 at 12:22 pm
See my answer here:
http://qa.sqlservercentral.com/Forums/Topic1030738-364-1.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 6, 2010 at 4:38 pm
Abhas,
You can use the Execute Process Task and File System Task to perform your operations. Create the necessary variables so that you can configure your package and applying these variable in expressions to configure your tasks to execute as desired.
It's likely that this will all or at least in part, be contained in a Foreach loop container so that you can cycle through files.
Paul
December 7, 2010 at 1:15 am
Paul_Blackwell (12/6/2010)
Abhas,You can use the Execute Process Task and File System Task to perform your operations. Create the necessary variables so that you can configure your package and applying these variable in expressions to configure your tasks to execute as desired.
It's likely that this will all or at least in part, be contained in a Foreach loop container so that you can cycle through files.
Paul
How would you use the Execute Process Task? Can you give an example?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 7, 2010 at 5:32 am
Hi Paul,
Can you give example and explore it?
December 8, 2010 at 2:48 am
Abhas,
Go ahead and look at the attachment. I don't know what I'm doing wrong on the Execute Process task (UnZip File), but it's not unzipping (expanding) the zipped log file properly.
Expand the project to a location and update the package variables to point to the new paths. Look at the expressions on the conntections as well as in the Foreach loop.
Sorry I didn't take more time to annotate; I've had a long day.
Good luck and if you need me to explain anything, send me a message.
Paul
December 8, 2010 at 12:37 pm
Hi abhas
To delete the files from Destination Folder
U need to follow the following steps
Create one variable varFilePath
Use a for each loop container,which iterate through each file in Destination Folder and assign the path of the file to variable varFilePath
Use one script task inside ForeachLoop Container,the task will first get the created date of the file and then calucalte the difference between
current date and created date ,if the difference is more than 5 days then it will delete the file from the Destination Folder
Write the following code in ScriptTask(the code is in vb)
Again U need to pass variable varFilePath in script task as read only.
Public Sub Main()
Dim path As String
path = Dts.Variables("User::varFilePath").Value.ToString()
If File.Exists(path) = False Then
Dts.TaskResult = Dts.Results.Failure
Return
End If
Dim fileCreateDate As Date = File.GetCreationTime(path)
Dim tsTimeSpan As TimeSpan
Dim noOfDays As Integer
tsTimeSpan = Now.Subtract(fileCreateDate)
noOfDays = tsTimeSpan.Days
If noOfDays >= 5 Then
My.Computer.FileSystem.DeleteFile(path)
Dts.TaskResult = Dts.Results.Failure
Else
Dts.TaskResult = Dts.Results.Success
End If
Dts.TaskResult = Dts.Results.Success
End Sub
Hope this will solve your problem
In case any query let me know
December 17, 2010 at 10:47 am
Thanks rasmi.
Now i get the following error.
Error: Failed to lock variable "user::varFilePath" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
also i have declared varFilePath readonly in script task.
December 20, 2010 at 2:19 am
Hi abhas
I think you have declared a variable user:varFilePath in your script task as read only,change it to User:varFilePath.
I have a sample project,if you want I'll send it to you
Bye
Rashmi
December 20, 2010 at 7:57 am
Thank you rashmi!
Its working now.
But can i do these by another way? Can i copy only those files from source to destination which are 90 days old only.
currently i am copying all files from source to destination and then delete old files from destination. Instead i want to copy only new files.Is this possible?
Thanks
Abhas.
December 21, 2010 at 12:25 am
Hi Abhas
Yes,You can copy only those files from source to destination which are 90 days old only.
Do the same as i said, the only change you need in your programme is
Use a for each loop container,which iterate through each file in SourceFolder
Write the folloeing code in ScriptTask
Public Sub Main()
Dim SourceFilepath As String
Dim destFolder As String
Dim destFilePath As String
Dim SourceFileName As String
SourceFilepath = Dts.Variables("User::varFilePath").Value.ToString()
destFolder = "D:\Test\Destination\"
SourceFileName = SourceFilepath.Substring(SourceFilepath.LastIndexOf("\") + 1)
destFilePath = destFolder & SourceFileName
If File.Exists(SourceFilepath) = False Then
Dts.TaskResult = Dts.Results.Failure
Return
End If
Dim fileCreateDate As Date = File.GetCreationTime(SourceFilepath)
Dim tsTimeSpan As TimeSpan
Dim noOfDays As Integer
tsTimeSpan = Now.Subtract(fileCreateDate)
noOfDays = tsTimeSpan.Days
If noOfDays <= 90 Then
System.IO.File.Copy(SourceFilepath, destFilePath, True)
Dts.TaskResult = Dts.Results.Failure
Else
Dts.TaskResult = Dts.Results.Success
End If
Dts.TaskResult = Dts.Results.Success
End Sub
Note :Change the path of destination folder "D:\Test\Destination\" before you run
Hope this will solve your problem
Incase any query let me know
Regards
Rashmi
December 24, 2010 at 12:51 am
Hi Rashmi,
Thanks it works but not fit in my scenario.
Actually all files are loaded at root level only.
My source is:D:\FolderA
in the same folder there are other 4 sub folders say
D:\FolderA\test1
D:\FolderA\test2
D:\FolderA\test3
D:\FolderA\test4
and test 1 to test4 contains files.I want to copy as same scenario at the destination.i.e i want to put files from F:\FolderA\test1 and so on
But above code copy all files at D:\FolderA\ level only.
does it possible?
thnaks
December 24, 2010 at 1:36 am
Hi abhas
Yes ,it is possible to get the file from test1 through test4 .In your ForeachloopEditor select the connection tab and then select the checkbox Traverse Subfolders.
Hope this will solve your problem
Incase any query let me know
Regards
Rashmi
December 24, 2010 at 4:02 am
Hi rashmi,
i already checked Traverse Subfolders but not working.
December 25, 2010 at 3:28 am
Hi abhas
The last solution works fine.I have attached a sample project .Try it out
In case any query let me know
Regards
Rashmi
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply