0

I pass a parameter into the Execute SQL Task tool in Visual Studio 2008 and i need to execute the follow:

DECLARE CurrentFileName  VARCHAR(200) :=  (?);  
DECLARE TableToTruncate VARCHAR(200); 
TableToTruncate   := CurrentFileName+'_TABLE_VAR';  
BEGIN 
TRUNCATE TABLE TableToTruncate;  
END;

Anyone have any idea why i keep getting 

error    ^ found "DECLARE" (at char 1) expecting a keyword".
BYC
  • 33
  • 4

1 Answers1

0

Best suggestion here is to create a stored procedure and call that on your SQL Task like below

CREATE PROCEDURE sp_truncate_table(@filename VARCHAR(200))
AS
BEGIN
DECLARE TableToTruncate VARCHAR(200); 
TableToTruncate   := @filename+'_TABLE_VAR';  

TRUNCATE TABLE TableToTruncate;  
END;

Then in Execute SQL Task configuration editor, in SQLStatement property say exec sp_truncate_table ?

See this Article for more information on the same.

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Thanks Rahul however i am still getting error ^ found "DECLARE" (at char 7) expecting a keyword" – BYC Oct 28 '14 at 21:25
  • That's not a proper way of plumbing query in SSIS sql task. See edited answer. – Rahul Oct 28 '14 at 21:38