February 24, 2010 at 9:41 am
Any ideas on this one people?
I'm running an Execute SQL Task. It should be getting a variable passed in (set as a string). It then inserts a record into a table within the Execute SQL with one field stating "Please raise a request for user" + @Variable.
The @Variable is mapped under parameter mappings with the type also set to string (ado.net - i've tried ole db and that gives me a whole new bag of errors).
It fails with "SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size."
It is NOT to do with the t-sql or the connection at all - If I replace the @Variable in the code and it STILL fails. It only goes through if I remove the parameter mapping. So it is something on this parameter mapping screen that it hates. I have never had an issue elsewhere with dates or int data types - but using this string or stringfixedwidth....BANG.
It is KILLING me.
Thanks for any help.
February 24, 2010 at 9:47 am
If it helps (misery loves company): I've never been more frustrated with one single program in my life more than SSIS.
Anyway, when I've had issues with parameters as you're having, I give up after a bit and switch to building what I need in an expression...either directly on the object or using a computed variable. Or, is that what you're currently doing and the issue remains?
Kurt
February 24, 2010 at 9:58 am
klininger (2/24/2010)
If it helps (misery loves company): I've never been more frustrated with one single program in my life more than SSIS.--
Kurt
Wow, that's a big statement. I think the older versions of Crystal Reports take the frustration award from my point of view:-) At least in SSIS when you find a solution you can usually understand why it works.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 24, 2010 at 10:08 am
IF you find a solution. Sadly many of the "useful error messages" I've come across in SSIS have only been solved by service packs. Not so much an error I could fault find, more so a bug - so you never know which flavour of hairpull you are dealing with!
I am not using an expression on this as its quite a hefty sql statement (i've just dropped in the section referencing the variable). I have no worries using expressions, but this....THIS. URGH. Killing me.
February 24, 2010 at 11:28 am
As klininger suggests using expressions is always a good approach to solve problems in SSIS... Is Your SQL less than 4000 char's ..if so you can achieve that using expressions..
February 24, 2010 at 11:31 am
Why don't you post the EXEC SQL task SQL statement and we might be able to help. I can say that it doesn't do what I would call complex substitution, all the mapping are VERY simple, so if you have some code that does some work and then does an insert, you aren't going to do it in the EXEC SQL task along.
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply