Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".