Solved

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

Posted on 2013-11-11
16
301 Views
Last Modified: 2016-02-11
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
Comment
Question by:Alaska Cowboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39639800
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39640542
>>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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39641333
I think you should give an example here because i don't really understand what you mean.
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39641634
Here's the pic of the 4000 char limitation
Expression 4000 char limit
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39641678
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39641715
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39642234
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39642374
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39642455
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39642541
>>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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39642618
"- 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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39642656
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39642766
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39642792
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
 
LVL 27

Accepted Solution

by:
Zberteoc earned 250 total points
ID: 39642803
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39642821
good one ! I am doing various searches but always good for personal attention !

Thx.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question