Welcome to EMC Consulting Blogs

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

Jamie, how, then, do you handle this error message:

"The raw adapters do not support image, text, or ntext data"??????

I've been going round and round and round and round!!!!!!!! I MUST get past this and complete my Upsert!!!!

Hi

I am new to SSIS and need some help.

My requirement:

If any column value changed in the table (primary key exists in the table), that need to be updated into another table.  The destination table is on remote server.  

I created SSIS package and tried to use temp table but I am unable to access that table.  

SSIS package details

1.  In the control flow, I Used SQL Execute Tasks to create temp table

2.  In the data flow task,

     I specified the table name in OLEDB Source.

     I specified the temp table name in the destination to dump the data from source to destination.

3.  In the control flow, I used SQL task to populate data to final destination table.

4.  When execute the package getting object doesnt exists error.

Please some one help on this and let me know if there any other options to do this.

Thanks in advance

I have been having an email back-and-forth with Jon Jaques which began with us discussing various limitations

At the end of the article Jamie explains he doesn't see the need to use temporary tables, in case he wants to pass data flow from one package to another. He uses raw file source and destination. It is worth mentioning there is even better solution available on the market, which is faster than the raw file. Instead of first writing data to file and then pulling it back from that file, the alternative solution streams data directly from one data flow to another.

You can find more about in the following blog post:

http://cozyroc.wordpress.com/2007/11/04/comparing-performance-of-a-raw-file-vs-cozyroc-data-flow-source-and-destination/

Thanks Jamie....  I have created global temp table and it is working.   Is there any alternate to avoid using temp table ?

Jamie,

I am able to create the table but then what I want to do is take the data from a flat file and then put it into this temp table. But I am not able to do that. As per you said it works if it is a global temporary table. But then once i complete the package and then delete the table I cannot delete the global temp table. The moment i delete it and try to execute the package within the designer (sql 2005) i throws up an error. Should i run the package outside or can i run it from my designer. The moment I delete the gloabal temporary table and then i try to run the package the error I get is that the designer says it cannot find the table. Any help?

Hello,

thanks for your posting. Its very informative.

I tried to replicate the task but I am taking some addtional steps also for the process.

Here it goes;

1) create a OLEDB connection pointing to SQL ServerDB

2) retainconnection=TRUE.

3) introduce ExecuteSQL task.

BODY:

create table ##temp01(id int)

GO

insert into ##temp01(id)

(

Select distinct Id from table)

GO

4) RUN

create table ##temp01(id int)

GO

under SSMS

5) I add a data flow task & set DelayValidation = TRUE

6) Add a OLE DB Source.

BODY:

SELECT * FROM #temp01

7) under SSMS DROP TABLE #temp01

8) Add a FLAT FILE destination and map it.

Is this correct? though it works.. But its not straight forward.

Secondly, For working with temp tables, It seems I need to create task for global temp table and next, need to modify EXECUTE SQL TASK and OLEDB SOURCE along with enabling WORK OFFLINE.

BTW, I am trying to export result of a temp table to a different format say flat file/ ACCESS/EXCEL.

I started with BCP. But figured out that, I can use Sproc ,as query,in BCP.

However it seems, i have to use Global temp table for using BCP. It doesn't work with local temp tables.

The thing being if using SSIS, i have to first create a global temp table and later mmodify it for local temp table then in that case, BCP seems better option.

Please advise.

Thanks

Is it possible to have a select...into and a select statement inside the same Execute SQL task...

Thanks guys.

This worked perfectly for me.

I had a problem when using Execute SQL task to run a .sql. The .sql contains two sets of statements one that does a select..into and the other a select. I have mapped the result set of the Execute SQL to a variable. When I started executing, it throw me an error saying disconnected result set not handled or some thing like that. And that is when I have posted the query.

After that I have realized that my select...into and select were under two batches(had a "go" in between) and that was the problem. Sorted that out...

But now, I have problem in that, I have a child package which contains (among other tasks) a file system task to make a copy of a file. When I  use a execute package task to execute this child package, I am getting the following error

"Error: The task "Copy the Template File to a temproray file" cannot run on this edition of Integration Services. It requires a higher level edition. "

But when I run the Child Package alone, it is executing with out any issues...

Any suggestions.

Jamie, thanks for the reply. BTW, what does that mean and what should I do to fix it.

Jamie,

Need your help on this. I am trying to load data into Excel file from SQL Server table.

After I did this, I wanted to transpose the rows in Excel to columns which I am doing using Script Task.

Now, after I do this, I am doing a copy of this modified excel(Source) to a location and then rename the Source.

But the package is failing at this File System Task level saying that the file is being used by another process. I have verified that the Script Component is doing the clean up work and releasing the file handle.

So what could the probable reason for this. Is the Dataflow task the culprit. Any pointers as to how to resolve this.

Thanks for the Help.

Aneel

Jamie,

Just Realized I was using a send mail task and this mail task takes the generated Source Excel as attachment. But this Send Mail task was failing and so the file handle is not being released by the Send Mail Task on failure.

So is there a way to force Clean up on the Send Mail Task.

thanks in advance

Aneel