SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OLE DB source using variable to file output


OLE DB source using variable to file output

Author
Message
mac1871rfc
mac1871rfc
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Sowbhari
Sowbhari
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2086 Visits: 1486
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.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18273 Visits: 20409
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Sowbhari
Sowbhari
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2086 Visits: 1486
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search