OLE DB source using variable to file output

  • We are trying to create a generic payroll package with the end result being an output file (excel or CSV depending on customer) with differing numbers of columns.

    The package is currently failing on the OLE DB source within a Data Flow task. The source is executing a stored procedure using a customer code as an input variable. The number of columns in the output of the stored procedure can vary depending on the input variable. For example:

    Customer A has 3 output columns

    Customer B has 10 output columns

    At the moment I am trying to pass the data to an Excel destination but the package errors because it is "unable to retrieve column information from the data source". I've tried using the delay validation options but no success. It's worth noting that the connection managers are dynamically set using expressions from variables set earlier in the package.

    If anyone has tried anything similar then I'd be interested to know how it was achieved.

    Thanks in advance.

  • If you can change the stored procedure to output as a concatenated string of the required columns data delimited with "," for the respective customer then you can write to CSV directly by defining flat file connection manager.

    If Excel is the required output format then you can acheive this using the derived column transformation.

  • Sowbhari (1/3/2014)


    If you can change the stored procedure to output as a concatenated string of the required columns data delimited with "," for the respective customer then you can write to CSV directly by defining flat file connection manager.

    If Excel is the required output format then you can acheive this using the derived column transformation.

    The headline idea here is that you define your flat file connection as having only a single column and then do all of the work to concatenate your columns and delimiters before this, all in a single column.

    Your single 'column' can thus contain a dynamic number of 'real' columns and delimiters and SSIS should be happy.

    The final comment about Excel is not correct - you cannot switch a file from CSV to Excel format using a derived column transformation.

    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 (1/5/2014)


    Sowbhari (1/3/2014)


    If you can change the stored procedure to output as a concatenated string of the required columns data delimited with "," for the respective customer then you can write to CSV directly by defining flat file connection manager.

    If Excel is the required output format then you can acheive this using the derived column transformation.

    The headline idea here is that you define your flat file connection as having only a single column and then do all of the work to concatenate your columns and delimiters before this, all in a single column.

    Your single 'column' can thus contain a dynamic number of 'real' columns and delimiters and SSIS should be happy.

    The final comment about Excel is not correct - you cannot switch a file from CSV to Excel format using a derived column transformation.

    Phil - Thank you for explaining with additional detail. I should have explained a little bit more rather just giving a high-level idea. Will make sure going forward to put as much as detail I can.

    On the excel part what I mean is not to switch a file from CSV to Excel rather than use derived column transformation to populate the conditional columns according to the customer.

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

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