What component to use for Calling View

In SSIS 2005 what component can I use inside my Data Flow task to call my View and use it as a datasource I know I've used an Execute T-SQL Task but I don't know what component to use inside an actual task itself. What I'm trying to do is in my Data Flow task, instead of using 3 OLE DB Source components, I want to actually call 3 stored procs and use those 3 components as the data sources to merge on using the merge and sort components later on

Answer this question

What component to use for Calling View

JStorm

You have to embed your SQL somewhere if you want to get data out of somewhere. Even if you pick a table from the dropdown you are still effectively "embedding SQL" because it needs to execute a SQL statement to get data out of the system.

I'm not quite sure why you have an issue with this.

-Jamie
trumpy81
Geez, that sucks! thanks for the warning! I don't need any more errors to troubleshoot in SSIS. But what about calling a stored proc or view from the SQL option It makes me squirm at the thought of embedding straight SQL, I'd much rather use SQL but EXEC a stored proc since obfiously managing one central stored proc is the purpose and best way to code anything SQL wise!
Prabhu000
no, I meant just based on your article, what I gathered was it's bad to use any other option except for table in your OLE DB source.
Enrique0210
Jamie, I prefer to embed my SQL in SQL Server, in stored procs. If I'm embedding SQL in multiple projects, that means I have to tweak multiple projects's SQL since SSIS is storing the SQL, not SQL Server. I'd rather maintain any SQL on the SQL Server then in SSIS reference stored procs so I don't have the overhead of tweaking many projects...if they are all using the same stored proce in some causes. I want to take advantage of reuse that stored procs are meant for, and do not want to store SQL straight into SSIS....I want to call my stored procs from within SSIS for reuse and maintenance purpose which I figure is obvious.
NewHacker
Thanks, that will do fine, as long as a View is ok. Thanks!
Natalie Casey

Eh

You have 3 options.

1) Select a table or view from the dropdown

2) Write a SQL statement

3) Use a SQL statement stored in a variable.

My article said don't use #1.

If you want to pull some data out of a view then write a SQL statement:

SELECT <column_list> FROM <view_name>

-Jamie
Mix from Latvia

Fair enough. Why do you think you cannot execute stored procs from SSIS

-Jamie
sposs

Favor,

I recommend you DON'T use the dropdown. Here's why: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx

-Jamie
Nick Porter
sorry, I'm scattering around trying to figure out SSIS. I want to call a View or Stored Proc. You're right, I forgot you can use the dropdown to specify not only tables but a stored proc or view in an OLE DB Source. I'm having withdraws from coffee and a headache!
Pirox
hehe, sorry to drag this out :). But the drop down allows you to reference SQL statements or Views. Is there another way to do this not using a drop down in the OLE DB component that I'm not seeing
Nosam Neb

My article said don't use the dropdown. It didn't imply anything else, or wasn't meant to anyway.

-Jamie
lode

You've lost me. At the start of the above post you say you want to use a view. At the end of the post you say stored proc. Which is it

Either way, a view or a stored proc can be used inside an OLE DB Source component.

-Jamie
What component to use for Calling View

Answer this question