August 3, 2006 at 12:53 am
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
August 3, 2006 at 2:33 am
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.
August 4, 2006 at 5:56 am
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.
August 4, 2006 at 10:09 am
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.
August 8, 2006 at 2:32 pm
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.
January 8, 2007 at 7:43 am
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.
August 7, 2007 at 9:51 am
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.
August 7, 2007 at 1:16 pm
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
August 7, 2007 at 1:45 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply