Accessing the variable in Execute SQL Task

I have a variable SYear with the data type Integer and the value 2005.

Connection type is OLE DB, and my sql statement is:

DECLARE @.MyYear Int

SET @.MyYear = ?

I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @.MyYear, it is fine. What is wrong in here? Somebody help me!

The OLE-DB parameter support is more orientated to queries, so this should work-

SELECT * FROM Table WHERE YearCol = ?

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

EXEC MyProc ?, ?

Why and how I have never really got to the bottom off, but that is what I have found to work.

|||This is VERY annoying. It is a type cast problem between the .Net type and the SQL type. When it can't cast the .NET type to the SQL type you selected, it just fails SILENTLY and gives you NOTHING in your task and NO ERROR.

What EXACTLY is the type of SYear, Int32 or Int16. And what EXACTLY did you set the parameter type too.

See this doc: http://msdn2.microsoft.com/en-us/library/4e5xt97a.aspx

I have had much better luck always setting the parameter type to varchar. At least then it gets passed as something.

Also, BypassPrepare must be True or it will fail with an error, that again does not tell you anything about setting BypassPrepare to True.

|||

DarrenSQLIS wrote:

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

Why not build the SQL statement in a variable expression first and then use that variable?|||

If you are asking me, then yep, it is valid enough, just didn't pop into my head when writing that reply. I prefer parameters myself, but use either. Couple of pros and cons here-

Execute SQL
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExecuteSQL.html)

|||

Thank you all for your replies.

First of all, I would like to explain why I need to use the variable @.Year1. In my SQL codes in Execute SQL Task, I need to use the value of SYear over and over again. If I use ? instead of @.Year, I need to define SYear in the Parameter Mapping as many times as I use '?'. Is that correct? SYear is a data-type 'Int32'. In the parameter mapping, the data-type used is 'LONG' (only choices here are LONG or SHORT for integer value). Any comment is appreciated. Thank you.

|||You are doing it correctly. Having a var of "Int32" and mapping it to a parameter type "LONG" is correct. Is SYear in the correct "scope"?

You must have "BypassPrepare" set to True otherwise you will always get an error using ?
When I do the same thing it works fine for me. I setup a global var called SYear Int32 and set it as a Parameter, Direction=Input, Type=LONG, ParameterName = 0

SQLStatement:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

What is the error message you get?

Also, make sure you have the SP1 installed on your local machine as well as the server.

|||

Thank you, Tom.

I have created the test package and the scope of SYear is the package which contains only this Execute SQL Task.

The one thing I do not understand is that the code 1 works but not code 2. Why? It is not logical.

**Code 1

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = ?

**Code 2

DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

The error message I got is (I set the result set NONE.):

SSIS package "Package1.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear" 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.

Task failed: Execute SQL Task

Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package1.dtsx" finished: Failure.

|||

I have seen this behaviour. It has to do with OLE DB limitations regarding variables. You can't use variables in OLE DB if your script contains multiple SQL statements. Your Code 1 contains one statement, so it works fine. Your Code 2 contains three, which doesn't work. I've got two workarounds for you:

1. use a DataReader source

2. Use multiple consecutive Execute SQL Tasks.

Attention: The DataReader Source syntax for using variables is different.

Configure your task to use the right variable. For instance, if you map package variable SYear to Parameter name @.SYear, then your code should look something like this:

DECLARE @.MyYear Int

SET @.MyYear = @.SYear

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

you don't need to declare @.SYear.

Regards,

Pipo

|||

To be clear, there are two more workarounds further up this thread, stored procedures and property expressions.

It is also advisable to issue SET NOCOUNT ON when working with parameters and result sets.

|||

Thank you for your reply, Pipo. But, Tom used 3 SQL statements with the input variable and he said it worked (please look at the previous reply). I do understand that there are some other ways to get around this problem, but I still would like to find out what is causing this. I have about 500-fields syntax check to clean the data for every year. I would not like to create the separate program for each year. That is why I would like to accept the user input (year) and use it for 500 syntax checks. The program should be flexible, shouldn′t it?

I really appreciate replies from you all.

Thank you.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure. Should I use the Execute SQL Task and set the SQL Source Type = File Connection?

|||What do you mean by code 2 doesn't work?

You need to have result set set to something other than "None" if it returns a record set. That is what is generating the error you are seeing.

My guess from the code you posted is, SQL is implicitly converting DB030 into the correct type on code 1 and code 2 you are not comparing an INT to an INT. What is the data type of DB030?

Do you have SP1 installed on the server and the client? What version does it show. The current version (pre SP2) is 9.0.2153.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure.

Instead of using a SELECT statement or other T-SQL in your Execute SQL Task, you call the stored procedure with placeholders for parameters, so it may look like this-

EXEC myproc ?, ?

? is still the placeholder for the parameter in the same way it is when using a SELECT such as SELECT * FROM table WHERE id = ? AND id2 = ?

|||

yhalldorsson,

To be honest, I overlooked Tom's little remark that it works fine for him.

I've been over this some time ago, and couldn't get it to work. To make sure I didn't miss anything, I tried again just now, using the exact same code showen above:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

I used the same parameter mapping as described by Tom, but I can't get it working. I get the exact same error message as you do.

So, to be honest, I'm getting curious as to what's the (hidden) difference between Tom's attempt and mine (and yours, presumably).

(To be complete: I used Tom's Parameter mapping and code, Single row result set, and put the Result Name Year into another variable of type int32.

And I set BypassPrepare to True)

Regards,

Pipo