Archival of folders from server path location to specified destination.

Question

Archival of folders from server path location to specified destination.

bodhansamanta88

Newbie

Points: 3
More actions
June 28, 2012 at 3:27 am

#260648

Hi Folks !!
I have few questions to ask,
1. Let's assume I have declared outer cursor to fetch @SourcePath, @DestinationPath,@Period from a certain master table. Then I've declared the inner cursor to fetch the folders which are eligible to archive for the respective @SourcePath. Can I fetch the outer cursor's variable to inner cursor while I'm putting the copy command in there like below,
SET @CMD = N'xcopy "T:\' +@SOURCE_PATH_folder+@Folder+ '" "T:\' +@DESTINATION_PATH_folder+@Folder+'"/i'
EXEC @PROCESS_RECORD = master..xp_cmdshell @CMD,no_output
PRINT @PROCESS_RECORD
2. How much time does generally one stored procedure need having nested cursor within it.
Hope some efficient answer at the earliest !!
Thanks,
Bodhan

Viewing 7 posts - 1 through 7 (of 7 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Bodhan

You've posted in an SSIS forum, but it sounds as if you're trying to do everything in T-SQL. If you are indeed using SSIS, it's better to take advantage of the For Each Loops and file manipulation tasks that Integration Services provides.

John

bodhansamanta88 Newbie Points: 3 More actions · Answer 2

Exactly John...I'm going to write one stored procedure to automate the archival procedure.

Can you share some idea.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

You haven't stated your exact requirements, so I can't really provide any recommendations, except to consider using SSIS instead of this.

The answers to your original two questions are:

(1) Yes, you can.

(2) It depends on what the cursors do. Test it with the size of data you'll be using and see whether performance is acceptable.

John

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

Since we're mixing the technologies in with the forum we're in I'll throw one more alternative out there. You could likely do everything you need with a single line of PowerShell, if you're up for something like that. As John suggested, please provide your complete set of requirements.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

bodhansamanta88 Newbie Points: 3 More actions · Answer 5

Thanks John...Actually I don't know all about the SSIS. So, just to get help I made that post.

For your information, I need to archive the data older than a certain period from several server locations to another to best utilize the space within that server location.

So, I'm trying to build one stored procedure by means of which I can fetch the list of folder from those locations one by one. Then I transform those folder name so that I can check which folder is eligible to archive.

Then I'm going to put those selected folder into a nested cursor to copy those folders into the specific destination path one by one and then delete from the source path location.

Please let me know if I'm still not clear.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

That's clear. What specifically do you need help with?

John