COMMUNITY FORUM

 
When using a SQL command on a task, SC appears to be unable to differentiate between a source column that shares a name with any further SQL commands.
 
 
 
A quick example below I have a map that contains a source column of appointment, but wish to update a table that contains _Appointment as part of the name.   For my example let’s assume my source column has a value of 0001.
 
 
 
SC will interpret the below update statement as ‘Update PSA0001Integration’. 
 
 
 
 
 
DECLARE @Appointment varchar(50)
 
Set @Appointment =  ‘_Appointment’
 
 
 
 
 
Update [PSA_Appointment_Integration]
 
set Processed = 1
 
where Appointment = @Appointment
 
 
 
I believe the best behavior here would be if there a wrapper that would tell SC not to interpret anything inside as a SC variable.  Specifically it could respect the square brackets [] that are typically used in SQL to represent special characters.   Unlike scripting a variable, source columns (particularly when they are a table column) and table names are not easily changed.
 
I understand from Pat Roth that SC used a .net replace of _script = _script.Replace(“_ColumnName”,data), which is the root cause of this. 
 
It’s hard to recommend a specific solution without know how the rest of SC is engineered, but this could be done by using regex syntax to only apply the replace to the _script string that is not inside the designated brackets.
 
At the moment as a work around we create stored procedures, and pass them values from the map to perform an equivalent SQL task, but this is somewhat cumbersome.