January 3, 2014 at 5:14 am
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.
January 3, 2014 at 9:17 am
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.
January 5, 2014 at 8:03 am
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.
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
January 6, 2014 at 2:46 am
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