[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SSIS - Passing a parameter to an Execute SQL Task

Posted on 2006-07-14
5
Medium Priority
?
2,650 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 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