Below I have laid out a common issue that many of us encounter while deploying SSIS packages containing CData SSIS Components from a development machine to a production server. The process seems smooth when running the package locally, but upon deployment, various errors such as timeout errors and seemingly empty required connection properties might appear.
Possible Root Cause: OAuth Flow and Account Permissions
In most cases, the root cause of these problems is related to the account executing the SQL Server Agent job during the deployment process. It is essential to ensure that this account has the appropriate permissions to read/write from the location of the OAuthSettings File/Credentials File that was created on the development machine.
Solution 1: Set Up Shared Location for Credentials File
To overcome the OAuth Flow issue, consider setting up a shared location for the Credentials file and then configure the OAuthSettingsLocation Connection Property to point to this new shared location. This ensures that the necessary authentication information is accessible during deployment.
Solution 2: Copy OAuthSettingsFile to Production Server
Alternatively, you can manually copy and paste the OAuthSettingsFile to the location specified in the production server. However, this approach is less suggested as it may lead to inconsistencies between environments and potential difficulties in managing credentials.
Applicability to Other Drivers
The same solutions are applicable when dealing with rsd schema files created from drivers like SSIS Components for REST, CSV, etc.
If you have encountered similar issues or have additional insights, please share them in the comments below. Collaboration within our community will undoubtedly help us overcome these challenges more efficiently.
Please reach out to support@cdata.com if this does not resolve the problem for you.
