none
Parametrized query from variable: query too big!!! RRS feed

  • Question

  • I've been having an issue when trying to run a parametrized query using a String variable to store my query in. I am a newcomer to SSIS and as far as I can see, I've reached a dead end. Any help will be greatly appreciated. Here's my issue:

    I have to run a parametrized query to extract some data from an Oracle OLE DB source. However, since the source can't parse for parameters or something like that, I have to provide the query through a variable, and link the parameters with another variable. Everything works well until the argument gets big (the argument in question is actually a list of keys used in my where clause). When that happens, the expression fails to evaluate, aparently because it's now too big for the variable it's stored in.

    What I'd like to know is if there's any other way to store/run that query, in which parsing for parameters and the size of the query won't be such impeding problems.

    Thanks in advance,
    Eduardo.
    Thursday, May 28, 2009 5:27 PM

Answers

  • Your likely only option is to construct the string inside a Script Task.

    To be clear, the 4000 character limit is on expressions, not strings.  When you build your string through an expression, you're running into that limit.  Constructing it inside a Script Task will let you make it as long as you like.
    Todd McDermid's Blog
    Thursday, May 28, 2009 6:28 PM
    Moderator

All replies

  • Your likely only option is to construct the string inside a Script Task.

    To be clear, the 4000 character limit is on expressions, not strings.  When you build your string through an expression, you're running into that limit.  Constructing it inside a Script Task will let you make it as long as you like.
    Todd McDermid's Blog
    Thursday, May 28, 2009 6:28 PM
    Moderator
  • Top shelf solution, Todd! It worked pretty much as well as it could have! =)
    Thursday, May 28, 2009 7:28 PM