param for MySQL data source in SSIS 2005

Hi experts,

I 'm trying to get countNumber from a select statement in MySQL source. Something likes this:

select @.countNumber = count(*) from tableName where condition_1 = xxx

I use ExecuteSQLTask to get the result set with result name @.countNumber along with ADO.NET connection type.
I 've installed mysql-connector-net-5.0.5 cause the source is MySQL 5x.

And I got a task failed error which is described below:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "new_process": "Result binding by name "@.new_process" is not supported for this connection type. ".

Please help me to fix this error, thanks alot

Best reguards,

Try this and see if it helps,

change the query like, Select count(*) from tableName where condition_1 = xxx

Create a variable of the type object. Make sure in the Execute SQL Task you have selected Result Set as Single Row.

In the ResultSet, give the ResultName as 0 and map it to the variable you have created.

|||

Rick wrote:

Try this and see if it helps,

change the query like, Select count(*) from tableName where condition_1 = xxx

Create a variable of the type object. Make sure in the Execute SQL Task you have selected Result Set as Single Row.

In the ResultSet, give the ResultName as 0 and map it to the variable you have created.

No, do not make the data type of the variable "object." Make it an integer.

|||

Phil Brammer wrote:

No, do not make the data type of the variable "object." Make it an integer.

Sorry to ask Phil, but can you let me know what is the issue in using the datatype object ?

|||

Rick wrote:

Phil Brammer wrote:

No, do not make the data type of the variable "object." Make it an integer.

Sorry to ask Phil, but can you let me know what is the issue in using the datatype object ?

Sure, the overhead is not needed. Plus, I don't think you can use an object variable in a precedence constraint, for instance (Unless SSIS can implicitly cast it). Object variables are designed to hold more than one row of data and/or multiple columns.|||Thanks Phil |||Hi experts,

At my first try, the task fails with integer data type.
I'm sorry Phil . But you are right when saying that object type is used to store more than one record with many columns.

I try it again with the object data type. It's cool but I have to run ScriptTask to get to cast type of the object var into int32.

Here is the way it runs:
Dim vars As Variables
Dim result As Int32
Dts.VariableDispenser.LockForRead("objVar")
Dts.VariableDispenser.GetVariables(vars)
Try
result = CInt(vars("objVar").Value)
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Dts.Variables("intCount").Value = result

If you have other better ways to solve my problem, it will be appreciated

thanks and best reguards,

khanhmy|||

Have you tried

Define variable 'CountNumber' of type Int32

Query

Select count(*) As CountNumber from TABLENAME where condition_1 = xxxx

In the resultset window map CountNumber to User::CountNumber.

Hope this helps!

|||To back up Jay L's post, this is what I do. Now, I guess it depends on what data type is returned from the COUNT(*) against a MySQL database. Since it's like Oracle, it's probably a DAMN numeric(nn,0) which SSIS has issues with.

You could try:

select cast(count(*) as REAL) from TABLENAME where condition_1 = xxxx

Try stuffing that into a variable of type, double.