I am using the free developer version of SQL Server 2019. I have configured the database connections and created packages using Visual Studio 2019. These packages are used to populate my STG transit database and the tables in my DWH data warehouse. The DWH feeds my OLAP cube via SSAS.
To automate this workflow, two-stage tasks was in SQL Server Agent by the first developer of the solution . The first stage executes the packages, and the second stage processes the cube to feed my PowerBI reports.
As my PowerBI reports were not updating, I accessed the SQL Server Agent task history, which returned this error message: "Unable to start step 1 (reason: PWBI\pbi proxy authentication error, system error: Incorrect username or password). Step failed "
Firstly, I have a concern regarding the difference in error while executing a package in Visual Studio 2019 and in SSMS Integration Services catalog. When I run the packages in Visual Studio 2019, I encounter the following error: "Unable to convert MySQL date/time value to System.DateTime" while processing "SRC - table_name.Outputs[ADO NET source output].Columns[column_name]" in the package. However, when I run the package from SSIS catalog, it completes successfully, and I am able to process the cube without any errors. Can someone explain me how this possible? The column in question displays anomalies in the dates. Although it is of the datetime type, it shows lines with 00:00:00.000. However, as this particular column is not relevant to my development needs, I have requested the ADO.NET data source editor to ignore any errors in the error output section.
Secondly, Regarding the proxy error, my main concern is related to the user account mentioned in the error message. I have been using the server for my development work, and currently, I am using a domain account Domain\pbiv2 , which is not the one referred to in the error message. The account PWBI\pbi mentioned in the error message is a local account to the server. Initially, they developed on the local account and then moved to the domain account.
Can anyone explain why SQL Server Agent returns an error about the Proxy of the local account error? How can I fix it in order to automate the process again?