Cursors are not supported on a table which has a clustered columnstore index

Question

Cursors are not supported on a table which has a clustered columnstore index

irehman

SSC-Addicted

Points: 496
More actions
November 21, 2018 at 8:57 am

#376445

Hello,
I'm using OLE DB connection in SSIS, which inserts the data into SQL Server Table. I'm follwing getting error
"Cursors are not supported on a table which has a clustered columnstore index".
How do I resolve that?
Appreciate your help.

Viewing 15 posts - 1 through 15 (of 19 total)

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

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

irehman - Wednesday, November 21, 2018 8:57 AM
Hello,
I'm using OLE DB connection in SSIS, which inserts the data into SQL Server Table. I'm follwing getting error
"Cursors are not supported on a table which has a clustered columnstore index".
How do I resolve that?
Appreciate your help.

Stop using a cursor!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

irehman - Wednesday, November 21, 2018 8:57 AM
Hello,
I'm using OLE DB connection in SSIS, which inserts the data into SQL Server Table. I'm follwing getting error
"Cursors are not supported on a table which has a clustered columnstore index".
How do I resolve that?
Appreciate your help.

Look at your code and determine if the cursor is actually needed by looking to see if there is a set-base solution.

irehman SSC-Addicted Points: 496 More actions · Answer 3

irehman

SSC-Addicted

Points: 496

November 21, 2018 at 9:05 am

#2014241

@Lynn,

Yes, doping Index is not an option.

Phil Parkin SSC Guru Points: 246690 More actions · Answer 4

irehman - Wednesday, November 21, 2018 8:57 AM
Hello,
I'm using OLE DB connection in SSIS, which inserts the data into SQL Server Table. I'm follwing getting error
"Cursors are not supported on a table which has a clustered columnstore index".
How do I resolve that?
Appreciate your help.

Can you expand on exactly what this OLEDB connection is being used for within the package?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

irehman SSC-Addicted Points: 496 More actions · Answer 5

Simply inserts the data from One SQL Table to another

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

irehman - Wednesday, November 21, 2018 9:09 AM
Simply inserts the data from One SQL Table to another

If all it is doing is moving data from one table to another, you really need to look at a set-based solution.

irehman SSC-Addicted Points: 496 More actions · Answer 7

@Lynn, I'm new to all this. What is Set based solution?

Phil Parkin SSC Guru Points: 246690 More actions · Answer 8

irehman - Wednesday, November 21, 2018 9:09 AM
Simply inserts the data from One SQL Table to another

So this is a dataflow with a standard OLEDB source and destination?

Are you using OLEDBCOMMAND at all?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

irehman SSC-Addicted Points: 496 More actions · Answer 9

irehman

SSC-Addicted

Points: 496

November 21, 2018 at 9:19 am

#2014251

yes

irehman SSC-Addicted Points: 496 More actions · Answer 10

irehman

SSC-Addicted

Points: 496

November 21, 2018 at 9:23 am

#2014252

Here is the flow

Phil Parkin SSC Guru Points: 246690 More actions · Answer 11

If you disable the OLEDBCommand, does the error stop happening?

If yes, you need to reengineer your package. Direct the 'changing' rows to a work table and then call a stored proc at the end to perform all of the updates in one hit (this is what set-based solutions do).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

irehman SSC-Addicted Points: 496 More actions · Answer 12

I deleted the OLE Command, rebuild and still the same issue.

Phil Parkin SSC Guru Points: 246690 More actions · Answer 13

OK, not sure what to do next.
I have no trouble loading a table with a NONCLUSTERED columnstore index using SSIS OLEDB source and target, so if that's an option it may be worth a try (I am using 2017, so if you are on a lower version, behaviour may be different).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246690 More actions · Answer 14

Which version of SQL Server are you using?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.