Alaska Cowboy
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.
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.
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 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.
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.
ASKER
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).
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.
As a workaround yous should identify which column(s) is(are) as big and then apply a LEFT(4000) if that works for you.
ASKER
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.
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.
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.
ASKER
>>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
- 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.
Explain, and again, examples are useful not just statements.
ASKER
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' "
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.
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.
ASKER
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 ?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
good one ! I am doing various searches but always good for personal attention !
Thx.
Thx.
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.