We have Sequence generator (Next Value) enabled for surrogate keys (SQL Server 2012). When we use the new SCD task, we get the following error:

[Insert Destination [42]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. ".

Is there any know issue that these two features [SCD task and Next Value] cannot be used together?

Bose,

evidently, the new SCD Task uses either the ROWCOUNT option and/or the TOP and/or OFFSET functions.

You can confirm this by using the Profiler, but the major outcome is that you cannot use the Sequence Generator coupled with the SCD Task in SSIS 2012.

I was able to get around this error by changing the "Data access mode" to "Table or view - fast load" on the OLE DB Destination settings.

• Proposed as answer by WorkingOnBI Wednesday, April 17, 2013 3:28 AM

I was able to get around this error by changing the "Data access mode" to "Table or view - fast load" on the OLE DB Destination settings.

• Proposed as answer by WorkingOnBI Wednesday, April 17, 2013 3:28 AM

I was able to get around this error by changing the "Data access mode" to "Table or view - fast load" on the OLE DB Destination settings.

• Proposed as answer by WorkingOnBI Wednesday, April 17, 2013 3:28 AM

Thanks.  That helped me to.  I am working on examples for the 70-463 exam (chapter 7 Lesson 1 exercise 1)

I faced the issue after setting up SCD  as stated in Chapter 7 lesson 1 exercises

jphendrix  work around solved worked for me.

Thanks

Same here for studying 70-463 exam using the Training Kit.

Thanks for the suggestion to change to "Table or view - fast load".

The author should include correction on Chapter 7 Lesson 1,  since every one will not get the SCD working.

All the best, jphendrix, LewieTheGreat, Bigdreams74 for 70-463.

Will love to hear your experience regarding the exam.

Cheers.

you are life saver...

Thank you very much, this worked for me as well.

Thanks also, same problem, same reason - 70-463 exercises.

Thanks jphendrix!  That solved my issue as well.

I had the same problem.

Thanks for help us =]

Thank you for your help.

Thanks, I faced the same error and your solution helped me overcome the issue.

Thanks.  That helped me to.  I solved the problem for 70-463 exam (chapter 7 Lesson 1 exercise 1)