Use temporal table on SSIS using OLE DB source editor

2
votes

I want to execute SP who have temporal table and querys with 600 lines of code. So I insert SP code into variable and then I call OLEDB Source editor

temp table:

IF OBJECT_ID('tempdb..##TGJAE' ) IS NOT NULL DROP TABLE ##TGJAE 

SELECT
   VOUCHER,
   DATAAREAID,
   TAXITEMGROUP,
   TAXBASEAMOUNT,
   SOURCERECID
INTO ##TGJAE
FROM TAXTRANS

but when I try to save I get issue:

TITLE: Microsoft Visual Studio

Exception from HRESULT: 0xC020204A Error at Flow [Query [16]]: SSIS Error Code 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 VT.ACCOUNTNUM as [Código de Proveedor], "RFC" = CASE when V.RFC_MX = '' then v.VATNUM else v' uses a temp table.".

Error at Flow [Query [16]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

enter image description here

Any one know what happen there? Regards

3
sqlsql-serverssis
Why a global temp? Why not just a local? I haven't tried, but I can only imagine using a global temp will cause some serious issues with SSIS. - Jacob H
I change it to local table and still getting same error @JacobH - Ledwing

3 Answers

1
votes

Somewhat of a guess here, but you may need to explicitly define your temp table to pass the SSIS query validation.

Try using a full create statement instead of SELECT... INTO:

CREATE TABLE ##TGJAE (
     VOUCHER datatype,
     DATAAREAID datatype,
     TAXITEMGROUP datatype,
     TAXBASEAMOUNT datatype,
     SOURCERECID datatype);

INSERT INTO ##TGJAE
     SELECT VOUCHER,
            DATAAREAID,
            TAXITEMGROUP,
            TAXBASEAMOUNT,
            SOURCERECID
     FROM TAXTRANS

SSIS is finicky with metadata, and using SELECT... INTO to create a table means that SSIS doesn't know the metadata of the table columns until it is created. ie. Metadata can't be determined.

1
votes

There's some confusion here:

  1. temporal tables and temporary tables are completely different things. The # means you are using a temp table. It is highly unlikely that there are any performance improvements in using a temporary table, especially in this case where you have to load it first anyway!

  2. The OLE DB source component needs to output a data set. The SQL you have written does not output a dataset at all which is why you get an error. Your SQL loads data into a temporary table but doesn't output data

Based on your example, you just need to change your code to this:

SELECT
   VOUCHER,
   DATAAREAID,
   TAXITEMGROUP,
   TAXBASEAMOUNT,
   SOURCERECID
FROM TAXTRANS;
0
votes

What you need to do is split this into two operations. Have your drop and create statement along with table population in a 'Execute SQL Task'. Use select statement in the DFT. Make sure 'Delay Validation' is set to true for your DFT in order to run. Also ensure you have a physical table created and not a global temp.

I would also advice against dropping every time and rather use a Truncate Statement.