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`))) = ?;

Post some screenshots of your Execute SQL Task. Main tab, the Parameters and Result Sets, if you would — billinkc
– billinkc, Commented Dec 22, 2021 at 16:25
Here is the error message I currently am getting: [Execute SQL Task] Error: Executing the query "SELECT ? = LocalUnitCode, ? = IBDAuditCode FR..." failed with the following error: "String[1]: the Size property has an invalid size of 0.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. — dcfretwell
– dcfretwell, Commented Dec 22, 2021 at 18:41
For a connection type ADO.NET you should use @parametername to map to the variables, which you may define in the Parameter Mapping tab — Jayvee
– Jayvee, Commented Dec 22, 2021 at 19:34

Yahfoufi · Accepted Answer · 2021-12-30 12:13:02Z

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;

edited Dec 30, 2021 at 12:13

Yahfoufi

2,5502 gold badges26 silver badges44 bronze badges

answered Dec 22, 2021 at 19:50

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

dcfretwell Over a year ago

Tried this option and I get the following error message. [Execute SQL Task] Error: Executing the query "SELECT LocalUnitCode = LocalUnitCode, AuditCode ..." failed with the following error: "String[1]: the Size property has an invalid size of 0.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

dcfretwell Over a year ago

Removed @ symbol from the error fyi. I had assume there would be no result set as the values are passing into a variable.

Jayvee Over a year ago

Result set none is correct because it will output to variables. Have you defined the variables ETL_ .. in SSIS variables? Also they should match the data type of the columns they are paired with.

dcfretwell Over a year ago

Yes I have two variable defined in SSIS 1) ETL_ID_Provenance_AuditCode 2) ETL_ID_Provenance_LocalUnitCode and both are set to String

Jayvee Over a year ago

I have updated the parameter definition picture including the size of the output parameters, I put 30 but you can set it to match the length of the columns.

|

Collectives™ on Stack Overflow

SSIS MySQL ADO.net SQL Task Input & Output

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related