CODEWITHSUNDEEP
ssis
Vinod Nair
Vinod Nair

Reputation: 81

Storing values in variables using Execute SQL Task Object

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.

Upvotes: 2

Views: 11793

Answers (1)

luviktor
luviktor

Reputation: 2270

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.)

Upvotes: 1

Related Questions