I've been having an issue with trying to run my SSIS package on a server, and it seems to be failing on the OLE DB Command step. What we have in our SQL 2005 DB, is a User-Defined Data Type (base type char(7)) and the OLE DB Command is supposed to
call a proc that passes in a value of this data type.
ie: CREATE PROCEDURE myProcedure ( @passedInFromSSIS MY_DATATYPE ) AS ....
In my SSIS package, I have the type defined as DT_STR with a length of 7.
Now, when I run the package locally (via Visual Studio), the process runs with success. However, once the package is deployed on a server and run from an application (note: it is run under a different user), the process fails on a validation step with
a "Invalid Parameter Number" error.
Now, if I change the input parameter in my proc to the base type of the user-defined data type, the process works again.
Has anybody run into a similar issue or know what may be causing this issue? I first suspected perhaps I needed to grant permissions on the user-defined data type (since I was able to run it under my security context, but not under the application's),
however noticed that there isn't security tied to the types.
Any other thoughts? Please let me know if you need further explanation.
View Complete Post