Post reply

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

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

  • November 21, 2018 at 9:01 am

    #2014238

    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/

  • November 21, 2018 at 9:02 am

    #2014239

    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.

  • November 21, 2018 at 9:05 am

    #2014241

    @Lynn,

    Yes, doping Index is not an option.

  • November 21, 2018 at 9:06 am

    #2014242

    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?

  • November 21, 2018 at 9:09 am

    #2014246

    Simply inserts the data from One SQL Table to another

  • November 21, 2018 at 9:11 am

    #2014248

    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.

  • November 21, 2018 at 9:15 am

    #2014249

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

  • November 21, 2018 at 9:15 am

    #2014250

    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?

  • November 21, 2018 at 9:19 am

    #2014251

    yes

  • November 21, 2018 at 9:23 am

    #2014252

    Here is the flow

  • November 21, 2018 at 9:30 am

    #2014253

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

  • November 21, 2018 at 9:39 am

    #2014256

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

  • November 21, 2018 at 9:49 am

    #2014258

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

  • November 21, 2018 at 9:56 am

    #2014260

    Which version of SQL Server are you using?

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

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