Apologies in advance for the longish post…
We have the following environment:
1) SSIS 2012
2) Mixed SQL Server 2008R2 and 2012
We have instances of databases installed on both 2008R2 and 2012 – these databases contain independent data, but have the same DDL. We want to keep the DDL and SSIS packages the same across all environments.
We have come across a problem when it comes to the use of OLEDB datasources which use stored procedures containing one or more references to #temp tables.
In SS2012, when executing a proc which makes any use of temp tables, it’s easy (and necessary) to modify the OLEDB source to include the ‘WITH RESULT SETS’ option to define the columns and datatypes which the proc will return. I like that, but executing it on anything before SS2012 gives a syntax error, because ‘WITH RESULT SETS’ is new in SS2012.
The way that we have been getting round this issue is by using table variables inside the procs rather than temp tables. This works, but the performance of table variables is not so good when there are lots of rows to be processed, as here.
[A question for another day is why do (local) temp tables give a problem when table variables do not – they’re not that different.]
We have plans to upgrade everything to 2012, which will mean that we can resolve the issue as described above. But it will not be for a while and I need to find an interim fix.
(I was quite hopeful about this idea) I tried creating a physical ‘work’ table and having the proc truncate it, populate it and then select from it, but still the fact that I am INSERTing into temp tables earlier on in the proc still causes problems, even though no result sets are being returned until the physical table at the very end of the proc.
I have tried to think of a simple way of handling the differences in the SSIS package which calls the proc – perhaps by passing in the OLEDB source SQL command string as a parameter – but it’s a level of complexity we don’t want to introduce. So I haven’t tried this.
I do have another idea which would definitely work:
1) Add an ExecuteSQL task to truncate/populate a physical work table.
2) Select from this work table in the OLEDB source.
This does, however, come with some negatives:
a) For every proc which needs to use temp tables, we need to create additional objects (a physical work table and a proc which truncates and populates the work table)
b) We need to introduce extra complexity into the SSIS package to call the ‘populate’ proc before selecting from the work table.
c) We need to be careful that no more than one instance of the ‘populate’ proc can ever be executing in the same db.
Does anyone have any ideas about how we can resolve this? Thanks in advance.
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
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.