Hi,
Converting SSIS packages from on-premise to the new Azure DWH and following Microsoft's ODBC connection standard guideline. Encountering various problems requiring syntax changes from OLE DB to ODBC.
Unable to get regular T-SQL MERGE statement (Execute SQL Tasks) call to work with ODBC connectors which do work with OLE DB connectors.
Any ideas what the problem might be?
Why don't you do a staging area for the ODBC requirements and then MERGE from that source.
are you getting error messages? If so, post them
Sorry about delayed response. Errors we are seeing with replacing OLE DB with ODBC are:
Merge succeeds if it affects any rows. If it doesn’t affect any rows, then the task fails. The proc is being executed, because it takes a period of time to execute, rather than failing immediately. The failure message is below.
[Execute SQL Task] Error: Executing the query "{CALL uspMERGEDimDate (?)}" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The same issue occurs with deletes – rows to delete = success, no rows to delete = fail (haven’t tested inserts/updates).
Other issues we have come across :
• Slower data flow processing with ODBC vs OLEDB/ADO.NET
• Cannot execute SSIS logging using ODBC
• Cannot use parameters in data flow sources (unless building using an expression for the SQL command)
Are these genuine constraints when working with ODBC or are we suffering some kind of bug?
Thanks in advance
What if your Execute SQL Task just says:
EXEC uspMERGEDDimDate ?
???
Is the ODBC connection setup and working on the calling server, which version is running where 32bit v 64bit.