dbTalk Databases Forums  

how to return the result of execute sql programmatically

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.dts how to return the result of execute sql programmatically

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss how to return the result of execute sql programmatically in the microsoft.public.sqlserver.dts forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Ray5531
 
Posts: n/a

Default how to return the result of execute sql programmatically - 04-27-2005 , 06:56 PM





I'm calling an Execute sql task from my script programmatically and I want
it to return its result which is a count into a global variable?? How should
I specify it?

Thanks


' Get Package Object
Set oPkg2 = DTSGlobalVariables.Parent
' Get Exec SQL CustomTask
Set oExecSQL = oPkg2.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomT ask
' Build new SQL Statement
sSQLStatement ="SELECT COUNT(ACTIVE_STATUS) AS countActive FROM
IF_CONFIG_SETTINGS WHERE (IFCODE =" & _
iIFCode & ")"


' Assign SQL Statement Exec SQL Task
oExecSQL.SQLStatement = sSQLStatement
' Clean Up
Set oExecSQL = Nothing
Set oPkg2 = Nothing

'EXIT from function if there is another instance of the same package
is running
IF DTSGlobalVariables("gCountActivePackage").Value > 0 THEN EXIT
FUNCTION



Reply With Quote
  #2  
Old   
Darren Green
 
Posts: n/a

Default Re: how to return the result of execute sql programmatically - 04-28-2005 , 01:50 AM





In message <OGHR$S4SFHA.3392 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Ray5531
<RayAll (AT) microsft (DOT) com> writes
Quote:
I'm calling an Execute sql task from my script programmatically and I want
it to return its result which is a count into a global variable?? How should
I specify it?

Thanks


' Get Package Object
Set oPkg2 = DTSGlobalVariables.Parent
' Get Exec SQL CustomTask
Set oExecSQL = oPkg2.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomT ask
' Build new SQL Statement
sSQLStatement ="SELECT COUNT(ACTIVE_STATUS) AS countActive FROM
IF_CONFIG_SETTINGS WHERE (IFCODE =" & _
iIFCode & ")"


' Assign SQL Statement Exec SQL Task
oExecSQL.SQLStatement = sSQLStatement
' Clean Up
Set oExecSQL = Nothing
Set oPkg2 = Nothing

'EXIT from function if there is another instance of the same package
is running
IF DTSGlobalVariables("gCountActivePackage").Value > 0 THEN EXIT
FUNCTION
You cannot use an Execute SQL Task from within a script task. To execute
a statement directly in script, use ADO.

You can use the script to set the SQLStatement of the task, but let
normal workflow execute it. Use the results parameters to get the count
into a global variable.



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org



Reply With Quote
  #3  
Old   
Ray5531
 
Posts: n/a

Default Re: how to return the result of execute sql programmatically - 04-28-2005 , 11:34 AM


The problem of using ado is that which user name and password shall I use?
I'd like to schadule the package in another server which my password of
integrated security might not work.

Any ideas?

Thanks
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Quote:
In message <OGHR$S4SFHA.3392 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Ray5531
RayAll (AT) microsft (DOT) com> writes
I'm calling an Execute sql task from my script programmatically and I want
it to return its result which is a count into a global variable?? How
should
I specify it?

Thanks


' Get Package Object
Set oPkg2 = DTSGlobalVariables.Parent
' Get Exec SQL CustomTask
Set oExecSQL = oPkg2.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomT ask
' Build new SQL Statement
sSQLStatement ="SELECT COUNT(ACTIVE_STATUS) AS countActive FROM
IF_CONFIG_SETTINGS WHERE (IFCODE =" & _
iIFCode & ")"


' Assign SQL Statement Exec SQL Task
oExecSQL.SQLStatement = sSQLStatement
' Clean Up
Set oExecSQL = Nothing
Set oPkg2 = Nothing

'EXIT from function if there is another instance of the same package
is running
IF DTSGlobalVariables("gCountActivePackage").Value > 0 THEN EXIT
FUNCTION



You cannot use an Execute SQL Task from within a script task. To execute a
statement directly in script, use ADO.

You can use the script to set the SQLStatement of the task, but let normal
workflow execute it. Use the results parameters to get the count into a
global variable.



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org



Reply With Quote
  #4  
Old   
Paul Smith
 
Posts: n/a

Default Re: how to return the result of execute sql programmatically - 04-29-2005 , 01:23 AM


If you use UDL's you can open it and parse the UID/PWD and use that.

"Ray5531" <RayAll (AT) microsft (DOT) com> wrote

Quote:
The problem of using ado is that which user name and password shall I use?
I'd like to schadule the package in another server which my password of
integrated security might not work.

Any ideas?

Thanks
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in
message news:I+gHlCSqeIcCFwRQ (AT) sqldts (DOT) com...
In message <OGHR$S4SFHA.3392 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Ray5531
RayAll (AT) microsft (DOT) com> writes
I'm calling an Execute sql task from my script programmatically and I
want
it to return its result which is a count into a global variable?? How
should
I specify it?

Thanks


' Get Package Object
Set oPkg2 = DTSGlobalVariables.Parent
' Get Exec SQL CustomTask
Set oExecSQL =
oPkg2.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomT ask
' Build new SQL Statement
sSQLStatement ="SELECT COUNT(ACTIVE_STATUS) AS countActive FROM
IF_CONFIG_SETTINGS WHERE (IFCODE =" & _
iIFCode & ")"


' Assign SQL Statement Exec SQL Task
oExecSQL.SQLStatement = sSQLStatement
' Clean Up
Set oExecSQL = Nothing
Set oPkg2 = Nothing

'EXIT from function if there is another instance of the same
package
is running
IF DTSGlobalVariables("gCountActivePackage").Value > 0 THEN EXIT
FUNCTION



You cannot use an Execute SQL Task from within a script task. To execute
a statement directly in script, use ADO.

You can use the script to set the SQLStatement of the task, but let
normal workflow execute it. Use the results parameters to get the count
into a global variable.



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org



Reply With Quote
  #5  
Old   
Ray5531
 
Posts: n/a

Default Re: how to return the result of execute sql programmatically - 04-29-2005 , 11:33 AM


what's UDL?
"Paul Smith" <paul (AT) spamno_sagestore (DOT) com> wrote

Quote:
If you use UDL's you can open it and parse the UID/PWD and use that.

"Ray5531" <RayAll (AT) microsft (DOT) com> wrote in message
news:eW8syABTFHA.3544 (AT) TK2MSFTNGP10 (DOT) phx.gbl...
The problem of using ado is that which user name and password shall I
use? I'd like to schadule the package in another server which my password
of integrated security might not work.

Any ideas?

Thanks
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in
message news:I+gHlCSqeIcCFwRQ (AT) sqldts (DOT) com...
In message <OGHR$S4SFHA.3392 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Ray5531
RayAll (AT) microsft (DOT) com> writes
I'm calling an Execute sql task from my script programmatically and I
want
it to return its result which is a count into a global variable?? How
should
I specify it?

Thanks


' Get Package Object
Set oPkg2 = DTSGlobalVariables.Parent
' Get Exec SQL CustomTask
Set oExecSQL =
oPkg2.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomT ask
' Build new SQL Statement
sSQLStatement ="SELECT COUNT(ACTIVE_STATUS) AS countActive FROM
IF_CONFIG_SETTINGS WHERE (IFCODE =" & _
iIFCode & ")"


' Assign SQL Statement Exec SQL Task
oExecSQL.SQLStatement = sSQLStatement
' Clean Up
Set oExecSQL = Nothing
Set oPkg2 = Nothing

'EXIT from function if there is another instance of the same
package
is running
IF DTSGlobalVariables("gCountActivePackage").Value > 0 THEN EXIT
FUNCTION



You cannot use an Execute SQL Task from within a script task. To execute
a statement directly in script, use ADO.

You can use the script to set the SQLStatement of the task, but let
normal workflow execute it. Use the results parameters to get the count
into a global variable.



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org



Reply With Quote
  #6  
Old   
Darren Green
 
Posts: n/a

Default Re: how to return the result of execute sql programmatically - 04-29-2005 , 01:27 PM


(universal) Data Link. See your right-click context menu, New File, or
create new text file and rename to whatever.udl.

UDLs are just text files, so insecure for passwords.

You can get the result through an Execute SQL Task, so no ned for ADO if
you don't like it. You can loop in DTS packages using workflow as well.



In message <O3UyYkNTFHA.2392 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Ray5531
<RayAll (AT) microsft (DOT) com> writes
Quote:
what's UDL?
"Paul Smith" <paul (AT) spamno_sagestore (DOT) com> wrote in message
news:%23yQQtPITFHA.548 (AT) tk2msftngp13 (DOT) phx.gbl...
If you use UDL's you can open it and parse the UID/PWD and use that.

"Ray5531" <RayAll (AT) microsft (DOT) com> wrote in message
news:eW8syABTFHA.3544 (AT) TK2MSFTNGP10 (DOT) phx.gbl...
The problem of using ado is that which user name and password shall I
use? I'd like to schadule the package in another server which my password
of integrated security might not work.

Any ideas?

Thanks
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in
message news:I+gHlCSqeIcCFwRQ (AT) sqldts (DOT) com...
In message <OGHR$S4SFHA.3392 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Ray5531
RayAll (AT) microsft (DOT) com> writes
I'm calling an Execute sql task from my script programmatically and I
want
it to return its result which is a count into a global variable?? How
should
I specify it?

Thanks


' Get Package Object
Set oPkg2 = DTSGlobalVariables.Parent
' Get Exec SQL CustomTask
Set oExecSQL =
oPkg2.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomT ask
' Build new SQL Statement
sSQLStatement ="SELECT COUNT(ACTIVE_STATUS) AS countActive FROM
IF_CONFIG_SETTINGS WHERE (IFCODE =" & _
iIFCode & ")"


' Assign SQL Statement Exec SQL Task
oExecSQL.SQLStatement = sSQLStatement
' Clean Up
Set oExecSQL = Nothing
Set oPkg2 = Nothing

'EXIT from function if there is another instance of the same
package
is running
IF DTSGlobalVariables("gCountActivePackage").Value > 0 THEN EXIT
FUNCTION



You cannot use an Execute SQL Task from within a script task. To execute
a statement directly in script, use ADO.

You can use the script to set the SQLStatement of the task, but let
normal workflow execute it. Use the results parameters to get the count
into a global variable.



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org



Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top