Can Variable define in OLE DB Command?

Question

I had defined a variable Test which is @[User::Test] with string value "abc".

My question is can I set the sql command that update my table where my column value = my variable ?

example.

update tableA set ValueB = '1pm' where ValueA = '" + @[User::Test]  + "'  

But this is not working for me. How to solve it ?

Answer 1

You have to use a question mark in the SQL text and bind the variable using the query configuration (IIRC, it was the Parameter Mapping tab). For example, I used to do stuff like this:

-- declare vars
declare @table varchar(256);
declare @sql varchar(max);

-- get the table as a parameter
set @table = ?;

-- drop the table if it already exists
if (object_id(@table) is not null) begin;
    set @sql = 'drop table '+@table+';';
    exec(@sql);
end;

-- create the table
set @sql = '
    create table '+@table+' (
        IPID int,
        ...
        _rn int
    );
';
exec(@sql);

Here, I found a screenshot in Google: https://www.simple-talk.com/iwritefor/articlefiles/1455-SsisVariables_Fig11-620x524.jpg