passing a variable to an execute sql task sql script

Question

passing a variable to an execute sql task sql script

david_h_edmonds

Default port

Points: 1430
More actions
May 23, 2018 at 6:11 am

#346821

hello, I have written the below sql script to update a table however I am having an issue passing a package variable to the script.
can anyone tell me if I am on the right lines here please?
Is it allowed to set a scalar variable to a ?
Declare @Table_Name varchar(100), @Table_Name_Short varchar(100)
set @Table_Name = ?
set @Table_Name_Short = rtrim(substring(@Table_Name,1,charindex('-',@Table_Name,1)-1))
declare @sql varchar(5000)
set @sql =
'UPDATE ' +
@Table_Name_Short +
' set ' + @Table_Name_Short + '.' + 'P360_value1 = tpmapUPDATE_1.P360_value1,'
+ @Table_Name_Short + '.' + 'UpdateDate = CONVERT(date,getdate())
from
tpmapUPDATE_1
left outer join ' +
@Table_Name_Short +' on ' +
@Table_Name_Short +'.AGM_System = tpmapUPDATE_1.AGM_System and
rtrim(' + @table_name_short + '.TP_Value1) = rtrim(tpmapUPDATE_1.TP_Value1 )
where
RTRIM(tpmapUPDATE_1.table_name) = ' + '''' + @Table_Name_Short + '''' +' and
rtrim(' + @Table_Name_Short + '.' +'AGM_System) =rtrim(tpmapUPDATE_1.AGM_System) and
rtrim(' + @Table_Name_Short + '.' + 'tp_value1) = rtrim(tpmapUPDATE_1.tp_value1)and
rtrim(' + @Table_Name_Short + '.' + 'P360_value1) <> rtrim(tpmapUPDATE_1.P360_value1)

'
exec (@sql)
my parameter mapping is as follows:
my variables are as follows:
variable table_name is the filename of a text file in a for each loop container.
hence it being blank here.
Many thanks,
Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

I wouldn't do it like this. Use an expression within SSIS to create your SQL and store than value in a variable; then, instead of using the option Direct Input for SQLSourceType, use Variable, then select your variable for the SourceVariable option.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

I think it depends if your Execute SQL task is using an OLEDB connection or ADO.NET connection to the database, they work differently.
If using ADO.NET connection then you use an undeclared variable in your script such as:
SET @Table_Name = @Parm_Table_Name
then you use the Parameter Mapping page of the Edit dialog of the SQL Task and map your package variable User::blahblah to the ParameterName @Parm_Table_Name.

If using an OLEDB connection, then you use the ? notation like you did above.

Of course you could use the dynamic SQL method Thom describes if you are sure you have clean values for your parameter and are avoiding SQL Injection.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Thom's method is the way I would go.

I'd probably create a package parameter to contain the 'template' SQL, so

SQLTemplate = '
Declare @Table_Name varchar(100),
@Table_Name_Short varchar(100)

set @Table_Name = the_table_name
set @Table_Name_Short = rtrim(substring(@Table_Name,1,charindex(''-'',@Table_Name,1)-1))
declare @sql varchar(5000) ...
'
And then define a package variable SQLVar with an expression like

SQLVar = Replace($Package::SQLTemplate,"the_table_name", the_real_table_name)

and then use SQLVar as the source for the Exec SQL task.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

david_h_edmonds Default port Points: 1430 More actions · Answer 4

Hi all, thanks for the advice.
I have gone with Thoms suggestion and it's working very nicely.
I am having an additional issue with importing the data due to differing datatypes in a column but I will post it in a separate thread.

Have a good evening all
,
Best Wishes
Dave