We have migrated from Microsoft SQL Server to Sybase ASE 15.7 and I'm currently in the process of modifying our SSIS packages to work with the new database. I've created a new 'SAP ASE OLE DB Provider' connection to replace the current 'Native OLE DB\SQL Native Client' connection which points to our new database and I'm modifying the OLE DB datasources to use this new connection. I've had to tweak some of the SQL statements slightly in some of the datasources in order to make them work (Mainly just prefixing the table names with SAPSR3. is sufficient to get the datasource working) but I've encountered an issue with one that I've been unable to resolve.
The original select statement, that used to work using the old connection, is as follows.
select b.REQUEST_ID,b.SEQUENCE,b.CALC_TYPE,b.PRICE,b.REQUESTER,
min(a.MATNR) as MATNR,left(a.DELTA,8) as 'DELTA',null as 'PRPRODHR'
from ZPRICE_MODEL_LOG a JOIN ZPRICE_CALC_LOG b
on a.RESPONSE_ID = b.REQUEST_ID
where a.DELTA >= ?
and a.ACTIVE = 'X'
and a.MANDT ='900'
group by b.REQUEST_ID,b.SEQUENCE,b.CALC_TYPE,b.PRICE,b.REQUESTER,left(a.DELTA,8)
However, after it seems that the SAP ASE connection has an issue with a statement that has both a parameter in combination with 'Group By'. I've had no problems with parameters in statements without a 'group by' and, likewise, I've been able to use 'group by' without a parameter in other statements.
Once I've tweaked it for our new database, If I replace the parameter with a hard coded value, as below, then this works fine and returns data.
SELECT b.REQUEST_ID, b.SEQUENCE, b.CALC_TYPE, b.PRICE, b.REQUESTER, min(a.MATNR) as MATNR, left(a.DELTA,8) as DELTA, null as PRPRODHR
FROM SAPSR3.ZPRICE_MODEL_LOG a, SAPSR3.ZPRICE_CALC_LOG b
WHERE a.RESPONSE_ID = b.REQUEST_ID AND (a.DELTA >= 20141001000000) AND (a.ACTIVE = 'X') AND (a.MANDT = '900')
GROUP BY b.REQUEST_ID, b.SEQUENCE, b.CALC_TYPE, b.PRICE, b.REQUESTER, left(a.DELTA,8)
However, the exact same statement with the parameter fails.
SELECT b.REQUEST_ID, b.SEQUENCE, b.CALC_TYPE, b.PRICE, b.REQUESTER, min(a.MATNR) as MATNR, left(a.DELTA,8) as DELTA, null as PRPRODHR
FROM SAPSR3.ZPRICE_MODEL_LOG a, SAPSR3.ZPRICE_CALC_LOG b
WHERE a.RESPONSE_ID = b.REQUEST_ID AND (a.DELTA >= ? ) AND (a.ACTIVE = 'X') AND (a.MANDT = '900')
GROUP BY b.REQUEST_ID, b.SEQUENCE, b.CALC_TYPE, b.PRICE, b.REQUESTER, left(a.DELTA,8)
The Error Message is as follows.
TITLE: Microsoft Visual Studio
Error at Calc Log Step [ECC CALC_LOG [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E10.
Error at Calc Log Step [ECC CALC_LOG [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
The error suggests that it cannot locate the tables in the source database but of course it can locate them just fine when I hard code a value instead of using a parameter. I'm unsure if this issue is with the Sybase data provider or if I've missed something in the configuration that only affects these combinations of parameters with 'group by' statements in sql statements but any pointers or workarounds that anyone can suggest would be much appreciated.
Thanks