SSIS Script Task

A.W.M.

I want to use script task in order to dynamically filled Table A, B, C from the data Table 1. What script should i used and how can i implement this in SSIS.

Ex:

Table 1

Col1 Col2 Col3

A F1 123

A F2 213

A F5 456

B G1 345

C H2 897

C H5 768

Now i wanna write a script which can dynamically do this:

1) read the each row of Table1

2) Get from Col1 = the name of the table on which it needs to update

3) Get from Col2 the name of the colunm of the table from Col1 on which update has to be made

4) Update that particular colunm with the value in Col3

Results shud be

Table A

F1 F2 F3 F4 F5

123 - - - -

- 213 - - -

- - - - 456

Table B

G1 G2 G3

345 - -

Table C

H1 H2 H3 H4 H5

- - - - 768

jwelch

Why use a script You can do this with a For Each Task and an Execute SQL Task inside it. Use an expression based variable to dynamically create an UPDATE statement based on the values from your table, and use that variable as the source for the Execute SQL.

AWM_dB

Can you point me to an article or an example where I can do this.

I was actually trying to do same as adviced, to use Simple Execute task along with For Each loop container but i am getting this error:

Error: Variable "User::ADOVar" does not contain a valid data object

I followed this link http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

and

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx CommentPosted=true#commentmessage

I did exactly as given but still i get error

Note: The variable which I am using "ADOVar" I was declaring it as an Object. Does this has any effect on it

jwelch

Here's another example I posted a while back, maybe it will help.

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

The variable should be typed as an object.

Did you try the suggestion from the other thread to use a script task to see if the variable was set properly

AWM_dB

I am still getting that error. I am just a beginner in SSIS and poor in VB.NET script. I dont know how to do that. I know Script task but poor in VB Script.

jwelch

I can't tell exactly where the error is from your description.

Try downloading the sample package from the URL I posted above, and make sure that is working for you. Then change it (step-bystep) to meet your needs.