There are tools in Excel to pull data from a myriad of sources but it doesn't seem to be much for pushing data out directly to a database.
In a nutshell, I have a workbook used by analysts for data entry. Around 30,000 records are generated that must be ingested by an SQL Server database.
My options are:
- Use VBA with an ADO connection/recordset. It works but it takes far too long to execute.
- Use an external SSIS package. Export output into separate workbook, upload that to network location, then use VBA to trigger the package for ingesting data. This works but it is awkward and difficult to automate.
- Use BCP utility. Haven't looked into this yet, but seems almost as clumsy as the SSIS package. It would also require an installation on the end-user system, so that won't work.
I'm currently stuck using #2, which is a bit unsatisfactory. I'd like to keep everything within Excel/VBA if I can to avoid installation/permission issues for the end-user.
Does anyone have experience/advice on this? Thanks in advance for the time and wisdom