Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Dataflow source, inline query or SP Expand / Collapse
Author
Message
Posted Tuesday, September 10, 2013 8:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
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,
Post #1493216
Posted Tuesday, September 10, 2013 10:15 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:05 PM
Points: 3,044, Visits: 2,649
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)



Post #1493436
Posted Wednesday, September 11, 2013 12:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 5,189, Visits: 12,056
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1493468
Posted Wednesday, September 11, 2013 7:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
Thanks for your responses.
Post #1493650
Posted Monday, September 16, 2013 3:52 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.

Post #1495311
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse