I am trying to pass a parameter to an "Execute SQL Task", but can't get it to work.
I can print the SSIS variable in a script task so I see it has a value.
Then I have my Execute SQL Task.
It has one parameter as:
User::myvar Input VARCHAR @paramName
Then in the SQL Statement I simply have code to email me the contents of that variable (there is other stuff, but for now the email is enough since it shows the variable blank)
DECLARE @paramName varchar(max)
@recipients = 'email@example.com',
@body = @paramName;
I know the code works, because if I use it directly in a Query in SQL Server I get the contents of that variable emailed to me, but in the task I get blank.
Now, per the tutorials I have seen it says you don't need that DECLARE statment in the task. BUt if I remove it I get the following error:
Must declare the scalar variable "@paramName"
But if I put in the DECLARE line, then the contents are blank.