When using several of the LeapFrogBI component types, configuration information is stored in Excel. This architecture is used in cases where it is not feasible to upload or input data into a web browser efficiently. Examples include; multi file stage, and rest API collection.
Excel workbooks include the option to automatically “Calculate Workbook” when the configuration is access during the load process. This is a very useful tool as it allows configuration to be created dynamically. For example; you could create an excel formula which uses the current date to suffix file names.
LeapFrogBI uses Excel Interop to carry out the calculation request. While this works well, it may be met with security challenges when using the SQL Agent to automate execution. Follow these guidelines to ensure your workbook can be auto calculated.
1. Ensure your packages are running under a user’s credentials with adequate permissions. If you are using the SQL Agent Identity as the authorized user, then ensure that the SQL Agent service is running under a user account that can access and manipulate the configuration file.
2. Some operating systems may still have issues even if the SSIS package is run under credentials with adequate permissions. This is because the SQL Agent does not use a user profile when executing an SSIS package in the same way that a manual execution would do so even when the package is run using a proxy account with adequate permissions. Luckily there is a work around. Add the following two folders to your file system.
C:\Windows\SysWOW64\config\systemprofile\Desktop
C:\Windows\System32\config\systemprofile\Desktop
Excel is looking for these desktop folders when launched from the SQL Agent and may fail if they do not exist.
Finding the answer to this mystery took quite a bit of digging. Luckily, the fix is quite simple. If you are interested in finding more details, check out these links to some technical blogs & discussions. In the end, it looks like the desktop folder is not created in the latest operating systems, but was present in earlier windows versions. Obviously, Excel requires this folder to get the job done.
