Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OLE DB source using variable to file output Expand / Collapse
Author
Message
Posted Friday, January 3, 2014 5:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 4:36 AM
Points: 4, Visits: 150
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.
Post #1527461
Posted Friday, January 3, 2014 9:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:59 AM
Points: 1,195, Visits: 1,069
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.
Post #1527608
Posted Sunday, January 5, 2014 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 5,257, Visits: 12,187
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1527903
Posted Monday, January 6, 2014 2:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:59 AM
Points: 1,195, Visits: 1,069
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.
Post #1528004
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse