Execute SQL Task: truncate tables w/parameter
Posted on 2007-10-02
I'm trying to truncate all tables that match the results of a query. I populate a recordset with the list of table names and pass it to a Foreach loop. This works fine; if I run a messagebox Script Task inside the loop it gives me the names.
What I want to do inside the loop is run an Execute SQL Task to truncate the table. I've done this successfully by typing the SQL command explicitly (ie "TRUNCATE TABLE Customer"), but something about the way I'm handling the parameter is failing. I've tried "TRUNCATE TABLE ?", "EXEC('TRUNCATE TABLE ' + ?)", and even scripting my variable to be a full TRUNCATE statement and then setting my SQL task to "EXEC(?)".
Each time, I get "...failed with the following error: 'Synatax error, permission violation, or other nonspecific error'. Possible failure reasons...". This seems like I'm doing something small and simple wrong, but I can't find it.
ResultSet = None
ConnectionType = OLE DB
SQLSourceType = Direct input
VariableName = User::strTableName (which is in scope and works when I use a Script task to test)
Direction = Input
Data Type = VARCHAR
Parameter Name = 0