Execute SQL Task Error

Hi,

I have a For Loop Container which has Execute SQL Task. The following SQL is not working in it.

Input Parameters: Batch_ID, Class_ID both of type long in the parameter mapping dialog.

The result set is of type 'One Row' and direction is input

Result set is: NextBatchID>User::MinBatch_ID of type int

NextClassID->User::MinClass_ID of type int

The query is giving very generic error

[Execute SQL Task] Error: Executing the query "" 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.

Code Snippet

DECLARE @.ClassID int
DECLARE @.BatchID int
SET @.BatchID = ?
SET @.ClassID = ?
SELECT MAX(T.Batch_ID) AS NextBatch_ID, MAX(T.Class_ID) AS NextClass_ID FROM
(Select TOP (10) BD.Batch_ID, BD.Class_ID,
ROW_NUMBER() OVER(ORDER BY Batch_ID, Class_ID)AS RowNum
From dbo.Batch_Data As BD
WHERE (BD.Batch_ID > @.BatchID) OR (BD.Batch_ID = @.BatchID AND BD.Class > @.ClassID)
ORDER BY Batch_ID, Class_ID) T
WHERE T.RowNum = 10

When I hardcode values the query works. With parameters it fails.

Any help/thought?

-Leo

I don't know if parameters are supported outside of the WHERE clause. I recommend you use an expression-based variable to build your query, then just have the Execute SQL Task retrieve the query from the variable.
|||

Hi,

We cannot use parameters other than WHERE cluase. Where can I find this and any other restrictions about the Parameters in BOL?

Thanks,

-Leo

|||

There are not ducumentes restrictions about that, * I think*.

Jay's suggestion is still valid; just use an expression to build the sql statement of the execute sql task. The expression will concatenate all the required variables at run time.

|||

The topic on the Execute SQL Task contains a wealth of information on the use of parameters:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

The rules that govern the use of parameters in SSIS are not SSIS rules, but come from the provider that is being used (and, of course, the database's dialect of SQL). So depending on the connection manager that you have chosen, you must observe the rules of SqlClient or ODBC or ADO or OLE DB for parameter usage.

-Doug

|||Hi, were you able to resolve the above issue? If yes, could you please educate me as to how? Thanks|||If you want to use paramters in a SQL statement outside of the WHERE clause, build it in an expression, as JayH suggested.