CODEWITHSUNDEEP
sql-serverssismetadataoledbetl
Dillon P
Dillon P

Reputation: 91

SSIS OLE DB Command metadata error because of DB trigger

I know this may seem like a commonly asked question, but I have a unique situation which I can't find an answer to.

I have a simple SSIS package which I want to use to update a table from an Excel spreadsheet. I'll do this using an OLE DB command task which executes a stored procedure. However, it's not retrieving any column names to map. The error is similar to this:

The metadata could not be determined because statement 'insert into #TempTable ... ' uses a temp table.'

I understand why it's returning this error, and know the typical workarounds. However, the error is not coming from the stored procedure that the package calls. That proc doesn't use temp tables. It's a result of the update firing off a database trigger that calls another proc, which DOES use temp tables.

Unfortunately, the triggered proc causing the error is from the third party application I'm developing for, and cannot be modified.

Does anyone know of a solution that doesn't involve adding code to the offending proc?

Thanks!

Upvotes: 0

Views: 450

Answers (3)

Dillon P
Dillon P

Reputation: 91

The staging table approach occurred to me shortly after I posted this, and that's the route I went with. While not ideal for such a simple package, it does work. A cursor won't even be necessary...for this package, I can simply update the appropriate table from the staging table, no need for a stored proc. Thanks to you both!

Upvotes: 0

Wendy
Wendy

Reputation: 660

Option 1, load Excel spreadsheet to a staging table, then use 'Execute SQL Task' to update your final table. Option 2, create Script task, read from spreadsheet and update your final table, thus avoid going through data flow for column mapping.

Upvotes: 1

billinkc
billinkc

Reputation: 61259

Your best bet would be to stage the updates to a dedicated table (OLE DB Destination instead) and then have an Execute SQL Task as a successor event.

Within the Execute SQL Task, set up a cursor to shred the staging table and then call your stored procedure.

DECLARE CSR CURSOR
READ_ONLY
FOR SELECT Col1, Col2
FROM staging;

DECLARE @Col1 nvarchar(100)
,   @Col2 nvarchar(100);
OPEN CSR;

FETCH NEXT FROM CSR INTO @Col1, @Col2;
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        EXECUTE dbo.MyProcedure @Col1, @Col2;
    END
    FETCH NEXT FROM CSR INTO @Col1, @Col2;
END

CLOSE CSR;
DEALLOCATE CSR;

Upvotes: 1

Related Questions