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 2005 Forums
 SSIS and Import/Export (2005)
 SSIS error using ? parameters

Author  Topic 

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-06-24 : 08:35:44
I have the following expression:

EXEC staging.InsertHeader '?', ?, ? OUTPUT

And have created the following parameters:
User::LastPartitionDateForSalesRetention  Input  VARCHAR 0
User::DataTransferSchemaID Input SHORT 1
User::RowCountOUTPUT Input LONG 2

And the following ResultSet to hold the OUTPUT param results:
RowCount User::RowCount

I get the following error:
Error: 0xC002F210 at Insert Header, Execute SQL Task: Executing the query "EXEC staging.InsertHeader '?', ?, ? OUTPUT " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


I'm pretty new to SSIS, so I have no idea what I have done wrong. I've looked at examples on the web, and matched what they have done? Can anyone please shed some light?

It works when I use an expression in the following format and the results are output to the resultset:

"DECLARE @RowCount INT
EXEC staging.InsertHeader '"+ (DT_WSTR, 8) @[User::LastPartitionDateForSalesRetention] +"', @RowCount OUTPUT,"+ (DT_WSTR, 1) @[User::DataTransferSchemaID]+" SELECT @RowCount"

Is this an OK way to do things, or should I use parameters (if I can get it working). What is the correct way?
Thanks

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:14:10
where are you using above statement? which task?
Go to Top of Page

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-06-25 : 03:51:15
Hi visakh16

I'm using the statement in an execute SQL task.

Hearty head pats
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 14:29:51
whats the purpose of wrapping first ? within '. i dont think thats required
Go to Top of Page

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-06-26 : 07:10:41
I wrapped it in '' because the value was a varchar/date. So I thought it would implicitly try to cast to an INT:

EXEC staging.InsertHeader '20090101',1,2

Hearty head pats
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:03:01
is it working after you removed quotes?
Go to Top of Page

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-07-13 : 05:29:14
Hey


Sorry for not replying sooner. But alas, no it didn't work when I removed the quotes. But no matter, I gave up on that and approached it using the expression method as depicted in my original post. But thanks for your help all the same.



Hearty head pats
Go to Top of Page
   

- Advertisement -