SSIS MySQL ADO.net SQL Task Input & Output

user_13389200 0
Viewed 232

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

enter image description here enter image description here enter image description here

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`))) = ?;
output ssis ado.net input mysql
1 Answers

Example of mapping variables to parameters in ADO.NET:

enter image description here

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;
user_29649630
Related