I have a Table say
Table1
Col 1 Col2 Col 3
A X1 1
A X2 2
B Y1 3
C Z1 4
C Z2 5
( Col1 represents Entity names, Col2 represents there respective field name and Col3 represents the values of those field name)
How can i use the above Table1 and update table A, B, C
Ex:
Table A
X1 X2
1 2
Similarly Table B
Y1
3
and Table C
Z1 Z2
4 5
2 Question:
I was trying to use for each loop container. I am getting this error:
Error: Variable "User::ADOVar" does not contain a valid data object
I followed all the steps in the links
http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
and check out other sources but unable to track where the problem is?
Can some body help me out please
Question 1:
This should work...
update TableA
set
x1=(select col3 from Table1 where col1='A' and col2='x1')
x2=(select col3 from Table1 where col1='A' and col2='x2')
Although I'm not sure why you would want to do something like this. Do you only plan on having one record each in TableA,B,C?
Question 2:
What exactly are you trying to do? Are you trying to add Table1 to a result set and then loop through each record and execute a dynamic update statement?
|||Anthony
Question 1:
can I do this in SSIS using task?
Question 2:
Yes I was trying to loop thru the results set from Execute Task and process each row.
My Table1 -> Col1 represents Entity name of the table; Col2 represents the Colum of that particular Entity.
As in my previos example:
I need to process the table1 row by row. It shud Check the Col1 for Entity Name ( Table i need to update) and Col2 for the Colum ( which gives me what Colum in the ENtity name i have to update ) and change the value with Col3
|||
AWM_dB wrote:
Question 1:
can I do this in SSIS using task?
Take a look at the Pivot / Unpivot components in the data flow.
Question 2:
Yes I was trying to loop thru the results set from Execute Task and process each row.
My Table1 -> Col1 represents Entity name of the table; Col2 represents the Colum of that particular Entity.
As in my previos example:
I need to process the table1 row by row. It shud Check the Col1 for Entity Name ( Table i need to update) and Col2 for the Colum ( which gives me what Colum in the ENtity name i have to update ) and change the value with Col3
The error makes it sound like the resultset isn't being set into the variable. Can you confirm that the variable is populated from the first Execute SQL Task by adding a Script Task between the Execute SQL and the For Each, and checking that the variable is not null? Also, verify that the variable is defined at package scope and not on each task.
