July 14, 2008 at 4:07 am
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?
July 14, 2008 at 6:44 am
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
July 14, 2008 at 7:39 am
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