Execute SQL Task - Passing Variables

how can you pass variables from one 'Execute SQL Task' to another?

Not sure I'm getting your whole scenario, but I'll take a stab at it.

Define a variable in SSIS at the package level. It will be accessible from all Execute SQL tasks.

If you are asking how to retrieve a value in one Execute SQL Task and use it in another, you still need to do the step above. In the first Execute SQL, set the Resultset type to Single Row on the General page of the task, and on the Result Set page, specify 0 as the Result Name (assuming you want the first column of your SQL statement), and specify the variable you created under Variable name.

In the second Execute SQL, you can map the variable to a parameter in the SQL statement by using the Parameter Mapping page.

Let me know if that answered your question.

|||

Using SQL Server 2000:

In a DTS Pkg...

Execute SQL Task:

I'm doing an update query.

I would like to define a couple variables, say, @.error and @.Pkg

Then run an insert query, catch an error (if there is one)

...

if @.@.Error then

set @.Error = @.@.Error

...

Then, have a second Execute SQL Task that runs on failure

Exec xp_sendmail

...

@.message = 'there was an error' & @.Error & ' in pkg' & @.Pkg

or something to that effect.

I can do all this in an activex script, but was wondering if there is a simpler way (e.g. through the execute sql task)

|||Wish I could help you, but it's been a while since I have done any DTS. This is an SSIS forum - you might have better results posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

If you were doing this in SSIS, you would do this through precedence constraints, an user variable to hold the error code, and the system:: PackageName variable.

|||

I tried your scenario (in SSIS) and receive the error message in the second Execute SQL Task:

Executing the query "DELETE FROM [Order] WHERE (order_date > @.StartDate)" failed with the following error: "Must declare the scalar variable "@.StartDate".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

@.StartDate is a Date derived from the variable User :: StartDate which is a DateTime

Could you give a step by step example of how to do this.

|||

If you are using an OLEDB connection, use ? instead of @.StartDate in the query.

Here's more info on using the Execute SQL Task - http://www.sqlis.com/58.aspx

|||

That worked...how do I access mulitple variables?

|||

Use multiple ?. Like:

Code Snippet

INSERT INTO table VALUES(?, ?, ?)

Then map each parameter on the Parameter Mapping page. Use 0 for the first param, 1 for the second, etc.