Solved

SSIS - Passing a parameter to an Execute SQL Task

Posted on 2006-07-14
5
2,495 Views
Last Modified: 2012-06-21
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
Comment
Question by:mrichmon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 17109494
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
 
LVL 35

Author Comment

by:mrichmon
ID: 17109776
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17111326
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
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 17157091
Closed, 500 points refunded.
Netminder
Site Admin
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question