April 23, 2014 at 9:48 am
Hi, after getting pretty deep with SSIS I put it down for six months. Now I can't do something embarrassingly simple. After spending a couple of hours on it and researching the matter I am back here with my begging bowl out.
All I want to do is have an Execute SQL task select an integer field from a table, put the value of each one into a user-defined variable, and iterate through a foreach container with each new value. All I want to do with the foreach container at the present time is have a script task that uses a message box to display each value.
In the execute sql task I enter the connection and the SQL query. The query is correct, it looks like "select staff_id from <table>." I run it in SSMS. Yes, it is correct! I am unable to set the resultSet to "full result set." I want all of the staff_id values from this table, not the first one. However, I can't use "full result set." In the result set page, I define a result set name of "0", and select the variable name I have defined. This is named "report_staff_id", has Package scope and the data type is Int32.
I run the package and invariably get an error on the Execute Task: "The type of the value being assigned to variable 'user::report_staff_id' differs from the current variable type." Let's see, my variable is Int32. The staff_id field from the table is an integer. Why does it differ? I try changing the variable to Int64, UInt32, UInt64. None work.
I wish SSIS could possibly tell the user what the "current variable type" is; as I don't have the foggiest notion why the integer in my table can't map to an integer variable that I have defined.
Can someone help? Thanks.
April 23, 2014 at 10:20 am
Try using a variable of the type "object" to retrieve the recordset. Then a variable of the same type as the column in a For Each loop.
😎
April 23, 2014 at 12:31 pm
I wish SSIS could possibly tell the user what the "current variable type" is; as I don't have the foggiest notion why the integer in my table can't map to an integer variable that I have defined.
How can a resultset of integers map to a single integer variable? It can't, and therein lies your problem.
As per the previous post, using a datatype of Object is the only thing you can do when you are using the 'full resultset' option.
You can then 'shred', or iterate around, the multiple values in the object variable in a foreach loop or in a Script task.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 23, 2014 at 12:35 pm
Thanks, Eirikur Eiriksson. I've got it going. I appreciate the help!
April 23, 2014 at 12:36 pm
Thanks, Phil. I now see the error of my ways! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply