Using temp tables (select INTO) and dtsx packages

Naomi 7,361 Reputation points
2022-02-10T16:46:59.737+00:00

Hi everybody,

I'm getting plagued by this error again and again and I'm trying to figure out if there is some sort of nice solution which would not require touching the package and yet would allow me to use temp table created on the fly.

Say, I have a stored procedure that returns result using

select col1 + 'separator' + col2 + ... as FinalExtract from some very complex query

There are lots and lots of columns concatenated and in addition most of them are wrapped into a scalar UDF which removes bad characters (so these queries take hours to finally return the results).

I'm attempting to optimize the performanace and I'm using select my columns into #tempResult, select distinct something into #tempResult1 from #tempResult and then my final output. I tried adding select cast(... as varchar(500)) as Extract from #tempResult1, but it doesn't help. I don't want to change the package otherwise I would use WITH RESULTSETS clause (probably).

This is the error text:

DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'SELECT
column names
,' in procedure 'procedure name' uses a temp table.".

Any clever solutions here besides trying to switch back to using cte instead of temp table?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,583 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,446 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 32,401 Reputation points
    2022-02-11T07:34:16.743+00:00

    Hi @Naomi ,

    I'm afraid that this is the cleverest way to resolve the issue and you need to edit the other packages manually.

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  2. Naomi 7,361 Reputation points
    2022-02-10T18:51:39.687+00:00

    I decided to bite the bullet and change the package to use WITH RESULT SETS () option. Testing it now.