I just can't seem to pass a parameter (or get one returned) from an Execute
SQL Task object (using an OLE DB connection).

I thought that it would be as simple as defining a variable at the User
level in the variable window. Then, map that User variable to another
variable in the properties of the Execute SQL object. I've tried every
combination of variable prefixes, names...but without success.

I can't seem to find an example of how to input or output variables into
this object. Ultimately, I would like to change the value of a User variable
from within one of these objects so that I can use them in an expression of a
Precedence Constraint.

Isn't there a "how to do" paper on this subject?


--
Michael Hockstein

Hi Michael,
My understanding of your issue is that:
You wanted to pass a parameter from an Execute SQL Task object by using OLE
DB connection. You have tried many times, but with no lucky.
You wanted to get some articles having samples regarding this topic.
If I have misunderstood, please let me know.

SQL Server 2005 Books Online talks bout this topic, but unfortunately it is
not clear.
I performed a simple demo for your reference:
Prerequisites
================================================
SQL Server 2000: My2KServer
Database: Northwind
Table: PCTEST (new created)
----------------------------------
CREATE TABLE [dbo].[PCTEST] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[pid] [int] NOT NULL ,
[sname] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
-----------------------------------


Steps
================================================
1. Create an OLE DB Connection "OLE DB Connection: My2KServer.Northwind"
2. Drage a "Execute SQL Task" to the "Control Flow" panel from Toolbox;
3. Double click the task, set the properties as following:
[General]
Connection: My2KServer.Northwind
SQLSourceType: Direct Input
SQLStatement: INSERT INTO PCTEST (pid, sname) VALUES (?,?)
BypassPrepare: False
ResultSet: None

[Parameter Mapping]
Create two variables Userid and User::sname.
User:id
---------------------------------
Container: Execute SQL Task
Name: pid
Namespace: User
Value Type: Int32
Value: 10

User::sname
-------------------------------
Container: Execute SQL Task
Name: sname
Namespace: User
Value Type: String
Value: Charles

[Variable Name][Direction][DataType][Parameter Name]
User:id Input LONG 0
User::sname Input VARCHAR 1
4. Save the package and execute the task.

You may refer to this article which talks more than local BOL on this topic:
Execute SQL Task
http://msdn2.microsoft.com/en-us/library/ms141003.aspx

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

OK, one of the tricks is to use the order of the parameter (OLE DB style) as
the [Parameter Name] in mapping.


Please add to your simple code how to RETURN a value and use an OUTPUT
parameter (unless the output parameter is only used of the SQL is a stored
procedure)
--
Michael Hockstein


"Charles Wang[MSFT]" wrote:

Hi Michael,
No problem. I changed the demo as following:
Firstly, I create a stored procedure in Northwind:
create procedure proc_insertPCTEST
(
@pid int,
@sname varchar(20),
@id int output
)
AS
INSERT INTO PCTEST (pid, sname) VALUES (@pid,@sname)
SELECT @id=SCOPE_IDENTITY()

Then in SSIS development environment, double click the "Execute SQL Task",
change the SQLStatement "EXEC proc_insertPCTEST ?,?,? output", switch to
the Parameter Mapping, add a variable:
User::id
==============
Container: Package
Name: id
Namespace: User
Value type: Int32
Value: -1

Direction: Output
Data Type: LONG
Parameter Name: 2
===============

For using this output parameter, I create a second "Execute SQL Task" named
"Execute SQL Task 1" and set the first "Execute SQL Task" as its input.
Double click the "Execute SQL Task 1", set the OLE DB Connection and enter
the SQLStatement:
"UPDATE PCTEST SET sname='TEST' WHERE id=?"

Click Parameter Mapping, add the User::id as its input parameter:
Variable Name: User::id
Direction: Input
Data Type: LONG
Parameter Name: 0

Then save the package and execute it. You will find the new inserted value
'Charles' has been changed to 'TEST'.

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Great, one last one.

Is there a way to return a value (is a returnvalue) without using an OUTPUT
parameter in a stored procedure?


--
Michael Hockstein


"Charles Wang[MSFT]" wrote:

Hi Michael,
I would like to. Per my test, you can get and use the return value of SQL
Server stored procedure like this:
I wrote a procedure:
create procedure proc_getid
as
declare @intval int
set @intval = (select max(id) as maxid from PCTEST )
RETURN @intval

Set the task properties in SSIS development environment:
Execute SQL Task Properties:
========================
[General]
SQL Statement: exec ? = proc_PCTEST
ResultSet: None
[Parameter Mapping]
User::id ReturnValue LONG 0


Execute SQL Task1 Properties:
=======================
[General]
SQL Statement: Update PCTEST Set sname='TEST1' WHERE id=?
ResultSet: None
[Parameter Mapping]
User::id Input LONG 0

Save the package and execute it, and the value has been changed to TEST1.
I appreciate your understanding that SQL Server 2005 is a new release
version and the related sample documents may not be adequate in SQL Server
Books Online. If you are very concerned on this, you can give Microsoft
feedback via the following link:
http://connect.microsoft.com/
Your suggestions will be routed to Microsoft SQL team so that the related
sample documents will be released in future.

Also, if your stored procedure doesn't use a return value, but a query like
this:
create procedure proc_getid1
as
select max(id) as maxid from PCTEST

, and you want to set the query result value to a variable, please refer to
the following:
Execute SQL Task Properties:
========================
[General]
SQL Statement: exec proc_PCTEST1
ResultSet: Single row
[Result set]
maxid User::id


Execute SQL Task1 Properties:
=======================
[General]
SQL Statement: Update PCTEST Set sname='TEST1' WHERE id=?
ResultSet: None
[Parameter Mapping]
User::id Input LONG 0

Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Thanks Charles. I'll experiment with these concepts now. You've been a great
help.


--
Michael Hockstein


"Charles Wang[MSFT]" wrote:

Hi Michael,

Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.

Have a nice day!

Charles Wang
Microsoft Online Community Support

Hi All,

I have a problem about Execute sql task .

[General->sqlstatement]

Insert Into NEDW.dbo.F_BerLin_Project_Tmp
Select InDate,
Item_Key,
VendorNumber,
ControlNumberCostCode,
RMAStatus,
RMAType,
POCost,
RCost,
RCostLoss,
WriteOff,
?
From NEDW.dbo.F_BerLin_Project_Tmp
where convert(varchar(10),ReportMonth+1,120) = ?
[Parameter mapping]
Valible Name =User::CURRENT_DATE
Direction = Input
Datatype = varchar
Parameter Name = 0

------------
Run the Parkage and have Errors.

Could you help me?

*** Sent via Developersdex http://www.developersdex.com ***