Link to home
Start Free TrialLog in
Avatar of Alaska Cowboy
Alaska CowboyFlag for United States of America

asked on

SSIS, How to use a stored procedure as the source sql

My basic problem is that an expression can't hold more than 4000 chars, so I have to look at alternatives. I ended up saying " select * from [such and such table(s)]" (so way less than 4000 chars), but now that's not going to work. I need to build the Sql statement using some case statements, "IsNull", "IsDate", etc., so I have to specify all columns, making it > 4000 chars.

So the solution I am pursuing is to write a SP (per this post) to pull back the data, thus avoiding the 4000 char limit. Ok, I can figure out how to write the SP (my first sql server SP in 10 years, having gone to the dark side [Oracle] after that).

But my question is, what are the steps ? and how does "OLE DB Source Editor" recognize what columns are being pulled, such that these columns are available for the mapping ?

My first attempt at this had me creating two variables to avoid the 4000 char limit. I then smashed these two variables together in a script task. But then when I got to the "OLE DB Source Editor", I chose my variable, but since it's not available until runtime, SSIS didn't know what columns are available, and so I was out of luck, and went to "select * ".

and assuming this works, what are my steps to call the thing ? and I assume the SP resides outside of SSIS as a separate component.
Avatar of Zberteoc
Zberteoc
Flag of Canada image

What 4000 character limit are you talking about?

If you refer to nvarchar then you can use nvarchar(max), which goes up to 2GB per value. a SP will not deal with this issue any other way, it is all about the type of the columns you return, which it is the same if you use SELECT or an SP.
Avatar of Alaska Cowboy

ASKER

>>My basic problem is that an expression can't hold more than 4000 chars
- I tried to create a variable for my sql statement, but doing so using an Expression croaks if the sql text itself is > 4000 chars.
I think you should give an example here because i don't really understand what you mean.
Here's the pic of the 4000 char limitation
User generated image
That is a UI limitation. The only thing I can think of is to go in the settings and change them. In the main menu n Management Studio click on Tools > Options  and try there. I am not sure for Query builder where they are. I never use query builder.
Zberteoc,

from all the posts I've seen, the 4000 char limit for an expression can't be changed, e.g., here. So it doesn't seem like it's something I can fiddle with.

As the link says, it's not a string that is limited to 4000 char, it's the expression component (all of this seems to cause much heartburn in the community, also described in the link).
Ok, is a SSIS and not Management Studio UI limitation. I think what that means is that one of the columns that you return from your SELECT is bigger than 4000 characters. I am not sure why that limitation or where you can change it in SSIS environment.

As a workaround yous should identify which column(s) is(are) as big and then apply a LEFT(4000) if that works for you.
Zberteoc, I appreciate your suggestions, but the 4000 char limit is real and I can't get around it. My query text is > 4000.

So given that, my actual question is this:
The solution I am pursuing is to write a SP to pull back the data, thus avoiding the 4000 char limit. Ok, I can figure out how to write the SP (my first sql server SP in 10 years, having gone to the dark side [Oracle] after that).

But my question is, what are the steps ? and how does "OLE DB Source Editor" recognize what columns are being pulled, such that these columns are available for the mapping ?

Thanks.
The link you posted says that it is not the query text that has the limit and it is proved.

However, in order to use a procedure you would have to just replace your SELECT with the procedure call like

EXEC procedure_name

Another solution would be to build a view instead of the procedure and use that like

select * from view

or

select col list from view

if you don't get into the 4000 chars limitation again.
>>The link you posted says that it is not the query text that has the limit and it is proved
- maybe we're just not connecting, but it is the query text - when I paste in a query text > 4000 into the expression for a variable, SSIS chokes . . .

>>order to use a procedure you would have to just replace your SELECT with the procedure call like "EXEC procedure_name"
- ok, but is this in the variable ? or in the OLE DB Source window ? And this is my sticking point - if I do this, how does the OLE DB source window recognize the columns to be mapped ? They won't show up in the Sql section of the OLE DB Source editor, because the columns are available until runtime (as I understand it)

>>Another solution would be to build a view instead of the procedure and use that like "
select * from view "
- I started down that path but there are parts of the sql that need to be formed in the expression, so that didn't work out
"- I started down that path but there are parts of the sql that need to be formed in the expression, so that didn't work out"

Explain, and again, examples are useful not just statements.
For example, the schema for the Oracle table changes from one environment to another, so "PHAR_DEV", "PHAR_TEST", "PHAR_PROD". Also, components of the where clause.

so my expression is
"select [this and that]
from " + @uservar::schema_var + ".PHAR_DETAILS
WHERE load_dt > " + @uservar:last_load_dt
   and SOURCE_CD = '123' "
I think we are talking about a different thing now. Initially you were having problems with the size limit and now is a SSIS issue in regards to how you build your queries. It is very confusing.

However, for that query you might want to use:

"select [this and that]
from " + @uservar::schema_var + ".PHAR_DETAILS
WHERE load_dt > '" + @uservar:last_load_dt +"'
   and SOURCE_CD = '123' "

you missed +" after @uservar:last_load_dt and also you want that value enclosed in single quotes because is a date.
thanks for sticking with this, but we're not getting anywhere.  I am not able to use an expression to build the query because of the 4000 char limit.

The solution I am pursuing is to write a SP to pull back the data, thus avoiding the 4000 char limit. Ok, I can figure out how to write the SP (my first sql server SP in 10 years, having gone to the dark side [Oracle] after that).

But my question is, what are the steps ? and how does "OLE DB Source Editor" recognize what columns are being pulled, such that these columns are available for the mapping ?

Can you help with this ?
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
good one ! I am doing various searches but always good for personal attention !

Thx.