Iterating the rows of a script component

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Sam_res03 wrote:

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Any particular reason you aren't posting in your other thread on this topic? This is a dup.|||

No particular reason. I just thought I wasn't specific about my problem. I didn't know how to modify the title. I am in pressure to finish off a task and didn't take time to explore how i could modify the title of the post.

Thanks.

|||

Sam_res03 wrote:

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Probably the best way is to push the data into recordset destination and then loop over them with a ForEach loop.

Hopefully this will help:

Execute SQL Task into an object variable - Shred it with a Foreach loop
(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)

In this example the recordset is populated by an Execute SQL Task rather than a recordsset destinaiton, but teh principle thereafter is the same I think.

-Jamie

|||

Jamie,

Thanks a lot for your reply. I think I am almost there...So I have one one column in the recordset. Is this what I should do?

By using a for each loop container, with foreach ado enumerator I can get the id and store in a variable.

Next can I have a dataflow task in the foreach loop...

and in that dataflow task, can I access this id pass it as a variable to an sql command (in the ole db source). The sql command is a select statement with a where clause.I am unable to figure out how that can be done.

I really appreciate your response....

Thanks a lot!!

|||

Sam_res03 wrote:

Jamie,

Thanks a lot for your reply. I think I am almost there...So I have one one column in the recordset. Is this what I should do?

By using a for each loop container, with foreach ado enumerator I can get the id and store in a variable.

Next can I have a dataflow task in the foreach loop...

and in that dataflow task, can I access this id pass it as a variable to an sql command (in the ole db source). The sql command is a select statement with a where clause.I am unable to figure out how that can be done.

I really appreciate your response....

Thanks a lot!!

You're right so far. You have two options to use your value in your SQL statement:

1) Use '?' parameters

2) Use expressions. Some info here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/SSIS_3A00_-Using-dynamic-SQL-in-an-OLE-DB-Source-component.aspx

-Jamie

|||

Hi Jamie,

I took your approach and I get this error..

Error: ForEach Variable Mapping number 1 to variable cannot be applied.

I tried to use a foreach loop container. I mapped index 0 to a user variable of type integer. IN the script component I have the row object cast as an integer.

I also get this error..

Error: The type of the value being assigned to variable "User::ID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Do you see what mistake I am doing? Can you please reply?

Thanks.

|||

What's the SQL statement? What's the type of the first column?

-Jamie

|||

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

|||

Sam_res03 wrote:

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

Hi Sam,

You can set up another variable which uses expressions that casts the object variable to an integer variable (e.g., the expression would look like "<DT_I4> @.[User::objvariable]" for an 4-byte signed integer which maps to the standard Int32 integer). Then you can use that in your where clause.

I do have a question... what is the exact type of your integer column? If it's bigint, it really won't work with a standard Integer type... and you have to use <DT_I8> instead.|||

Jon Limjap wrote:

Sam_res03 wrote:

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

Hi Sam,

You can set up another variable which uses expressions that casts the object variable to an integer variable (e.g., the expression would look like "<DT_I4> @.[User::objvariable]" for an 4-byte signed integer which maps to the standard Int32 integer). Then you can use that in your where clause.

I do have a question... what is the exact type of your integer column? If it's bigint, it really won't work with a standard Integer type... and you have to use <DT_I8> instead.

Yeah, Jon is right. If I were you I would investigate why you can't store it in an Integer variable. Try Int16, Int32 and Int64.

-Jamie

|||

Thanks Jamie. I will investigate it. By the way, if I have a dataflow task(source and the destination) in a foreach loop container and if the number of items it is iterating is x items , does it mean that it will make x calls to the source database?

Thanks.

|||

Thanks Jon. I really appreciate your response. I will try your trick and will let you know soon.

|||

Sam_res03 wrote:

Thanks Jamie. I will investigate it. By the way, if I have a dataflow task(source and the destination) in a foreach loop container and if the number of items it is iterating is x items , does it mean that it will make x calls to the source database?

Yes.

|||

Then that is not an efficient way either making 1200 calls. The only reason i tried to do this was, I didn't want to scan a table with 600k rows and do a look up in a temp table for the needed 1200 rows. I wanted to scan only for these 1200 rows/ids. Is there a way to achieve this? If I have the ids in an object, would i be able to concatenate those ids into a string varible and use that sring variable in a where clause of a select statement? I am getting this data from an Oracle DB. Is there any limitation on using a where clause in a select statement. If so, what is the other to solve this problem?

Thanks Jamie. I really appreciate your response. Thanks a lot!!