Why denormalized data is there in Data Warehosue and normalized in OLTP?May 29, 2017
Post Deployment ValidationsJuly 3, 2017
For a workflow which writes the data to SQL server database as target, the connectivity is established through native ODBC driver and using SQL Server Legacy driver (Dmsss26.so).
Tested the ODBC connection from the Informatica server and was able to successfully connect to the database. However the sessions fail with the below error:
[Informatica][ODBC SQL Serve Legacy Driver][SQL Server] Conversion failed when converting date and/or time from character string.
By changing the driver from legacy to new (SQL Server Wire Protocol: DWsqls26.so), the session/workflow gets succeeded.
Legacy driver DWmsss26.so is meant to be used only for SQL Server Databases of version older than 2005. If used across newer versions it will behave in an unpredictable manner. However Informatica corporation will not support in fixing issues related to legacy as it is no more certified by them.
If connection is being made to newer version of databases, new driver DWsqls* has to be used. DataDirect (ODBC driver provider) supports connectivity to 2012, 2008 R2 and 2008 versions with the usage of new driver.
It is also suggested to add the parameter “Workarounds2=2” to the ODBC entry. This is also a workaround provided by DataDirect for use in odbc.ini with their drivers.
This workaround is used when there is a difference in the column size as specified by the application and the database. It is most commonly used with date and datetime fields in SQL server.
DataDirect’s explanation of the workaround:
Workarounds2=2: Enabling this option causes the driver to ignore the column size/decimal digits specified by the application and use the database defaults instead. Some applications incorrectly specify column size/decimal digits when binding timestamp parameters.
There are many similar workarounds provided by DataDirect. Detailed explanation of different workarounds can be found here:
https://kb.informatica.com/howto/6/Pages/1/154770.aspx explains how to set advanced ODBC tracing.