Solved

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

Posted on 2013-11-11
16
293 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
  • 8
  • 8
16 Comments
 
LVL 26

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 26

Expert Comment

by:Zberteoc
ID: 39641333
I think you should give an example here because i don't really understand what you mean.
0
 
LVL 1

Author Comment

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

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 26

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

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 26

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 26

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 26

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now