Post reply

UPDATE tblHead SET iHairsRemaining = (iHairsRemaining - 40) WHERE vProblem = 'Perplexing'

  • November 9, 2010 at 8:48 am

    #228682

    Hi,

    I'm pulling my hair out on this one.

    I have a query which produces a dynamic pivot table, like is described here:

    http://www.tsqltutorials.com/pivot.php

    I need to export the results to a CSV or EXCEL file with column names in the header rows.

    unfortunately, when I enter the stored proc name in the 'query' box in the SSIS export wizard it says 'this is not a query' I sort of understand why it would say that, what with it not knowing what the columns are going to be until after it runs, but I still need to get this data into excel or csv...

    Can anyone offer me a suggestion?

    I'm going bald fast enough as it is...

    Thanks!

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • November 9, 2010 at 10:59 am

    #1248456

    Is the database compatibility level set correctly?

    BOL says:

    When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher. For information about how to set the database compatibility level, see sp_dbcmptlevel (Transact-SQL).

    Other than that, try setting the DelayValidation = True for your data flow. (you must already have your ssis package to do that)

    Remember that SSIS can't work with dynamic metadata. If your columns change every time, the package will not work.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 10, 2010 at 1:39 am

    #1248702

    thanks mate, unfortunately it is completely dynamic with different columns coming out each time.

    I have opted to take a programatic approach and write a php script that dynamically creates the sql and then builds a csv file.

    convoluted I know but I'm a pure-bred programmer and as such I'm happy reinventing the wheel all day long 🙂

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

Viewing 3 posts - 1 through 2 (of 2 total)

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