Search code examples
apissisyammerdata-import

Web API / SSIS - A starting point

In the interest of transparancy this is work life related. But I am most definatly not looking for 'the solution' simple a starting points.

The issue;

I've been asked to bring all yammer data into a database. While I'm quite familiar with database created, administrator and moving data to and from flat sources/databses using SSIS. I have virtually zero understanding of web APIs.

I have found that Yammer uses an api to allow for scheduled downloaded of information there.

The Question;

  • Can Yammer be used as a SSIS data source to transform/import into database tables? And if so - how!? I keep getting unauthorised attempts using my own admin credentials.

Thanks,

Solution

  • Yammer has a Data Export API which returns most of the data as a ZIP file containing multiple CSV files. The list of models and attributes is about half-way down the page I linked to.

    This seems more aligned with an SSIS solution, but some data is only available via individual REST calls. Do analysis of what the data export provides to decide if you need to make additional REST calls to get additional metadata.

    I'm not very familiar with SSIS, but the generic process you'd need to follow is:

    • Create a Verified Admin user in Yammer associated with a service account (O365 user with Yammer licence upgraded to Verified Admin in Network Admin.) For testing, you can use any verified admin account, but a service account is a best practice.
    • Log on with the Verified Admin account and register an application.
    • Acquire a token when logged on with a Verified Admin account. You can follow an OAuth flow, or get this from the application information page after registration. This token has the required privileges to export content.
    • Make requests to the export API specifying the correct parameters. Try a small time window without attachments to get started. Test this outside of SSIS with PowerShell before attempting this with SSIS.
    • Expand the ZIP file to a directory on disk. Again, doing this outside SSIS first is going to be simpler initially.
    • Use SSIS to import the CSV files to your database.
    • The CSV files have API endpoints for getting additional metadata on messages, users, groups etc. You'll need to work out how best to call these from SSIS if you really need the metadata, but it's more a question of "how do I make many REST calls with SSIS?"