blogs.conchango.com

Doesn't work !!!

Jamie, I found this post out on the MSDN SSIS forum; exactly what I was looking for, and it's executing perfectly.  You've saved me a lot of headaches from trying to do a workaround!

Forgot to add, once you have the mappings done, you do not need to keep creating the temp table in Management Studio first.  Even though you'll see the error symbol on your OLEDB destination, your package will still execute (I'm guessing that's what the delay validation is).

Jamie, I was trying to do this again in a different package.  I mocked the successful package that I have, but my new one is not working.  When I execute the package, I can see in the data flow that it is trying to validate the temp table, which is the last step in the flow.  I have double-checked that I have RetainSameConnection=TRUE and DelayValidation=TRUE.  

My package creates a temp table successfully in an Exec SQL Task.  The next step is a data flow, which retrieves records from a staging table, does an unpivot, some simple converting of fields, two lookups, and then unsuccessfully inserts into my temp table.  

My error I get is: [OLE DB Destination [16936]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "sqldev\bidev.tempdb" failed with error code 0xC001A004.  

Do you know what could be wrong?

Jamie.

Your approach did solve my problem to some extent. But my situation is somewhat like this:

I have an existing DTS package which I am migrating to SSIS. We have a Dynamic Properties task which we run before development/debugging the DTS package and it sets the Source and Detsination Object to existing database tables.

In the actual workflow, just before the Data Pump task we have another dynamic properties task which sets the Source and Detsination Object to temporay tables. (##xyz).

This way if one wants to have a look at the column mapping at design mode, all he needs to do is run the first dynamic properties task.

I guess your approach will not work in the above scenario. Any pointers or suggestions will be of immense help.

Jamie -

    Thank you for posting this article!  I am having an issue with getting this to work - probably my inexperience with temp tables in SQL Server!

    I create a global temp table in SS 2005 using something like:

    CREATE ##SomeTable (SomeValue int)

    I then created the execute SQL task to create the temp table and fill it:

    CREATE #SomeTable (SomeValue int)

    INSERT INTO #SomeTable (SomeValue) VALUES (1)

    When I try to create the OLE DB Connection, I try selecting from the temp table such as:

    SELECT * FROM #SomeTable

    I have DelayValidation=True and DelayValidation=True but I cannot get the task to save during design time.

    Do you have any suggestions on what I am doing incorrectly?

    Thanks!

Hi Jamie

I've got a strange one - when I execute a stored procedure from an OLE DB Command transformation, where the sp takes a parameter and RetainSameConnection=TRUE and DelayValidation=TRUE are set, I get the error

"Syntax error, permission violation, or other nonspecific error"

If I take out the param or set RetainSameConnection=FALSE on the connection, all is fine again?

I wondered if you've come across this?

Cheers,

Chris

Is there a way to see the timestamp (date and time) for when the temporary table is created?

Thanks

Vince

Hi Jamie,

Fantastic blog. It's as good as the books I bought to learn SSIS.

I've found this temp table solution works perfectly for the scenario you've described, where you're using the temp table to be consumed by an oledb SOURCE. For what I'm trying to do, in order to avoid a physical staging table, I need to write data to a temp table DESTINATION. I've found that I can't select my temp table in the destination the same way I can in a source. Is there something I'm missing?

I got the temp tables working for an oledb DESTINATION. The only way I could get it to work was to create the table in the destination editor by clicking the "NEW" button next to the select table dropdown. Then the table name [##stringmap] would be in the dropdown and I could move on to the "columns" screen.

Thanks again for the article.

How do I do this on sql2000 DTS package?  I am trying to insert an existing text file into a #temptable.  Creation of #temptable is precedent task to insert task.  The dts transformation errors with 'Invalid object name #temptable'. I tried with global ##temptable.  It errors with same message.

Thanks

I have a SP that created a temp table then select data into it and from there I do a select statement off the temp table to get my results.  Does anyone know how to get SSIS to view my SP?  I have tried a few different ways and can't get anything to work.

Try adding this before the stored proc or query:

SET FMTONLY OFF;

exec dbo.usp_SomeProcedure;

The designer turns this ON so as not to actually manipulate data or anything at design time.  Test it in a query window.  

SET FMTONLY ON

Select * From SomeTable

SET FMTONLY OFF

Select * From SomeTable

Just be careful where you use it, as it will actually run everything in your query instead of estimating the resultset.  

FYI, i found that here:

http://bisqlserver.blogspot.com/2007/10/using-stored-procedure-in-ssis-ole-db.html

Thanks!

Hi Jamie.

Thanks for the article. I am trying this out now and could use a little help. I want to populate the table from inside of a data flow where the records needed are the result of a merge and finally a conditional split. What is the best way to do this? I assume it would have to be with an OLE DB Command, but I do not know how to setup the output to go into the temp table.

Overall problem:

Basically, I have a data validation data flow that excludes records based on various criteria and I want the end result of my validation data flow to be the input to another data flow process that transforms the data some more (can add details here if needed) and inserts the records into a database.

Any help would be appreciated.

Thanks, Mike

Thanks Jamie.

I got it to work using an OLE DB Destination with a SQL Statement. Sometimes, I am a little slow... :-)

Mike

Thanks Jamie,

I was trying to get this for a long time.

Any ways my package is like this,

I get a Postional file from which i have to generate Multiple flat files based on a particular field.

to do this i am trying to populate the data from flat file into a ##table and use For Each ADO enumerator to loop through the field,which i want.

here the package is not failing,but inside the temp table only the last row is persisted and all other rows are not existent.

any solutions for this will be appreciated.

cheers

srikanth katte

Hi Jamie,

I've got a problem with retainsameconnection property of dynamic OleDB connection. I've set up a couple of connection and use a couple of Execute SQL Task.

The process I need in one Execute SQL task use 'A' connection and the other use 'B' connection but I need to validate both of the process, so if one (especially the last process) fail, all processes will fail.

With only one fixed OLEDB connection I'm able to do this with double BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN for each connection, and retainsameconnection property of both connection set to True.

But when I set the connectionstring property of the connection dynamically within a script task within foreach loop container (loading many connections I need to use). It always execute the first initial connection string. When I set the retainsameconnection property of the connection to False then all things work fine, except for the transaction block. Without the transaction block all went ok, but I loose validation process I need for both connection.

I already tried to set transaction to required to no avail.

1. I run the distribution transaction service

2. I set security configuration of MSDTC to allow Network DTC access and all of the component, choosing no authentication required.

without this setting I've got error "The partner transaction manager has disabled its support for remote/network transactions.", with this setting I've still got error "The transaction has already been implicitly or explicitly committed or aborted" (I didn't use any transaction block)

Never mind that, I've solved the problem by using Parent Package to execute Child Package.

I spent a while trying to get around this problem. The easiest solution is actually to make sure you SET NOCOUNT ON at the top of your script.

This does work as long there is only one SELECT to return results. The other can SELECT INTO or be part of an INSERT INTO.

I did try but my requirement is using temp tables with parameters and found that this combination does not work with these steps any leads???

Hello

We are using SQL 2005 Fuzzy grouping and it runs fine in our test

environments and the same package is taking hell lot of time in production

database. The database size is same in both environments.

I ran the trace and found it is taking very long time in tempDB cursor…

Cursor is very simple inserting into a table with while loop. It takes 4K to

process 1 min in the beginning and gradually it takes 2K records in a min and

finally it will never completes. Where as in the Test environments same

tempdb cursor takes 20 to 30 sec to process all records. Total records are

around 150K.

Only Difference I see is in ….Temp DB  file groups in production we have 30

+ FG …in test environments two file groups.

Here is the cursor.

--############################

DECLARE FGCursor CURSOR LOCAL

FOR

SELECT _key_out FROM

[tempdb]..[##FLOut_080909_12:46:51_544_babf6eb0-7d77-4d7d-bef1-42563f3c4e60]

GROUP BY _key_out

ORDER BY sum(_score) DESC

DECLARE @key_out INT

OPEN FGCursor

FETCH next FROM FGCursor

INTO @key_out

WHILE @@fetch_status = 0

BEGIN

INSERT INTO

[tempdb]..[##FGOut_080909_12:46:51_544_babf6eb0-7d77-4d7d-bef1-42563f3c4e60]

SELECT * FROM

[tempdb]..[##FLOut_080909_12:46:51_544_babf6eb0-7d77-4d7d-bef1-42563f3c4e60]

o

WHERE _key_out = @key_out and

not exists (SELECT 1 FROM

[tempdb]..[##FGOut_080909_12:46:51_544_babf6eb0-7d77-4d7d-bef1-42563f3c4e60]

i

WHERE i._key_in = o._key_in or i._key_in = o._key_out)

FETCH next FROM FGCursor

INTO @key_out

END

DEALLOCATE FGCursor

--########################################

Hi jamie,

Thanks for the post, got me out of a real jam. Have been battling with local temp files in SSIS for ages, a global temp file never crossed my mind.

Nick