looping through a dataset

  • I am quite new to integration services, and have to setup a package that loops through records with a certain status, execute a procedure, and update the status afterwards.

    Database in question is a SQLserver2005 type database.

    I have the following :

    1.

    a SQL task with in the select statement the query to retrieve all affected records. (SELECT message_number from table where status = x)

    Result set is set to 'full result set', and the result set is assigned to a variable of the type 'object' (let's call it 'message_numbers' )

    2. a 'for each container', set to a 'for each ADO enumerator' collection, and assigned to that variable'message_numbers'

    3. a SQL task in that for each container that will execute my procedure.

    This procedure asks for an input parameter called message_number, which i have to retrieve out of the record that is selected at that moment.

    question 1 is : how do i do that?

    Question 2 is : are there maybe better ways to do this?

  • How many records are we talking about here (being returned from your SELECT statement)? If it's a relatively small number, I would be tempted just to do this in a stored procedure and use SQL Agent to run the sp as often as you wanted.

    With an sp, you could insert the records with the correct status into a table variable, and loop through the table variable, running an exec against each record.

    Can't think of a reason to do this in SSIS, unless it's what you are more familiar with.

    Something like this:

    -- create variables

    DECLARE @NumberOfRecords int,

    @Counter int,

    @Message_Number int;

    -- create a table variable to store the records

    DECLARE @ToBeProcessed TABLE

    (

    RowID int IDENTITY (1,1),

    Message_Number int

    )

    -- populate the table variable with the records to be processed

    INSERT INTO @ToBeProcessed

    SELECT message_number from table where status = x

    -- find the number of records

    SET @NumberOfRecords = (SELECT count(*) from @ToBeProcessed)

    SET @Counter = 0

    -- loop through the records

    WHILE @Counter <= @NumberOfRecords

    BEGIN

    SET @Message_Number = (SELECT Message_Number from @ToBeProcessed WHERE RowID = @Counter)

    EXEC spTheDoesTheProcessing @Message_Number

    SET @Counter = @Counter + 1

    END

  • The number of records varies.

    But i've solved it by making the variable mapping go to a string type, instead of a integer type

    .

    (which sounds actually totally counter logical, because the values returned are integer values)

Viewing 3 posts - 1 through 2 (of 2 total)

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