SSIS MySQL ADO.net SQL Task Input & Output

Question

I am trying to use a SQL Task in Visual Studio SSIS to get two output values stored to variables.

I have done a range of Googling on the issue and have been able to get inserts working but I can't seem to see the output values to come out.

I am have tried using both '?' and @NameVariables but I am not having much luck.

SELECT `LocalUnitCode`,`IBDAuditCode` 
FROM `tablename`.`provenance`
WHERE `DataCaptureTool` <> 'DataCaptureTool'
AND RIGHT(REVERSE(`IBDR_Source`),LENGTH(`IBDR_Source`)-LOCATE('_',REVERSE(`IBDR_Source`))) = ?;

OR

SELECT `LocalUnitCode`,`IBDAuditCode` 
FROM `tablename`.`provenance`
WHERE `DataCaptureTool` <> 'DataCaptureTool'
AND RIGHT(REVERSE(`IBDR_Source`),LENGTH(`IBDR_Source`)-LOCATE('_',REVERSE(`IBDR_Source`))) = @Source;

OR

SET @LocalUnitCode = 
(SELECT `LocalUnitCode` 
FROM `tablename`.`provenance`
WHERE `DataCaptureTool` <> 'DataCaptureTool'
AND RIGHT(REVERSE(`IBDR_Source`),LENGTH(`IBDR_Source`)-LOCATE('_',REVERSE(`IBDR_Source`))) = @Source);

Not quite sure if the syntax is a problem or the parameter mapping or the result set needs changing. If I try and follow the guide that are using for SQL Server it does not appear to function correctly.

Any pointers in the right direction would be appreciated.

Thanks,

David

This is the code I am using in the SQL Statement:

SELECT ? = `LocalUnitCode`, ? = `IBDAuditCode` 
FROM `ibdr_staging_K`.`provenance`
WHERE `DataCaptureTool` <> 'DataCaptureTool'
AND RIGHT(REVERSE(`IBDR_Source`),LENGTH(`IBDR_Source`)-LOCATE('_',REVERSE(`IBDR_Source`))) = ?;

Answer 1

Example of mapping variables to parameters in ADO.NET:

SQL:

SELECT @LocalUnitCode = `LocalUnitCode`, @AuditCode = `IBDAuditCode` 
FROM `ibdr_staging_K`.`provenance`
WHERE `DataCaptureTool` <> 'DataCaptureTool'
AND RIGHT(REVERSE(`IBDR_Source`),LENGTH(`IBDR_Source`)-LOCATE('_',REVERSE(`IBDR_Source`))) = @Source;