SSIS: Passing paramters to and from stored procedure

SSIS: Passing paramters to and from stored procedure

Execute SQL task could be used for this purpose. The applications are very practical and many. If you can not get this to work and are still getting confusing error messages then I recommend installing SQL SERVER 2005 SERVICE PACK 1.

Check out Kirk's post on parameter setup based on connection type such as ado.net or oledb etc.

Here is the test proc

CREATE PROCEDURE [dbo].[testproc]
    @i int ,
    @j int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    set @j = 33
END

and here is the test script to execute it.

USE ADVENTUREWORKS
GO
declare @m int
exec dbo.testproc @i = 2, @j = @m output

print @m

Inside the ssis package:

Variable settings are as follows:

Name = i
scope = execute sql task
data type = int32
value = 1

Name = i
scope = package
data type = int32
value = 1

In the Execute SQL Task Editor here are the important settings

--General Tab

Resultset = none
connection type = ole db
connection = servername.AdventureWorks
sqlsourcetype = direct input
sqlstatement = exec dbo.testproc @i = ?,@j = ? output

-- Parameter Mapping

variable name = User::i
direction = input
data type = long
parameter name = 0

variable name = User::q
direction = output
data type = long
parameter name = 1

Execute the package and if every thing goes well execute sql task will turn green. If not then please check the following

variables: check the scope, data type etc, upper case or lower case name, make sure that name is exactly the same.
Inside the execute sql task editor:

Check the following settings: Result Set, connection type, connection, specially check the sql statement and parameter mapping. Make sure that that parameter name have the correct ordinal numbers and directions are setup up correctly as input and output and double check the data type.

If it still does not work then check out the error messages in the output window and trouble shoot accordingly.

HTH..

ZULFIQAR SYED

Comments

[Bopanna Kallichanda]

This helped me. The problem with my package was not having direction = output

[ZULFIQAR SYED]

Thanks for your feedback Bopanna.

[Jesus]

hey,I've checked your procedure and it's very interesting...unfortunately, what I'm looking for is using a package inside a visual basic form.Inside the package runs a SP that HAS to use a variable to show the information the user wants,you know what I mean??..I hope so.Anyway,the thing is that I can't find the way to pass this variable through the form to the package so I can see the info I want.Hope you can help me with this.
Thanks. Jesus

[sg]

is this issue the same for running some SQL Task? I have been using jsome insert and updating commands with parameter using'?' in OLEDB conn but it is throwing a vague error:

[Execute SQL Task] Error: Executing the query "UPDATE dc SET dc.payrollType = dcs.PayrollType, dc.checkDate = dcs.checkDate, dc.periodBegin = dcs.periodBegin, dc.periodEnd = dcs.periodEnd, dc.description = dcs.description , dc.status= dcs.status, dc.frequency = dcs.frequency, dc.emplType = dcs.emplType, dc.emplFilter =dcs.emplFilter, dc.lastETLDate = ? FROM dimCalendarDetail dc inner join DimCalendarDetail_Staging dcs ON dc.co = dcs.co WHERE dc.calendarId = dcs.calendarId AND dc.batch = dcs.batch SELECT @@ROWCOUNT AS rowUpdate " failed with the following error: "Invalid object name 'dc'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I found couple of blog mentioning it could be because of Service Pack. Now I have new service pack installed (SP 2 for sql2005) and is still showing the same error. Any help will be highly appretiated.

Thanks

[Jasmine]

this gave me a huge help.thnx a lot

[Gonzalo]

Hi ...
your problem seems to be in the ", dc.lastETLDate = ? FROM " part of the query...i have the same problem ... and found that the query parser does not allow using the "?" as parameter in the SELECT part of the query. it only permits to parse queries with the parameters in last part of the query as the WHERE condition

hope it helps .

[Atul Saxena]

Thanks
for the valueble support.
Can u tell me how can I run the same package with different combination of source and destination dyanmically . I want to run the ETL in single shot

[Unpro]

Finally, a sample that works!!!

but why OLE? Why does my .Nyet fails?...

[Laura]

Thank you!! You really helped me

[Lily]

this was the first site of many that defined it clearly and worked first time! thanks! ><

[Alan Barber]

Thanks very much for this. I couldn't get the syntax for OLEDB right for ages until I read your post. Cheers.

[Hamed]

Thanks for this great post