SSIS error using ? parameters - SQL Server Forums

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?

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

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:03:01

is it working after you removed quotes?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources