February 23, 2012 at 3:44 am
Hy i am trying to use SSIS recordset in a for each loop with some variables.
i am using a recordset containing result of a simple select name,id,date from mytable.
i have set a variable ( object type ) , on the editor to populate this object variable.
then , i am connecting the flow task to a for each loop.
and i am mapping 3 variabless : name (string), id ( integer) , date ( datetime), and set the collection parameter with my object variable, populated just before.
i am setting index for each variable match the order of my sql commande.
index 0 = name
index 1 = id
index 3 = date
when i am executing my package, i got this error :
in french :
Le type de valeur assignée à la variable «*Utilisateur::id» est différent du type de variable actuel. Les variables ne peuvent pas changer de type en cours d'exécution. Les types des variables sont stricts, à l'exception des variables de type Object.
Erreur*: 0xC001C012 à Conteneur de boucles Foreach: Impossible d'appliquer le numéro de mappage de la variable ForEach 2 sur la variable «*Utilisateur::id».
Erreur*: 0xC001F009 à Package: Le type de valeur assignée à la variable «*Utilisateur::nom*» est différent du type de variable actuel. Les variables ne peuvent pas changer de type en cours d'exécution. Les types des variables sont stricts, à l'exception des variables de type Object.
Erreur*: 0xC001C012 à Conteneur de boucles Foreach: Impossible d'appliquer le numéro de mappage de la variable ForEach 3 sur la variable «*Utilisateur::nom*».
meaning :
the type assigne to the id variable User::id differe from the actual type.variable can't change during the execution.
Erreur*: 0xC001C012 : to container for each loop: impossible to apply the mapping number foreach 2 to the variable User::id.
same for the variable user:name.
i don't understand why i have a variable type issue
can anybody help me ??
thx a lot
SQL SERVER 2005 , SSIS 2005, WINDOWS SERVER 2003 R2
February 23, 2012 at 3:28 pm
You might map index 2 to something. I seem to remember this problem but can't remember how I fixed it. I believe it was tied to trying to skip one of the fields..
CEWII
February 23, 2012 at 4:24 pm
Oups, sorry, i have written date index 3 , but is index 2
so all my field are mapped to the index 0,1 and 2.
despite that mapping , i have this error
February 23, 2012 at 7:05 pm
Are you absolutely sure the datatype is exactly the same?
CEWII
February 23, 2012 at 7:20 pm
yes, in my sql commande ,
SQLSERVER :
select nom,id,date from mytable
nom varchar(50)
id interger
date datetime
this sql commande is linked to the variables Myrecord : type Object
In ssis,
Variables :
User::name string
User::id int32
date datetime
Mapping :
user::name index 0
user::id index 1
user::date index 2
collection parameter in my foreach loop :
user::myrecord
February 24, 2012 at 8:14 am
My first glance would seem to indicate that looks just fine.
Have you verified that the task that fills the object variable is set to full result set? And the result set name of "0" (zero). Also have you checked the scope of the variable, meaning perhaps it exists at more than one scope.
CEWII
February 24, 2012 at 3:06 pm
Hy ,
how can i check that point :
"Have you verified that the task that fills the object variable is set to full result set?"
myrecord is fill by a simple adodatareader and a recorset in a dataflow task.
i can't see nowhere some option like that.
i have changed the resultset name by "0"...but don't understant what is going to change in my case...
same problem changing the name
"And the result set name of "0" (zero). "
there is only one scope for variable : package.
something strange :
i have change datatype, and set only object type.
now it's working fine.
however, when i adding a script task in my for each loop,
i can't acces to my variables as follow :
MsgBox(CType(Dts.Variables(1).Value, String))
MsgBox(CType(Dts.Variables(2).Value, Integer))
i have to write :
MsgBox(CType(Dts.Variables("name").Value, String))
MsgBox(CType(Dts.Variables("id").Value, Integer))
to be able to access my data....
i had some difficulties, but manage to have my data value.
but it seems so strange to don't be able to acces simply data.....
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply