• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2720
  • Last Modified:

SSIS - Passing a parameter to an Execute SQL Task

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)
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@mydomain.com',
@subject='Test',
@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.

Any ideas?
0
mrichmon
Asked:
mrichmon
1 Solution
 
RichardCorrieCommented:
youn are not putting any values into variable @parmaName...

if you are trying to create a stored procedure the code should look like

create procedure dbo.SendMyMail
      @ParamName varchar(8000)
as
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@mydomain.com',
@subject='Test',
@body = @paramName
go

then you can run
exec SendMyMail 'Test this'
/richard

0
 
mrichmonAuthor Commented:
No that is not correct.

Actually I found the problem.

It my code was perfect (without the DECLARE line), but I needed to change the connection type to ADO.NET instead of OLEDB
0
 
Einstine98Commented:
You are right michmon...

OLE DB takes variables as below :

SELECT * from table wehre field = ?

and then you have to declare the variable with numbers ...

first parameter used is 0 and so on

other types of connection accept the names

ADO...etc


@Parameter and then using the parameter in the execute task query and you are right you should not declare it....
0
 
NetminderCommented:
Closed, 500 points refunded.
Netminder
Site Admin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now