Skip to content


SQL task

Peter Burton asked 5 years ago
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.
Ethan Sorenson Staff answered 5 years ago
This has been added for consideration in a future release.
In the meantime you can get around this by using a script task that connects to SQL so you can specify where the variables are, and where they are not.

If you would like to submit an answer or comment, please sign in to the eOne portal.