Passing date parameter to Execute Process Task (batch file)

Question

Passing date parameter to Execute Process Task (batch file)

Brian Jones

SSC Veteran

Points: 209
More actions
August 3, 2006 at 12:53 am

#76560

I have an existing DTS application into which I need to add some functionality to copy latest images which are held on a remote server.
In DTS I have set-up a SQL query to retrieve the last date that the copy was successful for a particular server. I am trying to pass this date value as a parameter to a Windows batch file which would copy files that are later than this date.I have established a global variable in the SQL query.
I have used the Execute Process Task and pointed it at my batch file but I cant seem to get the parameter value to pass correctly. I have referenced the global variable in the Parameter field of the Execute Process Task. However the global variable name (LAST_RUN_DATE) is being passed not the value. The batch file works fine if I enter an actual date rather than the global variable name. Can I use a global variable name here? If so how? Do I need a new approach!
Any help much appreciated. I am only an occasional user of DTS.
Brian

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Don't use an 'Execute Process Task' in this instance. If you are going to be supplying some parameters then use a sql task instead.

exec master.dbo.xp_cmdshell 'YOURBATCHFILENAMEANDPATH' @YOURDATEVARIABLE

That's all there is to it.

------------------------------
The Users are always right - when I'm not wrong!

Don Staten SSC Eights! Points: 957 More actions · Answer 2

Unfortunately, you cannot pass a global variable to an Execute Process task (in the Parameters field in EM). It is possible to update an Execute Process task's parameters field, but you must use an additional ActiveX Script task to update the Execute Process task.

Like Jonathan said, your best bet is using an Execute SQL task as noted above.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

The problem with using xp_cmdshell is that it executes everything locally on the SQL Server. I don't know about you, but I don't allow people to install programs or put batch files directly on the SQL Server. If they need to do that, they can put it on a non-SQL Server server and execute the DTS package remotely via the DTSRun utility (Windows built-in task scheduler works great for scheduling these as jobs).

Now that said, the simplest way to do it (outside of xp_cmdshell) would be to simply re-write the batch file each time you run it. Use an Active-X script to write the file. I do this with ftp scripts where I need to download files that have variable names form day to day. It's very quick and very easy.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Bill Nye Hall of Fame Points: 3248 More actions · Answer 4

Yes, you can easily pass a global variable value into the Execute Process Task - simply use a Dynamic Properties Task. This can be used to set a wide variety of properties at runtime using a variety of ways to set the variable such as a query, ini file, or global variable.

I created a small test package just to confirm this since I hadn't done this recently. I will attempt to send it to you using the private message function of this forum. It is only a 36K structured storage file - to open it, right click on Data transformation Services in EM and then Open Package. If you run it, it will open a cmd window based on the dynamic properties task, even though it is hard-coded to run Calc.exe.

Enjoy.

Bill.

Roma S SSC Rookie Points: 37 More actions · Answer 5

Hi Bill,

Do you still have this package? I need to do almost the same thing.

Thank you,

Roman.

P.S Disregard please, I already figure it out.

Phil Larkin-403636 Old Hand Points: 336 More actions · Answer 6

Bill,

(sent email also)

I was searching for solutions to a problem I have. I need to pass a variable from a table from SQL server to a bat file. I see the XP_CMDShell option but I was not sure how to loop through all the rows in the table. I came across your response on SQL SERVER CENTRAL.

My situation is to take "X" ids and run a batch file that will run for each X id. The X id's would pass from a table in SQL server to the windows based Bat file and the bat file would be kicked off by the DTS package. I liked the idea of using global parameters in the DTS package but would need a little help. All this will take place on the same SQL server.

Thanks for your help.

Bill Nye Hall of Fame Points: 3248 More actions · Answer 7

Phil,

In this case, you may not even need to use global variables, but I've included in this sample some code that would allow you to do so depending on your needs.

To test this sample, first create a table of IDs:

if

exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IDs_to_Process]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[IDs_to_Process]

GO

CREATE TABLE IDs_to_Process (ID int IDENTITY(1,1), Name varchar(20))

INSERT INTO IDs_to_Process Values('Susan')

INSERT INTO IDs_to_Process Values('Bob')

INSERT INTO IDs_to_Process Values('George')

Then create a batch file named ID_Run.bat with the following code:

Echo %1: %2 >c:\%2.txt

exit

Then copy the following into an ActiveX DTS Task. You will just need to revise the database and server to where you created the table (assumes trusted connection):

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim oConn

Dim rs

Dim sSQL

Dim oPkg

Dim sRunfile

Dim wshShell

' ********* select ID's that need to be checked

sSQL="SELECT ID, Name FROM IDs_to_Process WHERE NAME<>'Bob' "

IF DTSGLOBALVARIABLES("gvSQL").VALUE>" " then

sSQL=DTSGLOBALVARIABLES("gvSQL").VALUE ' Allows reuse of this package by passing in sSQL

End IF

set WshShell = CreateObject("WScript.Shell")

set oConn=createobject("adodb.connection")

oconn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDB;Data Source=MyServer"

set rs=createobject("adodb.recordset")

rs.open sSQL, oConn

Do While Not rs.EOF ' ************ START rs LOOP

'************ In case you want to send to another package to run: This example allows ssn to be passed to pkg before running it

'SET oPkg = CreateObject("DTS.Package")

' get pkg

'oPkg.LoadFromSQLServer "MyServer", "", "", "256", , , ,"my package"

' pass in ssn

'oPkg.GLOBALVARIABLES.item("gvSSN").value=trim(rs.Fields("ssn"))

' run pkg

'oPkg.Execute

'oPkg.Uninitialize()

'Set oPkg=Nothing

'************ Run batchfile

sRunfile = "c:\ID_run.bat " & rs.Fields("ID") & " " & rs.Fields("Name")

WshShell.Run sRunfile

rs.MoveNext

Loop ' ************** END rs LOOP

set rs=nothing

oConn.close

set oConn = nothing

Main = DTSTaskExecResult_Success

End Function

Sorry, the formatting gets a little messed up, so you'll need to fix the lines that wrapped. Now just run the package to put George.txt and Susan.txt to your c drive (adjust as needed). Note that this runs asynchronously - it doesn't wait for the first batch file to finish before it runs for the next ID. If you need it to wait, you could either build in a delay, or turn the batch file into an executable.

Bill

Bill Scott SSCrazy Points: 2338 More actions · Answer 8

If you batch file is always going to be running your batch file for "Today" you can use the environment variable %Date% to plug in todays date on the appropriate batch command parameters. Its formatted according to your machines short date/time setting.