Unzip files from Stored Procedure using 7zip

Question

Post reply

Unzip files from Stored Procedure using 7zip

Jeffery Williams

SSCertifiable

Points: 6441
More actions
July 29, 2010 at 10:20 am

#224766

Here is sample code that will unzip files from a given directory, into a destination directory using 7Zip
I also provide the command line sent through command shell so that you can see the net result, and see what the variables in the Stored Procedure look like.
DECLARE @ZipFileName varchar(100)
DECLARE @SQLCommand varchar(max)
DECLARE @DataZipPass AS varchar(10)
,@FilePath AS varchar(8000)
,@ArchivePath AS varchar(8000)
,@CMD AS varchar(8000)
,@FileNameMask AS varchar(20)
,@Count AS int
-- ,@CustomerID int SET @CustomerID = 2
DECLARE @FilesToUnZip TABLE
(
FileName varchar(20))
-- SET The environment
SELECT @DataZipPass = DataZipPass, @FilePath = FilePath, @ArchivePath = ArchivePath, @FileNameMask = FileNameMask
FROM Customers
WHERE Customers.CustomerID = @CustomerID
PRINT '@DataZipPass: ' + CAST(@DataZipPass AS varchar(1000))
PRINT '@FilePath: ' + CAST(@FilePath AS varchar(1000))
PRINT '@ArchivePath: ' + CAST(@ArchivePath AS varchar(1000))
SELECT @cmd='dir /B '+@FilePath+ ' ' +@FileNameMask
--DELETE #Dir
INSERT INTO @FilesToUnZip
EXEC master..xp_cmdshell @cmd
DELETE FROM @FilesToUnZip
WHERE FileName IS NULL
INSERT INTO FilesToUnzip
(FileToUnZip, CustomerID)
SELECT LTRIM(RTRIM(FileName)), @CustomerID
FROM @FilesToUnZip
SELECT * FROM @FilesToUnZip
SELECT @Count=count (*) FROM FilesToUnZip WHERE FilesToUnZip.Processed = 0
WHILE @Count > 0
BEGIN
PRINT 'Files to Process: ' + CAST(@Count AS varchar)
PRINT ''
PRINT '@ZipFileName: ' + CAST(coalesce(@ZipFileName, 'Not Yet Set') AS varchar)
BEGIN TRANSACTION
SELECT TOP 1 @ZipFileName= LTRIM(RTRIM(FileToUnZip))
FROM FilesToUnZip
WHERE FilesToUnZip.Processed = 0
COMMIT TRANSACTION
PRINT '@ZipFileName: ' + CAST(coalesce(@ZipFileName, 'Not Yet Set') AS varchar)
SET @SQLCommand = 'exec master..xp_cmdshell ' + '''' + 'C:\7zip\7zip\7z.exe e ' + @FilePath + @ZipFileName +
' -p' + @DataZipPass + ' -o' + @ArchivePath + ' -y ' + ''''
PRINT 'Extraction Command sent: ' + CAST(@SQLCommand AS varchar(max))
PRINT ''
EXEC (@SQLCommand)
SET @ZipFileName = 'N'
PRINT '@ZipFileName: ' + cast(@ZipFileName as varchar)
SET @Count = (@Count - 1)
DELETE FROM @FilesToUnZip --WHERE FileName = @ZipFileName
UPDATE FilesToUnzip
SET Processed= 1
WHERE LEFT(FileToUnzip, 11) = LEFT(@ZipFileName, 11)
DELETE TOP (1) FROM FilesToUnzip
CONTINUE
END
The resulting command line sent is:
C:\7zip\7zip\7z.exe e d:\FFMun\FirstMerrit\SourceFiles\81055410.zip -pP800071 -od:\FFMun\ASampleCompany\UnZip\ -y
This is something I was stuck on for a while, solution was pretty easy. Hope this helps someone else in the community.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 1

You should submit this as an article, Jeff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

Jeff,

Thank you for the suggestion. You know I think I will do that. I need to clean up the code in that case. This project was a bit of a pain in the butt so the code is representative of try this, try that. ;)... I will clean it up and write some background fitting for an article. I ran across some things that will not work, depending upon your environment (are you on a domain, just a home machine, etc); and give some background there as well.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 3

Agreed... especially on the environment thing. We had the joy of doing cross-domain across servers through a fire wall on SOX compliant systems on one side and "open" systems on the other all of which was done "long haul" in 3 different geographic States. It was "fun". You're article would help lots of folks in similar situations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

caspersql SSCrazy Points: 2276 More actions · Answer 4

Many thanks for this post Jeffery - helped me with a project I was working on today.