SSIS foreach variable type error msg, using sql statemnet in var.

[tdrclan]

------------

I'm trying to read several table/views from a third party database and combine them into one table in our sql database.
I think I got the ssis package built the way I think it should be.
but I'm getting error when I debug it.
I think I got the variable setup correctly for what I'm trying to do.

I'm trying to built the sql statement (sample at the bottom) and use each as the input souce in the data flow.

--------------


I'm getting the following error:


SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ViewName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Get VeiwNames, Execute SQL Task: Executing the query "select 'select * , CLYEAR + CLMONTH + CLDAY + CLAC..." failed with the following error: "The type of the value being assigned to variable "User::ViewName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Get VeiwNames
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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 "Package.dtsx" finished: Failure.

ssis info:
cant seem to post with sql not preceeded by --


exec sql task:
-- select 'select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from ' + CLGROUP
from luminxclaimsBuildGroupMaster
where transid > 577 and
jobname = 'part1'

result set : User::ViewName

variable: User::ViewName package string value "select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from v_FV1215 " (without quotes)

Foreach Loop Container:
Foreach ADO Enumerator
ado object source: User::ViewName
enum mod: first rows in table

data flow source:
I was able to get this data the first time I tried to post this. but no I can get past "validation foreach container"
If needed I try to post it later.


exec sql results ( should be)
--select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from v_FR1213
--select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from v_s210413o
--select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from v_FV1214
--select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from v_S212112S
--select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from v_S210212S
--select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from v_eapc0813
--select * , CLYEAR + CLMONTH + CLDAY + CLACLMNO as claim from v_FV1215

 

[TheBugSlayer]

You should have a Execute SQL Task that retrieves the list of table names from the database AND assigned it to a variable of type OBJECT; resultset is "Full result set".

Connect the Execute SQL Task to a For Each Loop container with a collection of Foreach ADO Enumerator. The ADO object sourve variable should be the OBJECT type variable to which you assigned the resultset in the previous task. In the Variable Mappings section, select a variable that you previously defined- this variable will contain the successive table names. Assign it an index of 0. It does not look like the query in the previous task needs to return more than one column.

Add another Execute SQL Task inside the container above. Connect the container to the previous Execute SQL Task.

Then look at my other response to you in your previous post for how to build the query variable.



MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[tdrclan]

<<You should have a Execute SQL Task that retrieves the list of table names from the database AND assigned it to a variable of type OBJECT; resultset is "Full result set". >>
I think I did this already
I did have to create the object var
two vars are
viewnames is object type
Viewname is string type

<<Connect the Execute SQL Task to a For Each Loop container with a collection of Foreach ADO Enumerator. The ADO object sourve variable should be the OBJECT type variable ...>>
did this
viewnames ---- ado object source
Viewname ---- variable mapping


<<Add another Execute SQL Task inside the container above. Connect the container to the previous Execute SQL Task.>>
to do what? list the variable? ..

I was following this link to cretae the package,

https://www.simple-talk.com/sql/ssis/implementing-foreach-looping-logic-in-ssis-/

I wish I could paste screen images but the link above is what my package mostly looks like.
he is going to a flat file and using

<<SELECT FirstName, LastName, JobTitle
FROM HumanResources.vEmployee
WHERE JobTitle = ? >>
and sql command for data access mode


I'm going to another table and I'm using
sql command from variable
user::ViewName in variable name field


and still getting

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at Get View Name: The type of the value being assigned to variable "User::VeiwNames" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Get View Name, Execute SQL Task: Executing the query "select 'select * , CLYEAR + CLMONTH + CLDAY + CLAC..." failed with the following error: "The type of the value being assigned to variable "User::VeiwNames" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Get View Name
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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 "Package.dtsx" finished: Failure.

 

[jjefferson]

Is there some reason in the current error text that the variable is now "VeiwNames" ? Do you perhaps have two variables with a slight spelling difference, and are focusing on the wrong one? Just a thought.

 

[TheBugSlayer]

I was introduced to imgur yesterday on this site. See if you can upload an image there and the link here.

The second Execute SQL Task, the one inside the For Each Loop container is the one that's going to execute your insert, isn't it? Once for each one of the tables passed through the table name variable which you mapped in the.

See the image that I uploaded from the sample I put together for your question. I hope it helps.

It looks like you are pretty close. Check your variables and mappings one by one. According to the error message, it appears the datatype you are selecting in the mapping does not correspond to the datatype of the variable. Either that or your 'dynamic' query is not formed properly.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[tdrclan]

we can close this

I recreated the package from scratch and it is now working.

it appears that when the variable was changed/added after the package was built the messed up the variable so that the package could not work.