Storing values in variables using Execute SQL Task Object

Question

I have an execute sql task defined in my package which will execute a stored procedure. When the stored procedure is executed it will return user credential. I have to store the credential in a variables and then use these credentials in a new script task.

The following is what I have tried so far.

STEP 1 - I have created SQL task

On General TAB set following properties:

Resultset: Single row
connectionType: OLE DB
Connection:SourceDestination
SQLSourceType:DirectInput
SQLStatement: Execute dbo.GetLoginInfo1
ByPassPrepare = False

STEP 2 - Parameter Mapping Tab

Variable Name = User::DatabaseUserName
Direction = Output
Data Type = NVarchar
Parameter Name = @UserId
Parameter Size = 50

STEP 3 - Result Set tab

Result Name = 0
Variable Name = User::DatabaseUserName

When I executed the task I got following errors.

[Execute SQL Task] Error: Executing the query "EXECUTE [dbo].[GetUserLoginInfo1]"
failed with the following error: "Value does not fall within the expected range."
Possible failure reasons: Problems with the query, "ResultSet" property not set
correctly, parameters not set correctly, or connection not established correctly

When I debug I got to see following information: Break ON Pre execute:

User::DatabaseUserName = {Hi}

Break ON Post Execute: (error happens but the values gets chanage)

User::DatabaseUserName = {User1}

Sorry just forgot to mention the creation of variables. I have create one variable as listed below.

Name = DatabaseUserName
Scope = Package1
Datatype = String
Value = Hi

The below mentioned is the stored procedure that I have used.

ALTER PROCEDURE [dbo].[GetUserLoginInfo1]
AS
BEGIN
    SELECT userid AS userid
    FROM login_credentials 
    WHERE servername= 'server1'
END

I have tried and read lot many sites but I am still facing the problem.This is my second day on it and have to resolve it before tomorrow morning so any help will be really appreciated.

luviktor · Accepted Answer · 2012-03-01 08:05:06Z

1

You should completely omit STEP2. (However, your procedure does not have any output column.)

STEP 1, and STEP 3 are all right. (In STEP 3 you could use userid in the Result Name column of the grid, but the ordinal will be absolutely good.)

answered Mar 1, 2012 at 8:05

luviktor

2,2682 gold badges22 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vinod Nair Over a year ago

Thanks a lot viktor. Your help has been greatly appreciated.

Collectives™ on Stack Overflow

Storing values in variables using Execute SQL Task Object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related