SSIS2008 won't execute Stored Proc with parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS2008 won't execute Stored Proc with parameters

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-03-08 : 10:08:54

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

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2011-03-09 : 04:44:23

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
next:
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources