May 23, 2022 at 2:43 pm
Hi All,
I'm pretty new to ssis so hoping i can get some suggestions and/or examples on this. I'm using flat file destination task to export data from a sql server table to a text file. I need to limit the output file size to 96mb. I cannot find a way to limit the size in the advanced editor of the flat file destination task. Is there a way to do this within ssis? are there any examples on how to accomplish this.
Thanks in advance for any info you can provide.
May 23, 2022 at 3:15 pm
I'm not aware of such setting. https://docs.microsoft.com/en-us/sql/integration-services/data-flow/flat-file-custom-properties?view=sql-server-ver15
I guess it is usually handled at the input side / codesnippet to make sure you don't go over 96mb of data
May 24, 2022 at 3:05 am
I'm using flat file destination task to export data from a sql server
May 25, 2022 at 3:07 pm
I also think you'll have to do the limiting of the output data size at the source, i.e. use a SELECT TOP(calculatedNumberOfRows) * FROM yourTable. The number of rows that correspond to your output file limit will probably have to be determined by some sort of calculation or simply trial-and-error testing.
The alternative to TOP is the more powerful OFFSET-FETCH filter
Using this could allow you to "page" through your input table.
May 25, 2022 at 3:20 pm
A different approach that may be possible is to feed you table source into a Destination Script Component. In this you can define more than one downstream named output destinations. This should also allow you to do some row-based calculations of the aggregate output size.
Based on the calculated aggregate data size, you can then switch between the downstream output definitions.
This is theory of course. I haven't tried it out. 🙂
May 25, 2022 at 5:19 pm
Thanks much for the suggestions i appreciate it. I will be trying to research more on the offset fetch function.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply