SSIS User Defined Data Type (Alias Data Types) and OLE DB Command validation

• Alex Yue

  Grasshopper

  Points: 23

  More actions

  September 15, 2010 at 8:33 am

  #387790

  Hello everyone,

  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.

  Thanks!

• Koen Verbeeck

  SSC Guru

  Points: 259197

  More actions

  September 16, 2010 at 7:31 am

  #1222179

  Totally off-topic:

  why are you calling a stored procedure per row? Isn't it more effective to call the stored procedure on a set?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP