How to prevent SSIS package creating empty flat file at the destination

It’s very common that SSIS package is developed to generate some output as Flat File (.csv, .dat, .txt etc) by using Flat File Destination component. Content of the same can be sourced from either SQL Server database, some other flat file, excel source & so on.

Designing such an SSIS package is simple and straight forward. But, we get to see that when we run this package, it generates the output (flat) file at the configured destination folder even if there is no record fetched from the source.

Although, this is working as expected but we hardly need to generate the empty (only with the headers) file as the output file. Hence, we would like to put some condition in between somewhere to prevent this SSIS package not to create such an empty file.

And, when we think of some solution to achieve this, we come across something like as following

  1. Deleting the empty output file at the end of the package; if size of the file is 0KB or less than 1KB or so depending upon the size of the header row (if configured to be inserted).
  2. Capturing the record count of the total records being inserted into the file by means of ‘RowCount’ transformation and then using a condition into ‘Conditional Split’ transformation which will produce the output only if RecordCount > 0
  3. Calculating the record count before the ‘Data Flow Task’ or before actually inserting the records into the flat file by means of ‘ScriptTask’ or so and to execute the DFT only if RecordCount > 0.

..…there may be a few more workarounds for this.

Now let’s see which one of these above approaches is best suitable for us.

Approach #1, is not a good one since we are first allowing the file to be generated and then deleting the same by looking at its size. This may have already triggered some other process/job which might be dependent upon the existence of this output file.

Approach #2, although this sounds very promising, but when we try this; it doesn’t work 😦 . And, to prove this, below is what I have created.

In this package, source is having a False select statement i.e. no record would actually be selected out of this. Rowcount is configured to fetch the numbers of rows to be inserted into the destination into a variable ‘user::RecordCount’ and then a conditionalSplit to work on a condition of @RecordCount > 0.

SSIS_OutputFile_1

As was suspected, this package has still created an empty file (with headers) at the destination folder. Hence, not a working solution either.

Approach #3, finally we need to put a condition onto the Data Flow Task (DFT) itself in the control flow page. There can be a simple scenario where Source is some database like SQL Server & we are able to pre-calculate the count of the records being fetched from the source (into a variable RecordCount) by executing that particular query (be it a select query or some stored procedure etc) using Execute SQL task or so. And, therefore in this case structure can be something like as below:

SSIS_OutputFile_2

 

 

 

 

 

 

 

This will allow executing the DFT only if there is some records found to be inserted into the output file and hence no empty flat file will be created.

Now, there can be a little complex scenario where source query is not that straight forward to be reused to pre-calculate the record count whereas the records being inserted into the output file are coming from various logics into data flow (some other type of source like excel etc and by applying certain set of transformations) and hence in this case above solution might not work.

Therefore in that case, we would need to capture the record count in the Data Flow task itself once all the logics/transformations etc are applied.

This can be achieved in two ways:

  1. If record set is small, then we can make use of RecordSet destination in the data flow task along with capturing the record count into a variable and then in the control flow check if RecordCount>0 then execute another DFT to insert the records from that RecordSet object into the output file.
  2. If record set is quite large, then in the main DFT get the output into a working file (exactly the same as that of the main output file, but a temp file) along with capturing the record count into a variable and then in the control flow check for the value of this RecordCount variable and if RecordCount>0 then by means of another DFT generate the actual output file from that working file already present. And at the end of the package, delete that working file.
Advertisements
This entry was posted in SQL Server, SQL Server 2008, SSIS. Bookmark the permalink.

2 Responses to How to prevent SSIS package creating empty flat file at the destination

  1. renaud says:

    really helpful, thanks a lot

  2. Noah says:

    Thanks a lot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s