2

I'm providing maintenance support for some SSIS packages. The packages have some data flow sources with complex embedded SQL scripts that need to be modified from time to time. I'm thinking about moving those SQL scripts into stored procedures and call them from SSIS, so that they are easier to modify, test, and deploy. I'm just wondering if there is any negative impact for the new approach. Can anyone give me a hint?

Improve this question

4 Answers 4

Reset to default
2

Yes there are issues with using stored procs as data sources (not in using them in Execute SQL tasks though in the control flow)

You might want to read this: http://www.jasonstrate.com/2011/01/31-days-of-ssis-no-more-procedures-2031/

Basically the problem is that SSIS cannot always figure out the result set and thus the columns from a stored proc. I personally have run into this if you write a stored proc that uses a temp table.

I don't know that I would go as far as the author of the article and not use procs at all, but be careful that you are not trying to do too much with them and if you have to do something complicated, do it in an execute sql task before the dataflow.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Woooooah! I would recommend reading the reading the link that he posted as the source. If your stored procedure is well written that you are not going to have these issues. There are certainly possible benefits to using a stored procedure in an OLEDB source. It's just like anything else in SQL everything has it's use and must be used corrrectly.
I agree that you can avoid most of the problems with better stored procs, but the OP did ask if there were problems using procs and there are. If you don't know you have to be careful, then you might not be. That's why I said I wouldn't go as far as the author did in saying never to use them.
Yeah I was just pointing out that this is much less alarmist about using sprocs and more informative.
Thanks for the information. I read the linked article from HLGEM's post and the linked articles from that link. I was not aware about those problems before. Now I'll be very careful in using SPs in my OLEDB sources. Thanks again for all your replies!
0

I can honestly see nothing but improvements. Stored procedures will offer better security, the possibility for better performance due to cached execution plans, and easier maintenance, like you pointed out.

Refactor away!

Improve this answer

4 Comments

Thanks! I'm going to do that!
A properly parametrized query is just as good for performance as a stored procedure - it will also be cached, its executing plan will also be reused - no benefit for stored procedure on this front, I'm afraid....
@marc_s That is correct - there is too much ambiguity in the question to determine if that is the case. So I will correct my answer to read possible performance benefits.
Thanks for the replies. I read the posts HLGEM pointed out and related posts. Most of the embedded scripts are selects with multiple joins (sometimes across different databases) and sometimes unions. There are no parameters or temp tables involved. When I click "Columns" in the source editor, I can see the columns. From what I read from the posts, I guess I can then use stored procedures safely. Are there any more concerns? I just want to be careful before I take the step.
0

You will not face issues using only simple stored procedures as data source. If procedure is using temp tables and CTE - there is no guarantee you will not face issues. Even when you can preview results in design time - you may get errors in a run time.

Improve this answer

Comments

0

My experience has been that trying to get a sproc to function as a data source is just not worth the headache. Maybe some simple sprocs are fine, and in some cases TVFs will work well instead, but if you need to do some complex operations there's no alternative to a sproc.

The best workaround I've found is to create an output table for each sproc you need to use in SSIS.

  1. Modify the sproc to truncate the new output table at start, and to write its output to this instead of (or in addition to) ending with a SELECT statement.
  2. Call the sproc with an Exec SQL task before your data flow.
  3. Have your data flow read from the output table - a much simpler task.
  4. If you want to save space, truncate the output table again with another Exec SQL. I prefer to leave it, as it lets me examine the data later and lets me rerun the output data flow if it fails without calling the sproc again.

This is certainly less elegant than reading directly from a sproc's output, but it works. FWIW, this pattern follows the philosophy (obligate in Oracle) that a sproc should not try to be a parameterized view.

Of course, all this assumes that you have privs to adjust the sproc in question. If necessary, you could write a new wrapper sproc which truncates the output table, then calls the old sproc and redirects its output to the new table.

Improve this answer

2 Comments

Check out the call "WITH RESULT SETS". It allows you to execute a stored procedure while declaring the metadata of the expected results and allows SSIS to run.
@BilliD: Very nice! That should be much easier, thanks for the tip!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.