I am new to GP 2010 and I am given a task to export employees payroll data (hourly) from GP and update in another database ( Third party company).
It needs to run this transaction each night.
I am planning to use visual studio ( Which I am good at).
I need some suggestion to get this done.
I don't know what is best way to export data out of GP 2010 automatically each night ( A smart list, IM or excel macro). If any of these application can update other database too would be great.
Please let me know on this.
Thanks in advance.
A good tool for this would be SQL Integration Services where you could create an integration package for the export and then schedule it to run nightly via SQL Agent.
If you can get the two SQL servers linked together then just write a stored procedure and use SQL agent to schedule/run. Much simpler and less layers of "technology" to deal with.
If both SQL servers cannot be linked together then Mike Harris suggestion of SSIS would be your best option.
We have many custom labor reports that pull from GP and also have time clock data that imports into GP.
Hi Inavat, Could you provide a little more detail on what you are wanting to export? Such as are you looking to export Payroll transactions, Checks, ect..
Its employee specific data ( Nothing which includes any critical data like checks or SSN). It has paycode, shift, position, name and some extra fields.
I could say its non transactional data for GP.
UPR30300 hold all transaction data for employee Salary Details
select * from UPR30300
where payrolcd IN ('H','S') -- 'H= Hourly, s=salary.
Field = 1) UNTSTOPY hold Number of Hours
3) UPRTRXAM = total amt
I dont know much about SSIS.
I was reading , can SQL trigger be not an alternate easiest solution to this?
I learnt later that its not a transactional data table in GP. It was an extender window in GP added to allow additional data stored in a separate table.
Here's a suggestion.
1. Create a VS Console App
2. Use the Windows Scheduler to launch the application.
3. Create an Upsert (update/insert) Stored Procedure to update the database.
4. Write to the event log or send an email out if you encounter any error
We figured out SSIS is the preferred way which is what we are implementing now.
The one risk we are working is with the import GP information application (Empower) database has an interface in .NET which imports data properly to table.
We are inserting/updating directly into the applicaiton tables which is kind of a risk.
Hope fully it will go fine.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13