SSIS to Excel output limit fix

With many of the SSIS packages my team writes, we have to output to an Excel Destination. One problem we found is that any data sets over 10k rows would not populate the Excel file all the way. We tried everything from buffer size to exporting to text first – nothing quite worked!

After much research, we tested a theory. Our Network admin modified permissions on the output directory and the account that was running the SSIS and generating the Excel files. I believe it had to have full admin rights on the job submission directory.

Problem solved!

Seems rather odd that because of permissions, the excel destination is limited. We could tell from testing. Limiting the output of the same query to say, 100 records was no issue. 9999 records – no problem. As soon as the limits were off the problem returned. That’s where we figured there was some type of limitation problem.

A couple of other points to the process:

  • We have a generic Batch account to run the processes from the SQL agent, instead of having one of the team member accounts use it. This helps to maintain a single point of permission management.
  • We also have a single output directory on our sharedrive. The ‘job submission’ branches off for each department and individual outputs beneath. The structure is locked down to read-only but users can copy off for themselves and modify the file to their hearts content. One too many times the original file was messed up or deleted.

SSIS Variables

For a number of projects I work on, I make use of Microsoft’s Integration Services (SSIS) solution to run some queries and drop output into a formatted Excel file for our various end users. I’ve only been using SSIS for a little under a year, so I know there’s lots to learn.

One trick I’ve found to be very handy is the use of Variables in the the packages. ┬áSpecifically for this post, I’m focusing on using the expression to make the variable dynamic and using that value to output a file and change the name.

After starting the project, create a variable, making sure that the scope is for the package.

Click the ellipses next to the line you want to create an expression for to bring up the Expression Builder.

In the Expression text box, use the location of the output file (local or network) and concatenate with a date to keep it fairly unique.

!!! One important item to note is that if your package works across the network, you will need to be wary of the escape character. When building the expression, you’ll need to double-up on the ‘\’ for each one used. For example, if you’re output directory is \output, the package will interpret it as “\o”. That’s bit me a few times before I figured out to use “\\output”. See the pic above to see what I mean.

Once you’ve decided on the value for the expression, click ‘OK’ and then the variable will use whatever the calculated value is.

In SSIS, I’ve typically used this for naming the products of the ‘Excel Output’ task [I will detail that in another post]. The variable can be used in the “File System Task” object as the ‘Destination Variable’ or ‘Source Variable’

If the path ever needs to change, you only have to update the variable.