Solved

SSIS - Passing a parameter to an Execute SQL Task

Posted on 2006-07-14
5
2,382 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
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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