Scheduling job to Unzip files fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Scheduling job to Unzip files fails

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-22 : 15:45:52

All,

I spent way to much time on this and yet I'm still stuck on it.

I have a VB.net code that unzips 6 files.I have included this code in the Script Task component of SSIS and schedule it as a job.I have included it as part of SSIS as besides unzipping there are other evaluations as well.When i run this package in both Debug and Non Debug Mode in Visual Studio.It runs like a champ.When I schedule the package as a sql job.As soon as the 1st file is unzipped,the program just waits for a long long time,no further unzipping takes place.I'l have to stop the job or it would just stay there.No error code returned.Files to be unzipped sit in the local drive of machine.Im using Winzip licensed evaluation with winzip command line add on.If I remove the unzipping part from the rest of the SSIS pacakge.No problem in executing.

After much frustration,I decided to test the Unzipping part as a sql job.This time I wrote it as a VB Script Program and scheduled it as a Cmd Exe job.Same thing happens again.This time not even the first file gets unzipped.When I execute the bat file outside[not as a sql job],it works completely fine.

Guys, I need your help here.I have tried many different methods but this just does not seem to work.Help & suggestion's are very musch appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-22 : 16:08:46

My first guess is it is a permissions issue. Does the sql server agent have permission to get to the files you are trying to unzip?

Permissions get tricky when using cmdexec.

If the owner of the job is not part of the sysadmin group and the job is scheduled
OR
If the job is launched by someone NOT in the sysadmin group

Then the job is executed under the xp_cmdshell_proxy_account. So that would be the account that needs access to resources that contain your files. (see BOL: sp_xp_cmdshell_proxy_account)

Be One with the Optimizer
TG

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-22 : 16:25:59

Im checking the permission right now.Shall update soon.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-22 : 16:42:11

I made some changes to the permission level by adding the account that is being used to run the SQL Agent service to the Administrator group.Now the VB Script runs fine as a job.

The VB.Net is still stays after unzipping the first file.

Both jobs are excuted with the same account.

Any Idea ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-22 : 16:55:12

Ok, so the permission thing fixed your test script. Re-reading your original post I see that the SSIS job always successfully unzipped the first file but then "hung". So now it doesn't seem like a permission thing because one file was unzipped. Sounds like a problem in your SSIS Task. I'm definately not an SSIS guy - can you post the details of the task? Perhaps someone else can spot something...

Be One with the Optimizer
TG

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-22 : 17:06:26

I guess you need to have FOREACHLOOP Container with EAch file Enumerator for it to loop through all files with that extension.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-22 : 17:12:28

My VB.net code has a for each loop,It loops over all .zip files in the folder and sends the filename to a unzip function.This function goes ahead and unzips it with no problem when executed not as a sql job.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-23 : 12:01:32

All,

I have opted to use VB SCript to unzip my files.I have created a bat file for the VB Script and scheduled the bat file as a CMD exe job.It works fine to unzip.My question is within my code,if there is any problem while trying to unzip.How can I set & return error code that will notify the job,that error code is no longer 0.Therefore it should fail.Ive never done this vb script.Advice is appreciated.Thank You

Here is my VB Scripts.

Dim fso
Dim conn
Dim RS
Dim Destination1
Dim Description
Dim sFiles
Dim sFile
Dim sFolder
Dim SQL

set conn= CreateObject("ADODB.Connection")
set RS = CreateObject("ADODB.Recordset")
set fso= CreateObject("Scripting.FileSystemObject")

'NOTE: Change the connection string according to your environment.
Conn.Open = "Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=TEST;user id ='1';password='1' "

SQL = "SELECT Destination FROM dbo.tbl'"
'Msgbox SQL
RS.Open SQL, Conn,1,1

Destination1 = RS("Destination")
'Msgbox Destination1

Set RS = Nothing
Set conn=Nothing
Dim ssubFolder
Set ssubFolder = FSO.GetFolder(Destination1)

Set ssubFiles = ssubFolder.Files
For Each ssubFile In ssubFiles
Dim strFile1
strFile1 = ssubFile.Name
'Msgbox strFile1

'Check file modified date & file size
if CStr(FormatDateTime(ssubfile.DateLastModified,vbshortdate))= CStr(Date) and ssubfile.Size>22 Then

Dim results
Results= WinZip(Destination1, Destination1 & "\" & strFile1)

Else
SendMail(strFile1)
EmptyFolder(Destination1)

[red]-------------------------------------
** SET & RETURN ERROR CODE TO SQL JOB,ERROR LEVEL REQUIRED HERE
-------------------------------------[/red]
End If

Next
WScript.Sleep 1000

'-------------------------------------------------------------------------------------------------------------------

Function WinZip(ssubFile,sArchiveName)
'This function executes the command line
'version of WinZip and reports whether
'the archive exists after WinZip exits.
'If it exists then it returns true. If
'not it returns an error message.

Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set oShell = WScript.CreateObject("Wscript.Shell")

'--------Find Working Directory--------
aScriptFilename = Split(Wscript.ScriptFullName, "\")
sScriptFilename = aScriptFileName(Ubound(aScriptFilename))
sWorkingDirectory = Replace(Wscript.ScriptFullName, sScriptFilename, "")
'MsgBox Cstr(sScriptfilename) & ";" & CStr(sWorkingDirectory)
'--------------------------------------

'-------Ensure we can find WZUNZIP.exe------
If oFSO.FileExists(sWorkingDirectory & " " & "WZUNZIP.EXE") Then
sWinZipLocation = ""
ElseIf oFSO.FileExists("C:\program files\WinZip\WZUNZIP.EXE") Then
sWinZipLocation = "C:\program files\WinZip\wzunzip.exe"
Else
Zip = "Error: Couldn't find WZUNZIP.EXE"

-------------------------------------
** SET & RETURN ERROR CODE TO SQL JOB,ERROR LEVEL REQUIRED HERE
-------------------------------------

Exit Function
End If
'--------------------------------------

'Dim strpath

'strpath= """" & sWinZipLocation & """ -d """ & sArchiveName & """ """ & ssubFile & """"

oShell.Run """" & sWinZipLocation & """ -d """ & sArchiveName & """ """ & ssubFile & """"

End Function

'-------------------------------------------------------------------------------------------------------------------

Function SendMail(strFile1)

Dim MailObj
Dim boolSent
Dim erMssg
erMssg ="Invalid file size or not of current date"
Set MailObj = CreateObject("CDO.Message")
MailObj.From = "File@.com"

MailObj.To = "lolo@lolo.com"
MailObj.Subject = "File Error"
MailObj.TextBody = strFile1 & " " & "-" & erMssg

MailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

MailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "100.14.618.2"

MailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

MailObj.Configuration.Fields.Update

MailObj.Send

Set MailObj = Nothing

---------------------------------------------------------------------------------------
** SET & RETURN ERROR CODE TO SQL JOB,ERROR LEVEL REQUIRED HERE IF MAIL FAILED TO BE SENT
---------------------------------------------------------------------------------------

End Function

'-------------------------------------------------------------------------------------------------------------------

Function EmptyFolder(destination)

Set ssubFolder = FSO.GetFolder(destination)
Set ssubFiles = ssubFolder.Files

For Each ssubFile In ssubFiles
'if fso.getextensionName(ssubFile.Name)=extension Then
ssubFile.Delete
'End If
Next

End Function

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2008-09-24 : 08:31:46

It is possible that the command is waiting for input.

Try adding the -o option to the WZUNZIP command line:
-o[-] Overwrite existing files without a prompt (automatically reply "Yes" to each overwrite prompt).

Do you get any error when you break out of the batch?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-24 : 11:58:18

A likely cause is that Internet Explorer Enhanced Security Configuration is installed on the server. It will cause it to pop-up a message box when you execute a command within VB script. Since you are not running with a GUI, it just stops and waits for a response that it can never get.

You can uninstall it with Control Panel Add/Remove Programs, Add/Remove Windows Components, and checking off Internet Explorer Enhanced Security Configuration.

CODO ERGO SUM

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-25 : 14:31:31

All,

The problem was with the line that passes the arguments to unzip files.I have fixed it and now it works fine.Thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources