Earlier we have used Microsoft OLEDB JetProvider in SSIS package. After recent update from Microsoft, now we are facing issues with SSIS package. So we have decide to export data to excel using open XML. What should be the best approach for implementation since still we are using (xls) version 1997-2003.
Note: We already tried Microsoft Access Database engine 2010 Redistributable.
from my point of view, you have the following options (all about the ScriptTask unfortunately):
I would recommend following the first approach, but it all depends on your system though
UPDATE: Following the first option, you have to develop a small Web API Service. Here is the link with an example on C#
Per the second option, in order to use external DDLs, such as OpenXML, you have to register it in the GAC (if the installer doesn't). Here is the link with an example of using external libraries. If you are going to follow this option, I would recommend you develop a DDL that would work with Open XML directly and have simple API for calling it from SSIS Script Task. You will register your DDL in GAC and have a link in Script task. It will help you avoid a number of debugging issues.