SSIS Pass variable to Execute SQL Update

Asked Dec 11 '18 at 14:20

Active Dec 11 '18 at 14:20

Viewed 98 times

I have a variable called 'Test123' - it is a string.

I then have an Execute SQL Task. I have mapped the variable as a parameter - Direction = Input \ Data Type = String \ Parameter Name = 0 \ Size = 512

The actual SQL is thus:

 update [MyTable] set MyField = ? where MyField2 = 'xxx'

However, I keep receiving the error:

 "Incorrect syntax near '?'

Where am I going wrong? I thought you use the question mark for parameters (that I've mapped to the variable)

asked Dec 11 '18 at 14:20

Michael

I *assume* you are using the `Direct Input` option for your `SQLSourceType` property? The error suggests you aren't. – Thom A Dec 11 '18 at 14:24
Also, i recommend leaving the `Parameter Size` value as `-1`. – Thom A Dec 11 '18 at 14:25
What type of Connection Manager is being used by the Execute SQL Task? – Tab Alleman Dec 11 '18 at 14:25
Already Direct Input yes. Tried Parameter Size as -1 to no avail. Connection Type = ADO.NET – Michael Dec 11 '18 at 14:34
Ahh, there's the problem then. Why aren't you using an OLE DB connection? – Thom A Dec 11 '18 at 14:37
Tab Alberman asked because ? works with OLEDB. I have often resorted to building my SQL statement in a variable, which I often find easier – KeithL Dec 11 '18 at 14:38
2

See this answer in the duplicate: https://stackoverflow.com/a/46739168/1507566 – Tab Alleman Dec 11 '18 at 14:48
@TabAlleman - ah I see, thank you. – Michael Dec 11 '18 at 14:52

0 Answers0