We notice our informactia is using DataDirect ODBC to connect to SQL Server based on the log message we have seen:
[Informatica][ODBC SQL Server Wire Protocol driver]...............
My problem is, when informacta tries to invoke a SQL Server stored procedure to insert a row into SQL Server database, the sys.sp_sproc_columns_100 is invoked possibly trying to get parameter info. when millions of rows are inserted, millions invocation of that sys.sp_sproc_columns_100 push the CPU to 90%.
Compared with the execution of a SP in SQL Server management studio, there is no such invocation:
I wonder if there is any kind of setting/parameter in DataDirect ODBC such as odbc.ini which could avoid this, or the informatica application need to change their source code with specific way to call SP through ODBC to fix it....