Wednesday, March 21, 2012

running stored proc with parameter

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

Cherrie,

Not sure I am understanding the details of your problem (may be if you post the error you are geeting...); but here is my shot:

Assuming the parameter inside of your procedure is table1; then the SQLStatement of the SQL task should be something like:

EXEC sp_udt_keymaint @.table1=?

The in the parameter mapping page of the SQL task you have to map the SSIS variable to the SP parameter.

Rafael Salas

|||The syntax of the SQLStatement depends upon the connection type used. You may want to refer to BOL for the syntax of the each connection type.|||

Your best bet is to use a .NET provider. Judging by your procedure name, you're running a stored procedure on SQL Server. Then, set your SQLStatement to the name of your stored proc (dbo.sp_udt_keymaint). Add the parameter (@.TableName, the @. symbol is required) in the Parameter Mappings tab and map the parameter to a user-defined variable.

HTH

No comments:

Post a Comment