OK here is a bit of a summary. I haven’t gone into complete detail, just showing you the bits that are common to any attempt at this. You’ll want to work with your development staff and IT team to figure out security, etc. etc. your mileage may vary Here is one way to do this.
In Epicor, create a username with the permissions required to access this information.
Create an OData Connection Manger in an SSIS project to connect. Use the account from step 1 here. I’ve redacted the URL and user here. Test your connection
You should see
Create a connection to your local SQL server – I’ve redacted my server name here. This job runs sceheduled in SQL agent and uses the credentials of the agent account, you may need to set permissions here depending on your security model.
Test your connection. I’m running SQL server 2016, you may need a different native client.
In the SSIS package, I have a simple control flow in my UserLog.dtsx package. The first step truncates a holding table, the last steps checks for data in the holding table and moves it to the historical table. The interesting bit is the data flow task…
In your data flow task, you need a source and destination.
Here is how I have the OData source set up
If you’ve done it correctly and have everything enabled, you should see data when you hit preview
Map your columns in the Columns heading on the left – I just grab everything. Set up your error output per your development standards…
Set up your local SQL server with an OLE DB Destination and configure it. Here’s mine.
And set your mappings up. Here I’m keeping the same names as this is a staging table.