copy from multiple files simultaneously
-
Hello,
I want to copy multiple files residing on mulitple servers simultaneously (in parallel). On any given day I am not sure how many servers will have the file. How can I accomplish this task using DTS?
Thanks,
Amit Ganatra
-
do u know how many server are there and path of the file then
yes then u can use xp_cmd shell to copy file from all the server.
hope it helps u
from
killer
-
Thanks for your reply.
Today, I have total 35 servers and the number keeps on increasing each month. If I can establish connectivity to these servers then I can copy the file from them, else I have to moove on to the next server for that day. Hence, total number of servers and how many of them are available is always a variable.
Can you please elaobrate on the solution using xp_xmd_shell. May be I can modify the solution to suit my need.
Thank you,
Amit
-
First, setup a table listing your paths and file names (starting with the server name)
EG: FilePathTable -> columns = FilePath
FilePath
FileServer1\Sharename1\FileName1
FileServer2\Sharename2\SubDir2\FileName2
/*********************************/
DECLARE @command varchar(200)
DECLARE Path_Cursor CURSOR FOR
-- Remove '\CommonSubDir\CommonFileName.txt' after "FilePath" if there is no common directory or file path
-- Set the "TargetServer" to the server that this will be copied to (note you can use c:\ instead of \\ for local directory)
SELECT @CmdString = 'copy \\' + FilePath + '\CommonSubDir\CommonFileName.txt' + '\\TargetServer\TargetShare\'
FROM FilePathTAble
OPEN Path_Cursor
FETCH NEXT FROM Path_Cursor INTO @CmdString
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('master.dbo.xp_cmdshell ' + @CmdString)
FETCH NEXT FROM Description_Cursor INTO @CmdString
END
CLOSE Path_Cursor
DEALLOCATE Path_Cursor
/********************************/
You could also add some extra columns to FilePathTable such as a "Group" column which could Identify separate groups of files that you may wish to download. You could also separate the servername and sub directories for greater control in this or other programs that may use this information EG:
Columns = SrcServerName, SrcSubDir1, SrcSubDir2, SrcSubDir3, SrcFileName, TgtServerName, TgtSubDir1, TgtSubDir2, TgtSubdir3, TgtFileName, Group
Good luck and hope this helps
Kindest Regards,Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com -
NOTE MISTAKE
!!!: I specified the variable as @command rather than @CmdStringPS:
I'm not that great an expert, so it's quite likely that the above is not quite right, or that there is a far better way of doing this which I'm sure a fellow, friendly SQL guru 
will fill us in on.
Kindest Regards,Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com -
Alright, I fixed the thing up and shoved it into a stored procedure. Quite possibly this sort of feature exists somewhere and I've reinvented the wheel, but hell, it was fun anyway.
Run the following two queries to create a table called "FilePath" under the MSDB database and the query usp_Cmd_Copy_Files under the MSDB database
/*******************************/
USE MSDB
CREATE TABLE FilePath
(
SrcServerName varchar(100),
SrcSubDir1 varchar(100),
SrcSubDir2 varchar(100),
SrcSubDir3 varchar(100),
SrcFileName varchar(100),
TgtServerName varchar(100),
TgtSubDir1 varchar(100),
TgtSubDir2 varchar(100),
TgtSubdir3 varchar(100),
TgtFileName varchar(100),
Groups varchar(200)
)
/*******************************/
USE MSDB
CREATE PROCEDURE usp_Cmd_Copy_Files
@Groups varchar(200),
@Source varchar(200),
@Target varchar(200),
@CmdSwitches varchar(20)
AS
DECLARE @CmdString varchar(200)
IF @Source <> NULL and @Target <> NULL
Return 1
DECLARE Path_Cursor CURSOR FOR
SELECT 'copy '+@CmdSwitches+' '+
ISNULL(@Target,SrcServerName+'\'+SrcSubDir1+'\'+SrcSubDir2+'\'+SrcSubDir3+'\'+SrcFileName)+' '+
ISNULL(@Target,TgtServerName+'\'+TgtSubDir1+'\'+TgtSubDir2+'\'+TgtSubDir3+'\'+TgtFileName)
FROM MSDB.dbo.FilePath
Where Groups like '%'+ISNULL(@Groups,Groups)+'%'
OPEN Path_Cursor
FETCH NEXT FROM Path_Cursor INTO @CmdString
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('master.dbo.xp_cmdshell ' + @CmdString)
FETCH NEXT FROM Description_Cursor INTO @CmdString
END
CLOSE Path_Cursor
DEALLOCATE Path_Cursor
Return 0
GO
/****************************************/
To use the stored procedure just run it straight or with a specific group, Target OR Source and maybe some extra dos copy switches. EG:
--Will run the script agianst all lines in the FilePath table with a "Groups" field that contains "Complete" as a group
exec usp_Cmd_Copy_Files 'Complete'
--Will run the script against all lines, regardless of Group copying File_21.txt located at \\Server_21\Share_21\ to all targets listed in the FilePath table
exec usp_Cmd_Copy_Files NULL, '\\Server_21\Share_21\File_21.txt'
--Will run the script against all lines with a "Groups" field containing "Source" and send all source files to "\\Server_88\Share_88\' using the /Y switch
exec usp_Cmd_Copy_Files 'Source', NULL, '\\Server_88\Share_88\', /Y
Some groups I can immediately think of are: "Complete" for records that have source and target, Source for records without a target, Target for records without a Source.
Hope this is useful (and correct), Good luck.
Kindest Regards,Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com -
This thread has been moved from the QOD forum. Please pay attention and post questions in the appropriate forum.
-
Hello Charles,
Thanks for your reply.
I tried the approach you recomended (using cursor) and it is sequential. By sequential I mean, File 2 from Server 2 starts downloading only after File 1 from Server 1 has downloaded. My need is to start download of all files in parallel (independent of each other).
Amit Ganatra
-
*thinks* next best guess is the following. I have no Idea if this would actually work.
DECLARE @FullCmdString AS VARCHAR(4000)
DECLARE Path_Cursor CURSOR FOR
SELECT 'EXEC master.dbo.xp_cmdshell copy '+@CmdSwitches+' '+
ISNULL(@Target,SrcServerName+'\'+SrcSubDir1+'\'+SrcSubDir2+'\'+SrcSubDir3+'\'+SrcFileName)+' '+
ISNULL(@Target,TgtServerName+'\'+TgtSubDir1+'\'+TgtSubDir2+'\'+TgtSubDir3+'\'+TgtFileName)+
Char(13) + 'GO' + Char(13)
FROM MSDB.dbo.FilePath
Where Groups like '%'+ISNULL(@Groups,Groups)+'%'
OPEN Path_Cursor
FETCH NEXT FROM Path_Cursor INTO @CmdString
WHILE @@FETCH_STATUS = 0
BEGIN
@FullCmdString = @FullCmdString + @CmdString
FETCH NEXT FROM Description_Cursor INTO @CmdString
END
CLOSE Path_Cursor
DEALLOCATE Path_Cursor
EXEC (@FullCmdString)
Good Luck (but don't hold your breath on this one).
Kindest Regards,Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com -
FOUND IT!!!
Alright, forget the stuff above, I'm pretty sure it'll just run one after the other as well.
Go to this article:
http://www.sqlservercentral.com/columnists/lPeysakhovich/dtsparallelprocessing.asp
The guy initially gives a query for reindexing multiple databases, etc. But further down he explains how he runs the script parallel against multiple databases. You use a DTS task, create a SQL script to execute each copy command we've been talking about ('EXEC master.dbo.xp_cmdshell [copy ...]") and change the DTS property ‘Limit the number of tasks running in parallel’ and it should work. He recommends not doing this for more than 6 processors but this copy command is much simpler than his indexing, so hopefully you can get away with a lot more (although 35 might be pushing your luck).
If you name each SQL script in the task appropriately, then hopefully it should be quite easy to maintain.
Good Luck
Kindest Regards,Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com -
I would say, copy / generate the files from SQL Server to a file Share; and then have a .NET Code or some sort of code that reads the file share and copies wherever required
That way the LOAD on SQL Server is less
My 2 cents.
paul
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply