June 9, 2011 at 9:02 am
bear with me, started using SSIS just recently, having so much fun :rolleyes:
have a simple package
all i want to do is run a sql script to truncate tables
but i want the
use @productiondb
go
truncate table table1;
truncate table table2;
created a variable, scope is the relevant sequence container
mapped the variable in the sql task editor
got the error
[Execute SQL Task] Error: Executing the query "USE @ProductionDB " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Yes I am aware I can set the database name in the connection string, i am trying to learn how to use variables for more convulated logic.
sonal.
June 9, 2011 at 9:20 am
This was removed by the editor as SPAM
June 10, 2011 at 6:27 am
sonal_anjaria (6/9/2011)
created a variable, scope is the relevant sequence containermapped the variable in the sql task editor
Not sure what you mean by the above. You've created a variable and mapped it in the SQL Task Editor, but what have you mapped it to?
The code listed above it doesn't seem to set up the variable at all. Is there something missing? Are you mapping an input variable or an output variable?
EDIT: Hang about. I think I see the problem. You tried mapping a database name variable in your Parameters page. The problem is, you need to make your code dynamic SQL now. You can't execute a variable the way you're doing.
You have two options. Option 1
DECLARE @MySQL varchar(50);
SET @MySQL = 'USE ' + @ProductionDB + ' Truncate Table Table1; Truncate Table Table2';
EXECUTE sp_executesql @MySQL;
Or just set a variable, properly scoped to your string, to 'USE MyDB; Truncate Table Table1; Truncate Table Table2;' and put the variable in your config file so you can change the db name at will.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply