CODEWITHSUNDEEP
ssis
InTheWorldOfCodingApplications
InTheWorldOfCodingApplications

Reputation: 2616

One Variable for more than one Parameters in ExecuteSQL Task

I have multiple UPDATE statements inside my ExecuteSQL Task. Each one is dependent on one Vairiable e.g. MyId 

UPDATE TABLE_A SET COL_A={Something} WHERE ID=?
UPDATE TABLE_B SET COL_B={SomeTHing} WHERE ID=?

This Query takes MyId Variable as Parameter.

Do i need to have as many parameters as my Update Statements are OR there is a way to Have one shared parameter defined inside my ExecuteSQL Task

Upvotes: 2

Views: 2599

Answers (2)

billinkc
billinkc

Reputation: 61201

The other option is to change your Connection Manager from an OLE to an ADO.NET provider type. ADO.NET connection managers use named parameters instead of ? so you can re-use them instead of dealing with ordinal positions in the mapping tab. We often have 2 pointing to the same database, one OLE for Data Flow type components and an ADO.NET for Execute SQL Task. 

UPDATE TABLE_A SET COL_A={Something} WHERE ID=@id;
UPDATE TABLE_B SET COL_B={SomeTHing} WHERE ID=@id;

Upvotes: 3

William Salzman
William Salzman

Reputation: 6446

Try this:

DECLARE @id int

SELECT @id = ?

UPDATE TABLE_A SET COL_A={Something} WHERE ID=@id
UPDATE TABLE_B SET COL_B={Something} WHERE ID=@id

Upvotes: 7

Related Questions