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

  • 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.

  • 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/

  • 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.

  • @Lynn,

    Yes, doping Index is not an option.

  • 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?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Simply inserts the data from One SQL Table to another

  • 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.

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

  • 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?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • yes

  • Here is the flow

  • 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).

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

  • 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).

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Which version of SQL Server are you using?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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