January 30, 2011 at 5:41 pm
Hello,
Our manager wants us to move the previous days backups to a special folder. The Company network backup backs up that folder. That way, in theory, if the backup server goes down, we have a backup of the backup on the network tape. Ok...Cool.
I've created a package with two sequence containers.
1) One sequence container moves the files from the network backup folder to the original backup folder.
2) The other sequence contianer moves the files to the netwwork backup folder.
My Design
==============================================
I've actually got it working with Foreachloops and filesystem task.
The first Foreach loops are being populated by a recordset and then variables are used on an inner Foreach loop to get the sourcefile and the destinationfile dynamically.
Within that inner Foreach Loop, I have a FileSystemTask - Rename to move the files.
================================================
The problem is that some of our backups are very big. In fact, a couple of databases I have had to stripe into 6 files. Even so, these 6 files are about 6 GB each.
As I was debugging my package, I noticed that when it came to move the big files, it took a long time. One file to move has been over 25 minutes. If I click on the files and move them manually, I can click on all files and move them in a matter of seconds.
Is there a better way to move large files with SSIS than with the FileSystemTask - Rename?!
If these are going to so long, how would I move multiple files at the same time?
Going one file at a time with each file taking 25 minutes is not a solution.
I think I am doing something wrong here.
This is SQL Server 2008. I am using BIDS to create my package. I am new to debugging, so it maybe I've just something wrong in the debugging.
Thanks for pointing me in the right direction.
Tony
Things will work out. Get back up, change some parameters and recode.
January 31, 2011 at 1:21 am
Is it an option to create a windows batch file to copy the files?
SSIS is an ETL tool, I think there are better options to just move files from one directory to another.
(and even if I had to use SSIS, I would use a script task with .NET)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 1, 2011 at 6:55 am
Koen,
From my experience, ETL can mean Extract Transform Load, but it can also be Extract Transfer Load.
So, I know SSIS is a ETL tool, but from the many posts I hav seen about FileSystemTask and moving files, I think there are many people using SSIS to move backup files.
But, I appreciate your take on it. I would rather use SSIS than a batch file, just because I need it to be dynamic. In other words, the code needs to:
1) Copy everything from the network backup folder ("d:\backup_sharesetworkbackup\serverA\BAK_Files"), back to the regular server backup folder ("d:\backup_shares\serverA\BAK_Files")
2) Grab everything that has the previous day's date string in the name (i.e. 20110131) to the network backup folder. The "yesterday date string" obviously changes every day. The naming convention I use is to put the datestring in the name of the backup file (i.e. databaseA_20110131_0530.bak)
How would you do it with a script task with .net? I used DTS a lot, but I am only just starting to create projects with SSIS.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
February 1, 2011 at 7:13 am
What you are looking for is a T-tool, not an ETL-tool 😀
I mentioned batch files, because they can just as easily move files, but of course they lack flexibility.
Here's the code, in VB.NET, to move files from one folder to another.
Public Sub Main()
' Create variables to hold the SSIS variable values.
' Change the name of the SSIS variables to the names of the variables you use in your package.
Dim FileName As String = Dts.Variables("Template_FileName").Value.ToString
Dim FileType As String = Dts.Variables("Template_FileType").Value.ToString
Dim Directory As String = Dts.Variables("Template_Directory").Value.ToString
Dim Archive As String = Dts.Variables("Template_Archive").Value.ToString
' Start of the try block, since all the following statements require I/O operations.
Try
' Create a directory info associated with the upload directory. This directory info will contain information about the files it contains.
Dim di As New IO.DirectoryInfo(Directory)
' Create an array with all the files found in the upload directory that matches the search pattern.
' Due to the wildcards embedded in the strings, slight variations of the same filename can be picked up (e.g. suffixes with the date)
Dim aryFi As IO.FileInfo() = di.GetFiles(FileName + FileType)
'Create a variable to serve as an iterator through the for loop.
Dim fi As IO.FileInfo
' Check if there are any matching files at all.
If aryFi.Length() > 0 Then
' Move all the files in the array to the right directory.
For Each fi In aryFi
'Create the destination file path.
Dim newfs As String = Path.Combine(Archive, fi.Name())
fi.CopyTo(newfs, True) 'Copy the file to the new destination.
fi.Delete() 'Delete the original file.
Next
'If you want the package to fail if no files are found, use the following section.
'The script and the package succeed.
'Dts.TaskResult = ScriptResults.Success
Else
'If you want the package to fail if no files are found, use the following section.
'Fail the script and the package.
'Dts.TaskResult = ScriptResults.Failure
End If
'The script and the package succeed. If you want the package to fail if no files are found, comment this section out.
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
'An error has occured. Log the error and fail the task.
Dts.Log(ex.Message.ToString, 999, Nothing)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
There are 4 input variables:
Directory --> where the files are located
Archive --> where the files should be written to
FileName --> Part of the filename that never changes eg: MyFile
Filetype --> file extension preceded with wildcard eg: *.xls
Those last two variables give the following expression: MyFile*.xls
So for example, the file with filename MyFile_20110201.xls will be picked up by the script.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 1, 2011 at 10:19 am
Thanks for the response.
I will see how to translate that into SSIS coding.
The example that I found on the net that I used initially for this project was here:
http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html
This method works well. However, when it hit the big files, things really slowed down to an unaccetable rate.
Tony
Things will work out. Get back up, change some parameters and recode.
February 1, 2011 at 11:13 am
You should look into using ROBOCOPY for this. It is made for mirroring or moving data from one folder to another. You can probably do all you want with a single command. I use it for things like this all the time.
It is available from Microsoft, or you may already have it if you are running Windows Server 2008.
Robocopy
February 1, 2011 at 6:10 pm
I came here to suggest Robocopy as well. We've used it before when moving large mdf files from what drive to another. I use a Execute Process Task with 7-Zip to unzip files dynamically by adding my variables in an expression for the Arguments. You can do the same with Robocopy.
Good luck!
Jeff.
February 1, 2011 at 8:26 pm
Our system admins won't let us use robocopy.
Have anyone else noticed a difference in performance when using the FileSystemTask component on large files?
Tony
Things will work out. Get back up, change some parameters and recode.
February 2, 2011 at 8:03 am
Well I did face a similar problem with moving 12 Gb backup over a 2 mbps network line,
I used 7za.exe as a free tool for zipping the files at source, and than transferring them over to the desired destination.
the zipping process reduces the file size to 1.2 Gb and the copy job is taken care by a xcopy command this gives me the backup files available at the desired destination every day.
you can try the following steps:
1. Run daily SQL Server backup.
2. for recent file names read backup details from msdb database tables, and use 7za.exe (free utility) for zipping the files. for recent file names read backup details from msdb database tables.
3. copy the files using xcopy command.
Cheers, If you need the script feel free to ask.
Khalid Chogle
"Every Good Deed Is Charity"
February 2, 2011 at 8:41 am
WebTechie38 (2/1/2011)
Our system admins won't let us use robocopy.Have anyone else noticed a difference in performance when using the FileSystemTask component on large files?
Tony
Do they have a reason for not letting you use ROBOCOPY?
February 2, 2011 at 9:11 am
Well on 2 mbps line robocopy fails to pull in a large file of size 12+ Gb, so I have implemented this method
February 2, 2011 at 9:56 am
Have you considered doing the backup using SQL server compression (since you are running 2008?? This should significantly reduce the size of the file. There are also some points in the HELP to limit the effect on the high CPU usage generally associated with compressing of files.
The next step might be in SSIS, to do some sort of "shell out" and do the copy (something like RoboCopy would be ideal here or standard copy tools) building the execute string up in SQL is great because you can build the filename up as want it...and 6GB files are not that big, however 25 minutes to copy/move is a long time.
Is your source and destination on the same server or 2 separate servers?
Are you running a virus scanner that is interfering with the copy? Normally quite easy to see in the processes tab of task manager (will more than likely be something pushing the CPU, not SQL).
February 2, 2011 at 2:35 pm
khalidchogle (2/2/2011)
Well on 2 mbps line robocopy fails to pull in a large file of size 12+ Gb, so I have implemented this method
That sounds like a network problem, and robocopy is very good at overcoming them. It will do automatic retries, and will start from where it left off if you set the correct parameters.
I recently moved a 120GB database backup file over a fairly slow WAN link. The robocopy took almost 2 days to complete, but it worked fine. I have moved large files over a WAN link with robocopy many times.
February 2, 2011 at 4:18 pm
Michael,
A discussion of why our admins won't let us use Robocopy would be a digression from the topic for using SSIS to move files.
Plus please don't get me started on system admins who throttle DBAs with so called network policies. 😉
Suffice it to say, from all the blogs on how to use SSIS to move files, they all use small text files in their example. I know there are others that must have big backup files that need to be moved. The question is, can this be done with SSIS?
I played with it some last night:
1) I used the FileSystemTask - Rename
2) I used a script task and wrote some code to use FILE.move
3) The source and destination folder are on the same server.
4) Many of our applications have not been certified in SQL Server 2008. So in many cases, my sql server is still running SQL 2000. Some of them are running SQL 2005. All the newer sql servers are 2008. Unfortunately, the big 7 GB files are from a SQL 2000 server.
In both the script and filesystem tasks, the process works great until it has to move one of the bigger .bak files. It then literally is just sitting there. I'm not sure why.
Tony
Things will work out. Get back up, change some parameters and recode.
February 2, 2011 at 9:35 pm
May I ask why you are using RENAME instead of MOVE?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply