SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS2008 won't execute Stored Proc with parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

United Kingdom
226 Posts

Posted - 03/08/2011 :  10:08:54  Show Profile  Visit theboyholty's Homepage  Reply with Quote
I am very new to SSIS and I'm using SSIS 2008.

I have a stored proc in SQL which does all sorts of things like builds tables, imports text files, indexes, updates etc so there's no actual 'output' as such. It is a precursor to more SPs which will populate more tables from external files and build a useable dataset programatically.

My SP is usp_KV_01 (
@FileNameCustomer VARCHAR (255)
,@FileNameProspects VARCHAR(255))

I've created a package and added an Execute SQL Task into the control flow, edited it and set up some of the following properties:

ResultSet: Full result set
ConnectionType: OLE DB
SQLSourceType: Direct Input
SQL Statement: EXEC usp_KV_01 ? ?

In the parameter mapping tab I've created an item:
Variable Name: User::FileNameCustomer
Direction: Input
Data Type: VARCHAR
Parameter Name: FileNameCustomer
Parameter Size: 1

In the ResultSet tab:
Resultname: 0
Variable name: User::FileNameCustomer

Yet when I try to run it I get the message: "
[Execute SQL Task] Error: Executing the query "EXEC usp_KafeVend_Proc01 ? ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

I've run out of ideas. Can anyone tell me what I need to do to be able to run a Stored Proc which requests parameters before executing?

http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Aged Yak Warrior

United Kingdom
616 Posts

Posted - 03/09/2011 :  04:44:23  Show Profile  Reply with Quote
Your stored procedure is expecting two inputs. Try this:

Change the ResultSet to none.

Your Parameter Names should be a number starting from zero, so:
In the parameter mapping tab:
Variable Name: User::FileNameCustomer
Direction: Input
Data Type: VARCHAR
Parameter Name: 0
Variable Name: User::FileNameProspects
Direction: Input
Data Type: VARCHAR
Parameter Name: 1

You say "My SP is usp_KV_01" but appear to be calling a procedure called usp_KafeVend_Proc01. Check the SP names.

Hope this helps

This is a good example: http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000