Post reply

ForEach Loop Container question

  • February 24, 2011 at 11:15 am

    #234991

    Can ForEach Loop container loop through the rows in a table? If so, how?

  • February 24, 2011 at 11:42 am

    #1291163

    Yes.

    Create a variable type of "object" to hold the recordset for looping.

    Create an Execute SQL Task that has a ResultSet property of "Full result set" configured

    Add your select statement to the Execute SQL Task

    Add the object type variable defined as the Variable Name ("Result Name" should be 0)

    Create a Foreach Loop with type of ADO Enumerator

    Assign the aforementioned object variable to the "ADO object source variable"

    Under the Variable Mappings, assign the columns in the result set (0, 1, 2,....) to variables you need inside the Foreach Loop

    Perform your tasks you want iterated

  • February 24, 2011 at 12:07 pm

    #1291186

    thanks!

  • October 18, 2011 at 11:28 pm

    #1396455

    Hi,

    Sorry for replying to this old post. I did not want to create a new thread again with almost similar issue.

    Hence, I am replying to this one.

    I am facing an issue at the Foreach container. After doing the aforementioned configuration for the Foreach Loop Container, I am getting the :

    "The type of the value being assigned to variable "User::_STARTWEIGHT" differs from the current variable type."

    My full resultset Object variable is User::_StartweightResultset and the User::_STARTWEIGHT data type is Int32.

    I have looked everywhere but could not find the result. Any help would be greatly appreciated.

    Regards.

  • October 19, 2011 at 8:09 am

    #1396649

    My first guess is that you are not picking up the correct value from the resultset. Such as there are 2-3 fields in the result set but you are accidently picking up a text field and trying to put it in an int, I had this happen recently.

    CEWII

  • October 19, 2011 at 9:55 am

    #1396749

    Thank you Elliott for the reply.

    Problem solved :).

    One more question I have is..I am in the process of replacing a stored procedure with an SSIS package for a data pump. In the stored proc, I have a cursor which inserts data into 4 different tables which are linked together with a primary key (ID) which is an identity column in the first table. When I designed the pump in SSIS, I am getting only one row inserted in the tables and the next iteration results in the violation of the primary key. This is because only one row is inserted in the first table and the ID is the same. I want to know how we can implement the cursor which inserts ID number for each iteration.

    Regards,

  • October 19, 2011 at 12:35 pm

    #1396878

    Thats pretty tricky to do in SSIS, you have to capture the identity value and pass it back out, which depending on how you are doing the insert may not be possible. This is one of those cases where I tend to create a set of tables in tempdb with a name that is unlikely to conflict and then dump the data into those tables and then execute a sproc to load the data. If you are loading a LOT of data this may be impractical. Also within the loading of the other 4 tables you could potentially do a lookup of the key value you need. This could be a scenario when the lookup is configured for no caching but it depends on how the package is built.

    CEWII

  • October 19, 2011 at 1:16 pm

    #1396912

    Thank you very for your prompt reply Elliott.

    It looks like SSIS is going to be a challenge for me. 🙂

    Yes, I am loading a lot of data into these tables and also the tables are created using the OO methods which makes it more complicated. Do you know if I can find a site/link for designing in SSIS with OO for ETLing?

    Regards,

  • October 19, 2011 at 1:35 pm

    #1396931

    I don't know of a site. As far as ETL'ing of data I try to do everything I can in SSIS. As an example:

    This pulls some WMI data from a server/cluster and was trying to correlate it, this is a version that later was MUCH worse.. It was replaced with:

    Which pulls the same data plus more and dumps it into some tables. Then a sproc correlates all the data, and it runs faster..

    Sometimes it is either impractical or simply too difficult to do everything in SSIS, in those cases dropping the data into a table or tables and manipulating it there makes a lot of sense. But I always start by trying to manage it in SSIS.

    CEWII

  • October 20, 2011 at 12:23 pm

    #1397606

    Wow..that looks quite a bit of work.

    Thank you so much for the info.

    Regards.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply