Michael, thank you for all your research. We had this exact problem several month ago while converting from DTS to SSIS. The stored procedure, which was the data source, ran for 1 hour (which was OK under the circumstances), however, as you can imagine, we couldn't have it run 3 or 5 times. That would be unacceptable. We ended up cheating by temporary replacing the real stored procedure with a fake one, which did nothing but return a 1-row result set in a required format. This allowed us to build our SSIS package and do the necessary mapping. After the package was built and deployed, we replaced the "fake" stored procedure with a real one. Of course this method has a lot of downsides, for example any modifications to the source procedure, would require performing the same trick again, you couldn't do this if the procedure were used by a live application, etc. Thank you for the article! I have some new tricks under my belt now :)

Hi,This was a valuable post and reply - I didn't know about the temp table and table variable problems and the two workarounds were both good ways to handle the problem depending on the circumstances. I'm on Christmas Holiday at the moment but when I go back to work I'll be starting on a set of new SSIS packages where I will probably be looking to use a number of stored procedures so this has given me advance warning of problems I might get and workarounds so they are probably going to save me a lot of headaches and grief. So thanks for your article and comment - they are really appreciated,Bill Ede

I supposed this is a dumb newbie question but I have to ask: Why use a stored procedure for such a simple select? Wouldn't a view be better and avoid the problem in the first place? I think some people use them for security reasons but I haven't figured out why permissions on the view does not suffice. Excuse my tangent!

magarity (12/28/2008)I supposed this is a dumb newbie question but I have to ask: Why use a stored procedure for such a simple select? Wouldn't a view be better and avoid the problem in the first place? I think some people use them for security reasons but I haven't figured out why permissions on the view does not suffice. Excuse my tangent!

First of all the article was simply illustrating a useful technique. Secondly, using stored procedures as data sources has it's own merits. For example, let's say the underlying database schema has changed and some column got re-named. If you were using a SELECT statement as a data source, you would have to re-build and re-deploy your SSIS, which is a bit of a pain. With a stored procedure as a data source, you would simply have to modify the proc, thus likely avoiding having to make any SSIS changes. Similar benefits could be accomplished by using a view, but if the logic is complex and requires several intermediate steps, the view may not be good enough and in this case you would have to use a stored procedure.

Excellent article. Although this is not a situation I have encountered yet in SSIS, it is a good workaround to be aware of.

Now that I have used SSIS for many ETL processes, I too see the value in placing some of the logic in stored procs. Especially logic which may require minor tweaks from time to time. I find it much easier to update the sproc for these small changes, than to load up the dtsx and redeploy the SSIS package.

Of course, the downside is that you're splitting up the business logic and making the process just a bit harder to understand. It's not an approach I follow blindly - it's just another tool in the bag that I use when I think it makes sense.