Recordset Destination with Foreach Loop Container - finding indexes

I'm curious to know how other people are handling the Recordset Destination in to be processed by a Foreach Loop Container. It seems a little odd to me that you can only map the parameters by knowing the index of the columns of the Recordset, however, the order that you built the recordset destination doesnt stay the same. I've been debugging for a while to find out that after I saved my recordset destination the order of the fields changed. To some order without a clear logic. I'm going to guess it might be the lineage id.

The bigger problem was this was a really large record set with 60 or so columns. To try and debug the problem of finding the indexes, I had added a Multicast tranform and saved the output to an Excel Destination. Of course, the order I setup the Excel file was the order I got the fields. Why would this not be the case with the Recordset Destination?

I've never seen this behaviour. Its possible to define the order of the fields within the recordset destination. It is the order in which you select columns on the "Input Columns" tab.

-Jamie

|||

Strange. I know I saw it earlier tonight, however I just remote desktoped into my work machine to capture a quick video of it happening, and it worked right this time. I'll try it again in the morning, I'd be more than happy if it was working right (despite having to reorder my indexes again).

|||

There's lots to be said for being a name not a number.

I guess a suggestion at the product feedback centre is required.

Recordset destination used in a FOREACH?

Hi all,

Can a Recordset destination be used as source for a ForEach loop.

Correct me if i'm wrong but the Recordset is stored in a variable of type Object? So what stops my ForEach loop from itterating?

Regards,

Pieter

Well, there are about 70-80 examples of using a recordset as a foreach enumerator (source) between BOL, google groups, and this MSDN forum. Shorter answer, yes.

Here's a good example from Jamie Thompson, http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx, which not only has verbiage, but a .dtsx file as well.

|||Thanks for the reply. After my post i tried a test project and it worked 100%. I then tried again in my actual project and no luck. Since then I rebuilt my entire work project around the test project and it is still working....go figure

Recordset destination used in a FOREACH?

Hi all,

Can a Recordset destination be used as source for a ForEach loop.

Correct me if i'm wrong but the Recordset is stored in a variable of type Object? So what stops my ForEach loop from itterating?

Regards,

Pieter

Well, there are about 70-80 examples of using a recordset as a foreach enumerator (source) between BOL, google groups, and this MSDN forum. Shorter answer, yes.

Here's a good example from Jamie Thompson, http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx, which not only has verbiage, but a .dtsx file as well.

|||Thanks for the reply. After my post i tried a test project and it worked 100%. I then tried again in my actual project and no luck. Since then I rebuilt my entire work project around the test project and it is still working....go figure

Recordset Destination and foreach loop

I have a csv file in which I am reading into a recordset destination and want to than use a foreach loop to cycle through those records and do some things. The problem I am having is after defining the variable name of the records set as results, and than going into the foreach loop, choosing collection, using the foreach ado enumerator, i dont see anything in the dropdown under ado object source varable? I am new to SSIS but I basically want to parse through this file, change some columns in each line and than either update or insert data in a sql table.See the following post for a tutorial on shredding a recordset: http://www.sqlis.com/59.aspx|||

appreciate the quick response, although like I said

i dont see anything in the dropdown under ado object source varable?

|||What scope is your recordset variable at? Is it a package scope or a task / transform scope?

recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1

recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1