Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSIS Package not wanting to fetch metadata of temporary table

I have an SSIS Package, which contains multiple flows.

Each flow is responsible for creating a "staging" table, which gets filled up after creation. These tables are global temporary tables.

I added 1 extra flow (I did not make the package) which does exactly as stated above, for another table. However, for some reason, the package fails intermittently on this flow, while it is exactly the same as others, besides some table names.

The error that keeps popping up:

Update - Insert Data Flow:Error: 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: "Unspecified error". 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 * from '##TmpMcsConfigurationDeviceHistory86B34BFD041A430E84CCACE78DA336A1'' uses a temp table.".

Creation expression:

"CREATE TABLE " + @[User::TmpMcsConfigurationDeviceHistory]  + " ([RecId] [bigint] NULL,[DataAreaID] [nvarchar](4) COLLATE database_default NULL,[Asset] [bigint] NULL,[Code] [nvarchar](255) COLLATE database_default NULL,[Configuration] [bigint],[StartdateTime] [datetime] NULL,[EndDateTime] [datetime] NULL) 

"

Parsed expression (=evaluated):

CREATE TABLE ##TmpMcsConfigurationDeviceHistory764E56F088DC475C9CC747CC82B9E388 ([RecId] [bigint] NULL,[DataAreaID] [nvarchar](4) COLLATE database_default NULL,[Asset] [bigint] NULL,[Code] [nvarchar](255) COLLATE database_default NULL,[Configuration] [bigint],[StartdateTime] [datetime] NULL,[EndDateTime] [datetime] NULL) 
like image 479
Mortana Avatar asked Aug 20 '13 23:08

Mortana


People also ask

Can SSIS use temp tables?

If you absolutely must use temp tables in your SSIS package, turn on the Delay Validation setting on the data flow tasks that use temp tables.

Can we return temporary table in stored procedure?

In the SP, use INSERT to load data into a temp table following the same naming convention, e.g. #SPName_Output which is assumed to exist. You can test for its existence and return an error if it does not.


2 Answers

Using WITH RESULT SETS to explicitly define the metadata will allow SSIS to skip the sp_describe_first_result_set step and use the metadata that you define. The upside is that you can use this to get SSIS to execute SQL that contains a temporary table (for me, that performance helped a lot); the downside is, you have to manually maintain and update this if anything changes.

Query sample (stored procedure:)

    EXEC ('dbo.MyStoredProcedure')     WITH RESULT SETS       (         (             MyIntegerColumn INT NOT NULL,             MyTextColumn VARCHAR(50) NULL,             MyOtherColumn BIT NULL         )       ) 

Query sample (simple SQL:)

EXEC ('     CREATE TABLE #a        (         MyIntegerColumn INT NOT NULL,         MyTextColumn VARCHAR(50) NULL,         MyOtherColumn BIT NULL       )      INSERT INTO #a        (         MyIntegerColumn,         MyTextColumn,         MyOtherColumn       )     SELECT          1 AS MyIntegerColumn,         ''x'' AS MyTextColumn,         0 AS MyOtherColumn      SELECT MyIntegerColumn, MyTextColumn, MyOtherColumn     FROM #a')  WITH RESULT SETS     (         (             MyIntegerColumn INT NOT NULL            ,MyTextColumn VARCHAR(50) NULL            ,MyOtherColumn BIT NULL         )     ) 
like image 150
AHiggins Avatar answered Sep 27 '22 19:09

AHiggins


Another option (kind of a hack, but it works and doesn't require you to change your use of global temp tables) is to use a SET FMTONLY ON command in front of your actual query to send a fake "First result set" to SSIS with your correct column structure. So you can do something like

SET FMTONLY ON select 0 as a, 1 as b, 'test' as C, GETDATE() as D SET FMTONLY OFF  select a, b, c, d from ##TempTable 

When SSIS runs sp_describe_first_result_set, it will return the metadata and column names of your FMTONLY command, and won't complain about not being able to determine the metadata of your temp table because it won't even try.

like image 43
Kyle Hale Avatar answered Sep 27 '22 17:09

Kyle Hale