FileSystemTask - move very slow to move 7 GB files?!!

Question

FileSystemTask - move very slow to move 7 GB files?!!

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

grahamc SSCertifiable Points: 6762 More actions · Answer 2

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?

WebTechie SSChampion Points: 11405 More actions · Answer 3

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.

WebTechie SSChampion Points: 11405 More actions · Answer 4

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.

grahamc SSCertifiable Points: 6762 More actions · Answer 5

are you writing multiple files at the same? causing large disk queues?

is anti virus scanning those folders?

WebTechie SSChampion Points: 11405 More actions · Answer 6

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.

WebTechie SSChampion Points: 11405 More actions · Answer 7

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.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

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

WebTechie SSChampion Points: 11405 More actions · Answer 9

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.

dlatch SSC Veteran Points: 269 More actions · Answer 10

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)