Solved

DTS -- Passing Global Variable into Execute Process Task, Parameters field

Posted on 2004-03-24
10
2,894 Views
Last Modified: 2008-09-17
Experts:

I have an ActiveX Script task that dynamically creates a global variable (DiffFileName), which is a path\filename string (example string=\\172.168.1.86\K$\myfile20040324.bak)

Next, I have an Execute Process Task which runs Winzip.

Winzip requires several params, one of which is a source path\filename.

How do I get DTS to pass the global variable into the Execute Process Task, Parameters field? (See Details below.)

THANKS!
--SW

DETAIL:

Win2000/SQL Server 2000

Actual Execute Process Task Values (using example source filename above):

Win32 Process: C:\Program Files\WinZip\WINZIP32.EXE
   Parameters: -min -a -en "c:\OGL_cta_backup.zip" "DiffFileName"
  Return Code: 0
      Timeout: 0

Active X Task Script:
Function Main()
CurrentDate =now()
sFilename = "\\172.168.1.86\K$\myfile" &  Right(Year(CurrentDate),4)
If Month(CurrentDate) < 10 Then sFilename = sFilename & "0" & _
      Month(CurrentDate) Else sFilename = sFilename & Month(CurrentDate)
If Day(CurrentDate) < 10 Then sFilename = sFilename & _
      "0" & Day(CurrentDate) Else sFilename = sFilename & Day(CurrentDate)
sFilename=sFilename&".bak"
DTSGlobalVariables("DiffFileName").Value = sFilename
Main = DTSTaskExecResult_Success
End Function
0
Comment
Question by:sw7104
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10670448
You will have to create a parsible SQL Statement first (so you can fake sql server into thinking it's a good SQL Statement).  This will allow you to select your parms.  After you select the parms you want to use, then you can change your SQL Statement back to the master..xp_cmdshell ?

See this link:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20909916.html
0
 
LVL 14

Assisted Solution

by:adwiseman
adwiseman earned 250 total points
ID: 10670453
One would think that the parameter field would be settable using the Dynamic properies task, but it is not.  The Execute Process task builds the execute script using the parameters field.

You should be able to simple use an ActiveX script task to set the ProcesCommandLine



Function Main()

    Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask
        task.ProcessCommandLine = "\\server\share\winzip ""c:\temp\file.zip"" " 

    Main = DTSTaskExecResult_Success

End Function    
0
 
LVL 2

Expert Comment

by:dhenson
ID: 10670469
Rather than use the Execute Process Task... Would Shelling to the winzip exe from your Active X Task accomplish your goal?

Shell "C:\Program Files\WinZip\WINZIP32.EXE -min -a -en c:\OGL_cta_backup.zip " & sFilename

dhenson
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:sw7104
ID: 10670873
dhenson:
Ran following script and got error:

Error Source: Microsoft VBScript runtime error
Error Descrtiption: Type mismatch: 'Shell'

--------------------------------------
Function Main()

sFilename = "\\172.168.1.86\K$\myfile20040324.bak"

Shell "C:\Program Files\WinZip\WINZIP32.EXE -min -a -en c:\OGL_cta_backup.zip " & sFilename

Main = DTSTaskExecResult_Success

End Function
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10670913
Shell is not available in DTS.
0
 
LVL 2

Expert Comment

by:dhenson
ID: 10670985
ahh... oops.

my bad
0
 
LVL 2

Accepted Solution

by:
dhenson earned 250 total points
ID: 10671019
You can perform all the same date manipulation in a SQL Task and use xp_cmdshell to launch winzip (instead of using an active x task)

Heres one I did that is similar, you can use the ideas to do your own filename manipulation.

declare @zipfilename varchar(255)
declare @filepath varchar(255)
declare @localpath varchar(255)
declare @localfilename varchar(255)
declare @command varchar(255)

set @localpath = '\\atlas\besslog$'

--Determine day to import and set @localfilename
      if DATENAME(dw,getdate())='Monday'
            begin
            set @localfilename = @localpath + '\Filter_log_' + convert(varchar(8),getdate() -3,112) + '.txt'
            set @zipfilename = 'Filter_log_' + convert(varchar(8),getdate() -3,112) + '.txt.gz'
            end
      else
            begin
            set @localfilename = @localpath + '\Filter_log_' + convert(varchar(8),getdate() -1,112) + '.txt'
            set @zipfilename = 'Filter_log_' + convert(varchar(8),getdate() -1,112) + '.txt.gz'
            end
      set @filepath = '\\isatest\Logs$\' + @zipfilename

--Extract Yesterday's Logs to the c:\besslog folder
      set @command = 'c:\progra~1\winzip\winzip32 -min -e ' + @filepath + ' ' + @localpath
      --print @command
      exec master..xp_cmdshell @command

--rename the local file for import step
      set @command = 'move /y ' + @localfilename + ' ' + @localpath + '\besslog.txt'
      --print @command
      exec master..xp_cmdshell @command

0
 
LVL 2

Expert Comment

by:dhenson
ID: 10671427
If your up to using the Execute sql task instead... try this:

declare @zipfilename varchar(255)
declare @sFilename varchar(255)
declare @command varchar(255)

set @sFilename = '\\172.168.1.86\K$\myfile' + convert(varchar(50),getdate(),112) + '.bak'
set @zipfilename = 'c:\OGL_cta_backup.zip'

set @command = 'c:\progra~1\winzip\winzip32 -min -e ' + @zipfilename + ' ' + @sFilename
exec master..xp_cmdshell @command

If my syntax is correct, the above should unzip 'c:\OGL_cta_backup.zip' to a file named:
'\\172.168.1.86\K$\myfile20040324.bak'
0
 
LVL 1

Author Comment

by:sw7104
ID: 10672079
This has been so frustrating. Note that I've switched to local machine and files. Also, I've tried to strip this to the bare essentials.

This command succeeds at command line, in under 1 second total run-time (file is 409 KB uncompressed):

c:\Progra~1\WinZip\WINZIP32.EXE -min -a -en "c:\OGL_cta_backup.zip" "C:\Documents and Settings\SWatkins\My Documents\1Projects\OglebayNorton\test20040324.bak"

Yet, when I run the following in Query Analyzer, the process just grinds away and hangs and hangs. I'm at 8 minutes plus, and still "processing".

Everything is on the local machine/drive, a 1 gig mem XP Professional box.

Thanks for all your help. I'm about to put my fist through my machine.

--------------------------------------------------------------------------------
declare @localfilename varchar(255)
declare @zipfilename   varchar(255)
declare @command       varchar(255)
set nocount on
set @localfilename = '"C:\Documents and Settings\SWatkins\My Documents\1Projects\OglebayNorton\test20040324.bak"'
set @zipfilename = '"c:\OGL_cta_backup.zip"'
--C:\Progra~1\WinZip\WINZIP32.EXE -min -a -en "c:\OGL_cta_backup.zip" "C:\Documents and Settings\SWatkins\My Documents\1Projects\OglebayNorton\test20040324.bak"
set @command = 'C:\Progra~1\WinZip\WINZIP32.EXE -min -a -en ' + @zipfilename + ' ' + @localfilename
--SELECT @command
exec master..xp_cmdshell @command
0
 
LVL 1

Author Comment

by:sw7104
ID: 10672559
Ultimately, I had to abandon the xp_cmdshell approach. I went back to adwiseman's approach, which I did not initially understand, but after testing and reviewing the Dynamic Properties Task, I figured it out.

Thanks to dhenson for the time and trouble -- I'm guessing the xp_cmdshell approach works, but I couldn't get it to.

My near-final solution (without the "date in filename" manipulation part) on my local dev machine:
-----------------------------
Function Main()

    Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask
        task.ProcessCommandLine = """C:\Program Files\WinZip\WINZIP32.EXE"" -min -a -en ""c:\OGL_cta_backup.zip"" ""C:\Documents and Settings\SWatkins\My Documents\1Projects\OglebayNorton\test20040324.bak"" " 

    Main = DTSTaskExecResult_Success

End Function  
-----------------------------
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Multiple functions in SQL select statement 4 27
t-sql need help on t-sql 10 25
TSQL convert date to string 4 34
SQL, add where clause 5 23
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question