Earlier I wrote about connecting SSIS to Dataverse for Teams. At the time of writing the article it was not possible to connect to DV4T via KingsWaySoft and SSIS.
I wrote about the theme because I still think it is highly important to merge some data to DV4T in some business scenarios from a Dataverse table. DV4T is essentially built for quick and agile projects, but these projects could need a little data from time to time to hit the ground running. Exporting / Importing data via Power Automate is in my book not a very solid option, and feels like a “work arround”.
In the recent update of KingsWaySoft they just released an Interactive Login Experience
Normally the best practice for SSIS and KingsWaySoft would be with an Application User. The only problem is that Dataverse for Teams doesn’t support application users. With the new type of connection the you have to manually add the username/password for each run. It retrieved a new token for each run.
How to set it up
Open your preferred SSIS tool and start a new connection manager. Notice that I have chosen the Interactive Login for OAuth Type.
Add the username and open “Select Organization”
I am not completely sure what the “Use SDK App” is for, but we have 2 factor authentication, and I had to activate this option for it to bypass the need for App ID.
Click on Retrieve and now we can see all organizations. Dataverse AND DV4T. The BlueberryAPI is a Dataverse for Teams database.
WAIT BEFORE YOU TEST CONNECTION! Click on OK
Right click on the connector and open properties
On the right side make sure you add the API 9.1
NOW you can test the connection 🤗
This is what Dataverse for Teams looks like. 1 table called events.
This is what we see in SSIS 🙌🎉🎈
Big thank you to Daniel Cai for this💪