Fix SSIS OData Connection Issues with Project for the Web
Question details
The user is attempting to connect SQL Server Integration Services (SSIS) to Project for the Web using OData, but the connection manager is unable to load the collection or establish a successful connection.
- Product
- SQL Server Integration Services (SSIS)
- Device & OS
- not provided
- Scenario
- Configuring a data integration pipeline to extract data from Project for the Web using an SSIS OData connection manager.
- Observed behavior
- The SSIS OData connection manager fails, displaying an error stating it cannot load the collection or open the connection.
Ensure you have the correct administrative permissions and valid credentials for your Microsoft 365 and Dataverse environments before attempting to configure the connection.
Verify Dataverse Endpoint and Authentication Compatibility
Project for the Web handles data differently than Project Online. It stores data in Microsoft Dataverse, requiring you to use specific API endpoints and authentication methods instead of traditional OData feeds.
A common reason for SSIS OData connection failures with Project for the Web is treating it identically to Project Online. Project for the Web does not use the same OData model. To resolve this, you must connect to the underlying Dataverse Web API.
Log in to the Power Platform admin center, select your environment containing the Project for the Web data, and copy the 'Environment URL' from the Details section.
Open your SSIS package, add an OData Source, and input the Dataverse Web API URL (e.g., https://[your-environment].api.crm.dynamics.com/api/data/v9.2/) into the Service Document Location field.
Ensure your SSIS OData Connection Manager is configured to use Microsoft Entra ID (formerly Azure Active Directory) OAuth or a Service Principal, as basic authentication is usually blocked by Dataverse.
Click the 'Test Connection' button in the OData Connection Manager. Once successful, load the collections to select the specific Project tables you need to extract.
Manage Your Exported Project Data with WPS Office
While advanced data integration via SSIS requires Microsoft's Dataverse endpoints, you can easily export, analyze, and manage your daily project reports using WPS Office. It offers a free, lightweight suite that is perfectly compatible with Microsoft file formats.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your computer.
- 2. Export Your Data: Export your project data or SSIS execution logs to a CSV or Excel file format.
- 3. Analyze in WPS Spreadsheet: Open the exported file in WPS Spreadsheet to sort, filter, and format your project data effortlessly.
Frequently Asked Questions
Why does my SSIS OData connection work for Project Online but not Project for the Web?
Project for the Web stores its data in Microsoft Dataverse, unlike Project Online which uses a dedicated OData reporting feed. You must configure your SSIS connection to target the Dataverse Web API endpoint instead.
Can I use basic authentication to connect SSIS to Project for the Web?
No. Dataverse and modern Microsoft 365 services require modern authentication (OAuth or Microsoft Entra ID) to establish a secure connection. Basic authentication is disabled by default for security reasons.
How do I find the correct API endpoint for my Project for the Web data?
You can find your specific environment URL by logging into the Power Platform admin center. Navigate to your environments, select the one used for Project, and look for the 'Environment URL' under the details tab. Append '/api/data/v9.2/' to access the Web API.
