February 3, 2011 at 12:23 am
Lynn Pettis (2/2/2011)
May I ask why you are using RENAME instead of MOVE?
I believe that is has something to do with the fact that you can't rename the file when using MOVE. I ran into this error/warning once when I set the properties using expressions. I should test it again to see what the exact issue is.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 3, 2011 at 2:02 am
That sounds like the file copy is being blocked/slowed by something on the server...anti virus or something like that.
Do you have access to the server that the backup files are stored on?
Is this a shared backup area? If so, is there other backup files being written to the same location at the same time?
February 3, 2011 at 6:43 am
Lynn,
From one of the blogs that I looked at it, it suggested rename. I copied it from there. I can try to see if move makes a difference.
Here is the blog:
http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
February 3, 2011 at 6:47 am
grahamc (2/3/2011)
That sounds like the file copy is being blocked/slowed by something on the server...anti virus or something like that.Do you have access to the server that the backup files are stored on?
Is this a shared backup area? If so, is there other backup files being written to the same location at the same time?
Hi Graham,
Yes, I have access to the backup server.
There is a top level share with backup folders underneath it for each server.
Backup Server
ServerA
---Bak Files
---Trn Files
ServerB
---Bak Files
---Trn Files
The FileSystemTask works well for small files. I will try to see if FileSystemTask move makes a difference. I've already tried the script task with File.move and the behaviour was the same.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
February 3, 2011 at 7:56 am
are you writing multiple files at the same? causing large disk queues?
is anti virus scanning those folders?
February 3, 2011 at 9:41 am
Graham,
I am using a sequence container, with precedents.
I do a foreach loop and then the filesystem task. So I believe this is the only operation going on a time. So, it should be only one file at a time.
I do want to do parallel tasks once I get this going though. But not yet.
Yes, the servers are being scanned by anti-virus.
Tony.
Things will work out. Get back up, change some parameters and recode.
February 9, 2011 at 10:28 am
Ok, I just wanted to wrap up this post.
So I went around and around trying to move files with the FileSystem task or the Script Task. It worked fine on all files except the big ones. As soon as the package steps got to one of the bigger files, the pacakge would just stop.
I looked at the code over and over again. I tweaked it, searched the blogs and forums and tweaked it some more.
This is the solution that I came up with:
1) Created a table in my database called tblYesterday with one column called yesterday.
2) Created a ExecuteSQL Task in SSIS to update the table each day with the previous day's date in a string ("20110208").
3) Created a Data Flow to export the table to a text file (yesterday.txt)
4) I wrote two batch files.
5) The first batch file moves all the files from the network backup folder to the regular backup folders.
6) The second batch file looks for a text file called yesterday.txt. It gets the value of that file and puts it into a variable. It then moves all backups from each backup share folder by looking for the variable (i.e. \\backup share\--bak files\*%yesterdayVariable%*.bak)
7) I then created another variable to get all the SQL Server 2008 backups as the naming convention for SQL Server 2008 backups changed.
8) I then get all the backps files with this second variable as in step#6.
9) I then scheduled these two batch files to run each morning with Task Scheduler.
End result. Everything works perfectly. It takes all of 1 - 2 minutes to move all these files around regardless of the size of the files.
I learned a lot about SSIS. It was a good initiation to resolving challenges with SSIS.
Ok..now for the next project..creating the data warehouse tables for the new reporting system (using SSRS of course). 😎
Tony
Things will work out. Get back up, change some parameters and recode.
February 9, 2011 at 11:19 am
Great! Glad that if finally worked out.
Thanks for posting back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2011 at 2:16 pm
Graham,
No, I was processing the files one at a time. I'm not sure who to check the queue.
Yes, antivirus scanning is running on the server.
Tony
Things will work out. Get back up, change some parameters and recode.
February 13, 2011 at 1:03 pm
Did you ever discover the break point? You have used the terms "small files" and "large files" throughout the thread. Did you ever discover the file size that changes from "small" to "large" and consequently from fast transfer to slow transfer?
(Just wondering if it has to do with the 4gig, 32bit limitation)
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply