web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Export from GP and upd...
Microsoft Dynamics GP (Archived)

Export from GP and update another database each night

(0) ShareShare
ReportReport
Posted on by Inayat Rasool 135

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.

*This post is locked for comments

I have the same question (0)
  • wmike Profile Picture
    wmike 60 on at
    RE: Export from GP and update another database each night

    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..

  • Inayat Rasool Profile Picture
    Inayat Rasool 135 on at
    RE: Export from GP and update another database each night

    Hello Mike,

    Its employee specific data ( Nothing which includes any critical data like checks or SSN). It has paycode, shift, position, name and some extra fields.

    Something like

    1000,7/12/2011,xxx,yyy,hourly,1

    1001,6/7/2011.yyy,zzz,salaried,0

    I could say its non transactional data for GP.

    Thanks

  • Verified answer
    wmike Profile Picture
    wmike 60 on at
    RE: Export from GP and update another database each night

    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.

  • Suggested answer
    sandipdjadhav Profile Picture
    sandipdjadhav 18,306 on at
    RE: Export from GP and update another database each night

    Inyat,

    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

    2) PAYRATE

    3) UPRTRXAM = total amt

    Thanks

    Sandip

  • Inayat Rasool Profile Picture
    Inayat Rasool 135 on at
    RE: Export from GP and update another database each night

    Mike,

    I dont know much about SSIS.

    I was reading , can SQL trigger be not an alternate easiest solution to this?

    Sandip,

    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.

  • David Zamora Profile Picture
    David Zamora 50 on at
    RE: Export from GP and update another database each night

    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

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    RE: Export from GP and update another database each night

    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.

  • Inayat Rasool Profile Picture
    Inayat Rasool 135 on at
    RE: Export from GP and update another database each night

    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.

    Thanks everyone.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans