Calling A Stored Procedure On A Data Source (with Parameters)

This seems to be much more difficult than it was in DTS (or perhaps I just need to adjust to the new way of doing things).

Eventually I found that I needed to use "SQL command from variable" and using two other variables as input parameters. The expresion for the command is

"usp_ValveStatusForDay '" + @[User:ate] + "','" + @[User::Report] + "'"

which typically evaluates as

usp_ValveStatusForDay '18 Oct 07','Report_Name'

This previews correctly and the resulting columns are available for mapping to a destination. So far so good.

By the way, is this the best way to call a stored procedure with parameters?

I have pasted the stored procedure at the end of this posting because I have come accross a puzzling problem. The query as shown below works correctlly but if I un-comment the delete statement, the preview still works and the columns are still avilable for mapping but I get the following errors when the package is executed.


Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider.

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC02092B4.

I realise that I could execute the delete query in a separate SSIS package step but I am curious as to why there is a problem with the way I tried to do it.

At one stage the stored procedure used a temp table and later I also experimented with a table variable. In both cases I got similar errors at execution time. In the case of the temp table there was another problem in that, while the preview worked, there were no columns available for mapping. Using a table variable seemed to overcome this problem but I still got the run time error. eventually I found a way to avoid using either a temp table or a table variable and the package then worked correctly, copying the data into the desitnation table.

It seems to me that if there is any complexity at all to the stored procedure, these errors seem to occur. Can anyone enlighten me as to what the "rules of engagement" are in this regard? Is one solution to use a wrapper stored procedure that simply calls the more complex one?



ALTER procedure [dbo].[usp_ValveStatusForDay]

(

@dateTime DateTime,

@reportName VarChar(100)

)

AS

BEGIN

DECLARE @day VarChar(10)

DECLARE @month VarChar(10)

DECLARE @year VarChar(10)

DECLARE @start VarChar(25)

DECLARE @end VarChar(25)

SET @day = Convert(Varchar(10),DatePart(day, @dateTime))

SET @month = Convert(VarChar(10), DatePart(month, @dateTime))

SET @year = Convert(VarChar(10), DatePart(year, @dateTime))

IF @month = '1' SET @month = 'Jan'

IF @month = '2' SET @month = 'Feb'

IF @month = '3' SET @month = 'Mar'

IF @month = '4' SET @month = 'Apr'

IF @month = '5' SET @month = 'May'

IF @month = '6' SET @month = 'Jun'

IF @month = '7' SET @month = 'Jul'

IF @month = '8' SET @month = 'Aug'

IF @month = '9' SET @month = 'Sep'

IF @month = '10' SET @month = 'Oct'

IF @month = '11' SET @month = 'Nov'

IF @month = '12' SET @month = 'Dec'

SET @start = @day + ' ' + @month + ' ' + @year + ' 00:00:00'

SET @end = @day + ' ' + @month + ' ' + @year + ' 23:59:59'

--delete from ValveStatus where SampleDateTime between dbo.ToBigInt(@start) and dbo.ToBigInt(@end)

exec dbo.usp_ValveStats_ReportName @reportName, @start, @end, '1h'

END