Parameter Mapping using an Execute SQL Task

问题:

I am trying to create a temporary table and insert data into the temp table within an Execute SQL Task inside a foreach loop container.

Here are the sql tasks

IF OBJECT_ID('TEMPDB.DBO.#TEMP') IS NOT NULL
DROP TABLE #TEMP
GO

CREATE TABLE #TEMP 
      ( ... );

INSERT INTO #TEMP
      SELECT (...)
  FROM table t1 INNER JOIN table2 t2
  ON t1.id = t2.table1_id
WHERE t1.value = ?

I want to have a parameter in the WHERE clause but whenever I try to add it I get this error.

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

When I hard code the value in the Where Clause the insert works perfectly.

What am I doing wrong on my parameter mapping?

Here are my parameter mapping settings

Also I am not able to use a stored procedure.

回答1:

Just go to Expressions Tab as shown in the screenshot you provided and write the following expression to SqlStatmentSource property

"IF OBJECT_ID('TEMPDB.DBO.#TEMP') IS NOT NULL
DROP TABLE #TEMP
GO

CREATE TABLE #TEMP 
      ( ... );

INSERT INTO #TEMP
      SELECT (...)
  FROM table t1 INNER JOIN table2 t2
  ON t1.id = t2.table1_id
WHERE t1.value = '"  +  @[User::Where_Variable]  +   "'"

回答2:

Populate a variable with your entire SQL script, and use the variable as your SQLSourceType in your Execute SQL Task.