Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  How do I use ssis to replace a cursor

Author  Topic  csaville Starting Member 1 Post Posted - 2010-05-19 : 18:56:24 I have a cursor which does the following: Table A OptionID StartDate AllocationID NumberOfNights 1003 2009-04-24 0:00:00.0 200 3 1003 2009-04-24 0:00:00.0 210 3 While @vloopCounter is less than the @vNumberOfNights we insert a row with the initial StartDate and ensuing dates into a new table. (The business logic is to know what has been allocated on any specific date.) WHILE @@FETCH_STATUS = 0 BEGIN SET @vloopCounter = 0 PRINT @vBookedOptionID WHILE @vloopCounter < @vNumberOfNights BEGIN SELECT @vThisDate = @vServiceStartDate + @vloopCounter Table B OptionID UsageDate AllocationID 1003 2009-04-24 0:00:00.0 200 1003 2009-04-25 0:00:00.0 200 1003 2009-04-26 0:00:00.0 200 1003 2009-04-24 0:00:00.0 210 1003 2009-04-25 0:00:00.0 210 1003 2009-04-26 0:00:00.0 210 The cursor is taking forever to run and I have been tasked with creating an SSIS package. Any suggestions?? Sample data OptionID StartDate AllocationID NumberOfNights 1001 2009-06-12 0:00:00.0 100 2 1001 2009-06-12 0:00:00.0 101 2 1002 2009-06-16 0:00:00.0 110 2 1002 2009-06-16 0:00:00.0 115 2 1003 2009-04-24 0:00:00.0 200 3 1003 2009-04-24 0:00:00.0 210 3 Results OptionID UsageDate AllocationID 1001 2009-06-12 0:00:00.0 100 1001 2009-06-13 0:00:00.0 100 1001 2009-06-12 0:00:00.0 101 1001 2009-06-13 0:00:00.0 101 1002 2009-06-16 0:00:00.0 110 1002 2009-06-17 0:00:00.0 110 1002 2009-06-16 0:00:00.0 115 1002 2009-06-17 0:00:00.0 115 1003 2009-04-24 0:00:00.0 200 1003 2009-04-25 0:00:00.0 200 1003 2009-04-26 0:00:00.0 200 1003 2009-04-24 0:00:00.0 210 1003 2009-04-25 0:00:00.0 210 1003 2009-04-26 0:00:00.0 210