ssis - execute stored procedure

Posted on 2010-09-20
Medium Priority
Last Modified: 2013-11-10
I am trying to figure out how to execute a stored procedure from ssis. I have a working stored procedure. It has no parameters. I want to somehow execute it fro an ssis package. I found something at another site that says: Another option is to use expressions in the execute sql task editor.  You could create an expression for the SqlStatementSource property.  A simple example:"exec usp_SomeProc @somevar = '"+ @[User::strSomeVar]+"'"
but I could not figure out how to do that. I just want a simple EXEC dbo.usp_MAINTAIN_UDF_HISTORY.
Question by:qbjgqbjg
LVL 16

Expert Comment

ID: 33720247
Try using


exec usp_SomeProc @somevar = '"+ @[User::strSomeVar]+"'"

Author Comment

ID: 33720657
I was already using SET NOCOUNT ON. Actually I think I may have it figured out. I used execute sql task . And the trick was for the sql command, I had to spell out execute. So the command was
EXECUTE dbo.usp_MAINTAIN_UDF_HISTORY. It worked. Now I am trying to figure out how to make it send me an email if it fails.
LVL 30

Accepted Solution

Reza Rad earned 1000 total points
ID: 33722188
if this is a data flow task, with ole db source,
set data access mode as "SQL Command"
and in sql command text, write like this:
exec mySP

instead of mySP write your stored procedure name,

if your stored procedure has any parameters you should use question mark as each of parameters. but if there is no parameter, just user exec mySP.

LVL 15

Assisted Solution

AmmarR earned 1000 total points
ID: 33723310
Dear qbjgqbjg:

just use a simple Execute SQL Task and type in exec SP_name and it should work fine

check image below, i am using it to call a store procedure and its working fine.

Author Comment

ID: 33725516
I had already tried it with EXEC  dbo.usp_MAINTAIN_UDF_HISTORY in the execute sql task. It did not work. It produced an error.  EXECUTE dbo.usp_MAINTAIN_UDF_HISTORY does work.
We are using sql server 2005. So maybe there is a difference.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

586 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