Solved

SSIS - Passing a parameter to an Execute SQL Task

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 40
SQL Server 2012 Row Selection 2 29
Increasing Identity length in sql server 4 19
Stored procedure 23 0
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now