3

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`))) = ?;
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
dcfretwell
  • 43
  • 5
  • what is the error message? – Jayvee Dec 22 '21 at 16:23
  • Post some screenshots of your Execute SQL Task. Main tab, the Parameters and Result Sets, if you would – billinkc Dec 22 '21 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 Dec 22 '21 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 Dec 22 '21 at 19:34

1 Answers1

1

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;
Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • 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 Dec 23 '21 at 10:19
  • Removed @ symbol from the error fyi. I had assume there would be no result set as the values are passing into a variable. – dcfretwell Dec 23 '21 at 10:20
  • 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. – Jayvee Dec 23 '21 at 10:40
  • Yes I have two variable defined in SSIS 1) ETL_ID_Provenance_AuditCode 2) ETL_ID_Provenance_LocalUnitCode and both are set to String – dcfretwell Dec 23 '21 at 13:43
  • 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. – Jayvee Dec 23 '21 at 17:08
  • That all works and goes through on SSIS but the variables are not updating with the values so the SSIS variables are not storing the details which seems odd. I have checked the query by replacing the values and I do get results out. Seems odd that the values are not changing. – dcfretwell Dec 24 '21 at 14:01
  • Maybe the input variable @source is not populated when the query runs. You can test it by hardcoding the value in the query, to narrow down the issue to the output. – Jayvee Dec 24 '21 at 14:12
  • I have tried running the code with it hard coded like so: `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))) = '1_1';` But sadly this is still not populating the two variables. The task goes through on SSIS without failure but the values are not populated and I have tried putting a watch on the key fields. Not sure what else to try on this all seems like it should work. – dcfretwell Jan 06 '22 at 14:03