Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Dataflow source, inline query or SP


SSIS Dataflow source, inline query or SP

Author
Message
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2950 Visits: 2629
I realized I have an inconsistent philosophy. When working in Reporting Services I always use stored procedures for datasets. However, when working in SSIS, particularly in the Data Flow tab, I usually use embedded queries as my data source rather than a stored procedure. This also appears to be quite common in presentations at events like SQL Saturday, and in blogs. I don't really have any reason to use embedded queries as a data source in SSIS other than it is the habit I developed. As you all konw there are a lot of good reasons to use stored procedures in developing almost anything. So my question to you is, what is your opinion on using embedded code vs. stored procedure in SSIS as a data source, particularly in a data flow?
Thanks,
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3561 Visits: 3094
Personally, whenever I can I use stored procedures in whatever application I am writing.

However, when you are extracting data from a vendor application, you may not be allowed to make any DDL changes to the source database. So, you may end up needing to using sql code in the data source.

Having said that, you may be able to use another database to create these sprocs in. This database would be on the same server as the source database and all of the objects it referenced would be in the source database (either by including the database name in the query or by creating synonyms pointing to the tables)



Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8264 Visits: 19439
I can see why they would embed the selection SQL at demos and presentations - keeps everything in one place & helps things flow more easily.

But in all the places I have worked which use SSIS a lot, using procs as sources has been the de facto standard.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2950 Visits: 2629
Thanks for your responses.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
If you are only reading data I would definitely use sprocs or views;

If you are inserting, deleting or updating data then it is a bit more complicated. Where possible I would still use a sproc; the main reasons being that you get an opportunity for code-reuse and you can annotate the code more easily and you can edit the code without having to recompile and deploy the package. You also maintain database dependencies within the database which can help with query tuning and other DBA tasks such as replication.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search