September 10, 2013 at 8:37 am
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,
September 10, 2013 at 10:15 pm
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)
September 11, 2013 at 12:57 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 11, 2013 at 7:01 am
Thanks for your responses.
September 16, 2013 at 3:52 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply