Error in variable mapping in Execute SQL Task

Hi,

I am getting an error message (mentioned below) in the variable mapping of Execute SQL Task in SSIS.

" Error: ForEach Variable Mapping number 9 to variable "User::Value" cannot be applied. "

" Error: The type of the value being assigned to variable "User::Value" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. "

Pls anyone have a look and give me a solution asap.

Thanks & Regards,

Prakash Srinivasan.

What was the type of the variable "User::Value" and what the was the value that you were trying to assign to this variable?|||

Hi,

Thanks for your response. I was trying to pass the float value and declared the variable as double only. But yesterday I deleted all the variable mappings and did the same mapping again, the problem got resolved.

I don't know what was the problem, even I re-assigned the same index values (starts with zero) for all the variables in the Foreach ADO Enumerator.

Anyway the problem got solved.

Thanks & Regards,

Prakash Srinivasan.

|||

Hi,

I'm seeing the same problem with one package I have put together. I have an "int" variable from a select statement. That variable is mapped to an int32 variable in the package. When I try and foreach through the data set (from previoeus Execute SQL task), it bombs and I get the same error. Error: ForEach Variable Mapping number 1 to variable "User::variablename" cannot be applied. Then in the PostExecute I get the following error: Error: The type of the value being assigned to variable "User::variablename" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Seems to me there must be a bug here that only gets triggered when you create the mapping with the incorrect data type, fix it, and then try again. For some reason, even though you have fixed it, it believes the data types are still the old ones. MS, if you are interested in a package that displays this behavior, then email me.

dcb99

|||

All,

Ok. Figured this out for my case. The problem is the foreach will not allow a NULL to be assigned to an int32 variable. Seems like SSIS should have nullable types built into it. Anyway, when I replace the column in my select statement with an ISNULL(columnname, 0) then it works fine. Too bad the error want something like: "Data is out of range for this variable type. Please adjust data or use aa different variable type."

dcb99

Error in variable mapping in Execute SQL Task

Hi,

I am getting an error message (mentioned below) in the variable mapping of Execute SQL Task in SSIS.

" Error: ForEach Variable Mapping number 9 to variable "User::Value" cannot be applied. "

" Error: The type of the value being assigned to variable "User::Value" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. "

Pls anyone have a look and give me a solution asap.

Thanks & Regards,

Prakash Srinivasan.

What was the type of the variable "User::Value" and what the was the value that you were trying to assign to this variable?|||

Hi,

Thanks for your response. I was trying to pass the float value and declared the variable as double only. But yesterday I deleted all the variable mappings and did the same mapping again, the problem got resolved.

I don't know what was the problem, even I re-assigned the same index values (starts with zero) for all the variables in the Foreach ADO Enumerator.

Anyway the problem got solved.

Thanks & Regards,

Prakash Srinivasan.

|||

Hi,

I'm seeing the same problem with one package I have put together. I have an "int" variable from a select statement. That variable is mapped to an int32 variable in the package. When I try and foreach through the data set (from previoeus Execute SQL task), it bombs and I get the same error. Error: ForEach Variable Mapping number 1 to variable "User::variablename" cannot be applied. Then in the PostExecute I get the following error: Error: The type of the value being assigned to variable "User::variablename" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Seems to me there must be a bug here that only gets triggered when you create the mapping with the incorrect data type, fix it, and then try again. For some reason, even though you have fixed it, it believes the data types are still the old ones. MS, if you are interested in a package that displays this behavior, then email me.

dcb99

|||

All,

Ok. Figured this out for my case. The problem is the foreach will not allow a NULL to be assigned to an int32 variable. Seems like SSIS should have nullable types built into it. Anyway, when I replace the column in my select statement with an ISNULL(columnname, 0) then it works fine. Too bad the error want something like: "Data is out of range for this variable type. Please adjust data or use aa different variable type."

dcb99

Error In Merge Replication while server syncs

At first we received error like

"The merge process could not retrieve identity range resource for table..."

then after solving this error we again started syncing of servers manually then we got some other error like

"the process could not enumerate changes to the subscriber".

We tried our best to solve this problem but some error appears each time like

"The process could not deliver the snapshot to the Subscriber."

"The schema script 'sync -t"Design" -o"dbo" -d"\\ECOSYSTEM\D$\FTP\MSSQL\EcoSystem\ftp\ECOSYSTEM_MLEcosystem_MLEcosystem\20060619204802\Design_1337.bcp" -hORDER( [intDesignId] ASC)' could not be propagated to the subscriber"

again identity erroe emerges and agin we do the same process.

How to solve these problem?

These look like transitory network issues. Can you set QueryTimeout to something larger and see if this solves the problem.

-- Hilary Cotter

Error handling for Script Task

I'm importing an Excel file and massaging it with a custom script task. I get an error "Index was outside the bounds of the array." That error is helpful but I needed to know which of the 70,000+ lines the error occurred on. What is the best way of devbugging this issue? Try-Catch-Finally with messageboxes in the Catch clause?

Barkingdog

Assuming this is Script Task, not Script Dataflow Component, you can set a breakpoint at the beggining of the task, then use menu Debug > Exceptions, check the 'Thrown' box - this will make IDE stop when an exception is thrown by the Script Task.

P.S. 70,000+ lines seems to be way too much for script task. You may find a custom task to be a better option for such undertaking.