[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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.
0
Alaska Cowboy
Asked:
Alaska Cowboy
  • 8
  • 8
1 Solution
 
ZberteocCommented:
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.
0
 
Alaska CowboyAuthor Commented:
>>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.
0
 
ZberteocCommented:
I think you should give an example here because i don't really understand what you mean.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Alaska CowboyAuthor Commented:
Here's the pic of the 4000 char limitation
Expression 4000 char limit
0
 
ZberteocCommented:
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.
0
 
Alaska CowboyAuthor Commented:
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).
0
 
ZberteocCommented:
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.
0
 
Alaska CowboyAuthor Commented:
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.
0
 
ZberteocCommented:
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.
0
 
Alaska CowboyAuthor Commented:
>>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
0
 
ZberteocCommented:
"- 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.
0
 
Alaska CowboyAuthor Commented:
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' "
0
 
ZberteocCommented:
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.
0
 
Alaska CowboyAuthor Commented:
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 ?
0
 
ZberteocCommented:
Try this:

http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx

A simple search on google for: using stored procedure in ssis

gave me that :)
0
 
Alaska CowboyAuthor Commented:
good one ! I am doing various searches but always good for personal attention !

Thx.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now