SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

theboyholty
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

YellowBug
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
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
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.05 seconds. Powered By: Snitz Forums 2000