Is it possible to do a bulk insert from the Workfront API into SQL Server using fusion? | Community
Skip to main content
R
RyannaBe
Level 2
February 22, 2021
Question

Is it possible to do a bulk insert from the Workfront API into SQL Server using fusion?

  • February 22, 2021
  • 3 replies
  • 2017 views

My goal is to store Workfront task details in SQL to use to build reports in Power BI. Any help is greatly appreciated. Thank you!

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    3 replies

    D
    darin_patterson
    Level 4
    February 23, 2021

    Hi Ryanna,

    Unfortunately, Workfront Fusion isn't designed really to support "reporting use cases" (e.g. I want to report on all of the data in Workfront in some other tool). Workfront Fusion is designed to support transactional integrations (e.g. synchronizing a subset of data in two different systems like Workfront and Salesforce) or automating Workfront processes based upon simple or complex criteria.

      Doug_Den_Hoed__AtAppStore
      Community Advisor
      Doug_Den_Hoed__AtAppStoreCommunity Advisor
      Community Advisor
      February 23, 2021

      Hi Ryanna,

      I invite you to consider our Workfront Snapshot solution, with which you can back up your own Workfront data in a self serve and downloadable fashion, then use results for conversion, archival, or advanced analytic reporting purposes...such as using a SQL Server DTS Package to import the data (which comes in a Microsoft Access .mdb format) so you can then analyze it using Power BI reports.

      Regards,

      Doug

        R
        Rick_MacDuffie
        Level 4
        February 24, 2021

        Could it, probably. I've used fusion for a few bulk export/import things.

        That said, you don't have to put Fusion or SQL in the middle. I have a Power BI model that pulls in 5 years worth of projects, tasks, issues, etc direct from Workfront's API using incremental refresh. Took a little fiddling, but if your comfortable in Power Query you can traverse all the rows of tasks you have in a single query, and build visuals against that.. All it takes is a Web connector in Power BI and some clever M to traverse all the pages in increments of 2000 rows to pull in all the tasks.

          R
          Rick_MacDuffie
          Level 4
          February 24, 2021

          Had to look through my youtube history to recall the video that I found most helpful.

          https://www.youtube.com/watch?v=vhr4w5G8bRA

          Feeling industrious....? Below are two M queries that handle the heavy lifting in Power BI.

          My GetTasks function:

          -------------------------------------------------------------

          (index as number)=>

          let

          Source = Json.Document(Web.Contents("https://<YOUR INSTANCE>.my.workfront.com/attask",

          [

          RelativePath="/api/v12.0/task/search",

          Query =

          [

          apiKey=<YOUR WORKFRONT API KEY>,

          entryDate=Text.From(<YOUR DATE RANGE START>),

          entryDate_Range=Text.From(<YOUR DATE RANGE END>),

          entryDate_Mod="between",

          #"$$FIRST"=Text.From(index),

          #"$$LIMIT"="2000",

          fields="<COMMA SEPARATED LIST OF FIELDS YOU WANT TO WORK WITH"

          ]

          ]))

          in

          Source

          -------------------------------------------------------------

          The important looping pages portion of the Tasks query:

          -------------------------------------------------------------

          let

          Source = List.Generate(

          ()=> [Result = try GetTasks(0) otherwise null, Offset = 0],

          each List.IsEmpty([Result][data]) <> true,

          each [Result = try GetTasks([Offset]+cPageSize) otherwise null, Offset = [Offset]+cPageSize],

          each [Result]

          in

          Source

          -------------------------------------------------------------

          Enjoy!

            Doug_Den_Hoed__AtAppStore
            Community Advisor
            Doug_Den_Hoed__AtAppStoreCommunity Advisor
            Community Advisor
            February 24, 2021

            Inspired, Rick. Thanks for sharing!

            Regards,

            Doug

              Terms & ConditionsAccessibility statement

              Loading footer...