Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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

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

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee 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.

  • 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

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

  • Suggested answer
    sandipdjadhav Profile Picture
    sandipdjadhav 18,265 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

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

  • 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

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

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans