Set variables within sql task to global variables

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Set variables within sql task to global variables

Author

Topic

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-08-05 : 08:50:08

Is it possible to assign a vairable within a execute sql task equal to a pakcage global variable? I have tried the following but it obviously does not work. Any suggestions?

Declare @Flag as int
Set @Flag = ? --Package Global Variable

if @Flag = 1
send an email code that I use
else
nothing

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-05 : 10:01:29

You can access the global variables from an activex task.
So why not handle all of this in activex?

Duane.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-05 : 10:02:44

Are you doing anything else in the SQL task? If not, why not use an ActiveX script task instead...

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-05 : 10:03:58

Sorry for the echo Duane! Your reply hadn't been posted when I started writing mine!

Mark

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-08-05 : 10:58:39

Here is what I am trying to accomplish. I have an ActiveX script task that checks to see if a file exists on a sql server. If it does, I place a value of 1 in a Package Global Variable and delete the file, if it does not exist I place a value of 0 in the Package Global Variable. I then want to use a Execte SQL task to update an oracle table with the value of the Package Global Variable. The last step I am wanting to do is send an email using xp_smtp_sendmail (found xp_smtp_sendmail code from another site refrenced in sqlteam fourms) if the Package Global Variable is = 1. If the value of the Package Global Variable is 1 or 0 I still want the package to report success (unless something fails like connecting to the Oracle database). Any of that make sense?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-05 : 11:05:49

Why not use xp_sendmail from a sqlstatement that you use after connecting to the db through the activex task?

Or am I misunderstanding the problem?

Duane.

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-08-05 : 11:35:22

ActiveX Script: Step 1

Function Main()
Dim objfso, strPath
set objfso =createobject("scripting.filesystemobject")
strPath = "c:\SkittleDependencyFile\DailySkittleCache.txt"
If objFSO.FileExists(strPath) then
objFSO.DeleteFile(strPath)
DTSGlobalVariables("gvFlag").value = 1
Else
DTSGlobalVariables("gvFlag").value = 0
End if
Main = DTSTaskExecResult_Success
set objfso = Nothing
End Function

Execute SQL Task: Step 2 connect to oracle database

update testing
set Flag = ?

Step 3:
************************************
this is where I am stuck!
I need to know that step 2 succesfully completed before I execute step 3
Send email if gvFlag = 1
if gvFlag = 0 do not send email but report success.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-05 : 11:59:15

You want to send the mail from the sql server right?

Why not connect to it from the activex task and send the mail?

[CODE]
Set ConnProd = CreateObject("ADODB.Connection")
Set ConnStg = CreateObject("ADODB.Connection")

ConnProd.ConnectionTimeout = 1000
ConnProd.CommandTimeout = 3600
ConnProd.Provider = "SQLOLEDB"
ConnProd.Open "Server="&DTSGlobalVariables("ProductionServerName").Value&";Trusted_connection=Yes;Database="&DTSGlobalVariables("ProductionDatabaseName").Value

Function Main()

'Check value of global variable here:
'** if ok to send mail do this
'*****************************************************************
SQL = "exec xp_sendmail 'blah', 'blah'"
ConnProd.Execute(SQL)

If Err.Number <> 0 Then
DTSGlobalVariables("ErrorMessage").Value = "Error Description: " & Err.Description & Chr(10) & Chr(13) & "SQL Text: " & SQL
Err.Clear
Main = DTSTaskExecResult_Failure
Exit Function
End If

..........

Main = DTSTaskExecResult_Success
'************************************************************

End Function

[/CODE]

That should do it shouldn't it?

Duane.

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-08-05 : 12:59:41

Duane,

That is exactly what I was looking for!
Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources