I have 2 diferent server. In a SSIS project I want to select data from server1 and transfer the data in a temp table in server2. Here is what I have try but can't make it work.1-Create a oldb1 connection to select from server12-Create a oldb2 connection to server2 with retainsameconnection to true3-task flow 3.1 - sql task ( connect to oldb2and create table #test(field1 int)) SSIS give no error 3.2 - Data flow task 3.2.1 - connect to oldb1 in the data source and do the select I need SSIS give no error 3.2.2 - connect to oldb2 in the data destination and try to send data in #test SSIS said no table #test 3.3 - sql task ( connect to oldb2do some data transformation on #test 3.4 - sql task ( connect to oldb2and deletetable #test)Does anyone have a idea how can I do this or where i'm wrong. (Please don't tell me to create a real table on server2...) If someone have a package like this one, i'm really interest to take a look at it to see where i'm wrong Steve s_forgues@hotmail.com ThankSteve

The .... sql task ( connect to oldb2and create table #test(field1 int)) SSIS give no error ... must be out of the the DFTif you want to make a Temp table in SSIS you must use a SCRIPT TASK that is out side of DFT try this -- ==========================================================================================-- ==========================================================================================-- Author:Shahriar NIKKHAH DATE : March 25, 2008Time: 16:26-- Name:-- Description:--If you want to use a #temp table in SSIS and then use the #temp table in a "Data Flow task" (DFT)-- You must create the #temp table in a separate "Execution SQL Task" object in SSIS and then in another --"Execution SQL Task" object run your formula against the #temp table, next step is to Run the -- "Data Flow task" (DFT) which abisclly will use the #temp table ----------------------------------- Example....-- "OLE DB Source" object in the SQLCommand in the properties, you must TYPE in the "Properties window"--DO NOT right click on the "OLE DB Source" and select "Edit..." you have to type from the properties window-- like .....-- SELECT [UserId],[QueueID],[EventDate],[M7921],[M7922],[M7923], [M7924], [M7925], [M7926], [M7927], [M7928] FROM #MainView2--Must type in one line DO NOT plress the ENTER button-- End of Example....-----------------------------------and finally is the last step (which is out of the DFT ) is to drop the #table with "Execution SQL Task"---- Modified by:-- Last Modified On: -- ========================================================================================== SET NOCOUNT ON----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE #MainView2([UserID]BigInt,[QueueID]BigInt,[EventDate]SMALLDATETIME,[M7901]FLOAT,[M7902]FLOAT,[M7903]FLOAT,[M7904]FLOAT,[M7905]FLOAT,[M7906]FLOAT,[M7907]FLOAT,[M7908]FLOAT) ;GOSincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

Try creating a global temp table using (##test)Nitesh Rai- Please mark the post as answered if it answers your question

Hiyou mentioned ...... connect to oldb2 in the data destination and try to send data in #test SSIS said no table #testBecause you dont have a temp table it is not made insted of making a temp table you can use a datasource and a OBJECT VARIABLE that playes the role of your Temp TableQuestion why do you need a temp table in the destination SQL? because once the package ends the temp table will be droped, all i am asking what is your next step after having a temp table in the DFT? do you have another DFT after the first one?Sincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

If you want a GLOBAL table just change the script to CREATE TABLE ##MainView2Sincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

I think it's not want I need...What I want is to send the data from oldb1 source to a #temp table in the lodb2 destination.If you want I can send you an exemple of what i'm trying to do.Steve

My task flow look like thissqltask create #temp tableDFT to select and transfer to #temp tablesqltask transform #temp table datasqltask to update a 3rd table from #temp tableSteve

please do SNikkhah@Live.caPlease remove all confidential information or documentsplease if you can script your source table and insert few data in it as in INSERT INTO TABLE XXXX VaLues (.......and you didi'nt say whay what will be your next move in SSIS after populating the Temp table?Sincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

stefor,you are not able to get the temp table in the drop down box..rt?by creating a global temp table through ssms will allow you to select the table from the drop down boxNitesh Rai- Please mark the post as answered if it answers your question

Nistesh is right you can also make a regular table set your connection (we are talking in designe mode) and set the DELAY VALIDATION = TRUE for the Destination object and save the SSIS and finally remove the table , so that SSIS will make the Temp tablestill haven't got your sample, please use Windows ZIP (i dont have rar nor winzip)Sincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

If I do like you said it work for the DFT but can't acces the temp table in next SQL task.Any idea?ThankSteve

I think I have it done, restart a project from nothing and it work. Just don't know what was my problem.I did like nistesh.I'll try in my original projevt and if it work I'll mark post as answer.Steve

Youre Package STEP1: Execute SQL Task You are using a the tempDB as the connection DB that is not a good idea, because you are making a temp table so technically it will be virtually made in the tempDB. You are making a global temp table why? I dont know and I am sure you have a good reason for that. create table ##Temptest(field int) STEP2: Data Flow Task The DFT has 2 steps, 1- OLE DB Source 2- OLE DB Destination The OLE DB Source is just a simple SELECT statement (**) The OLE DB Destination is inserting data to the ##Temptest table. STEP3: Execute SQL Task 1 Inserting into a final table insert into AdventureWorks.dbo.test(field1) (***) Step1 answers: You are missing SET NOCOUNT ON in your code, i have read some documents that mentioned that to make temp tables in SSIS you may need the NoCount to be on and etc.. Step2 answers on OLE DB Destination: You can do this in a script its much easier, why? Because to set the destination table you must make a table in tempDB do the settings in SSIS, link the fields and etc.. them remove the table from tempDB and go back to SSIS and set the destination table to the #TempTable and etc.. But all of this and much less can be done in a Script Task and you can copy that in SSMS and do your tests Step3 answers: Your Insert statement is wrong.Sincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

My suggestion Story short you are looking to make a temp table, populate it with data, and finally inserting the temp table data into a 3rd table. I would make 2 DFT for that and a Object Variable that plays the role of your temp table First DFT Step 1 : Your simple SELECT statement ( see **) Step 2 : inserting into a Record Destination that is liked to a Object variable, this part is playing the role of a temp table Second DFT Step 1 : A script Component that is reading the record from the Object Variable and passing it on to the next step Step 2 : Inserting into your final 3rd table ( see ***) I have emailed you the package Sincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

Steve, there's another way to do it if you don't want to use a Global TMP table but a local temp table instead. You need at least two conenctions: OLEDB connection to any DB (oldb1 in your case) OLEDB connection to the tempdb in the server with the RetainSameConnection enabled. what you do then is the following: You pull the data from oldb1 and apply whatever transformations you need. Add an "OLEDB Destination" task that uses the oldb1 connection. Create a normal table in the oldb1 DB and do the mapping of columns. Set the property ValidateExternalMetadata of that "OLEDB Destination" to false. Open the "OLEDB Destination" using the Advanced Editor and change the connection to the tempdb and the table name to #test Set the DelayValidation of the DFT to True. Before this DFT put a SQL Task that creates the #test table in the tempdb. And that way you will be able to insert into a local tempdb within a DFT. Hope it helps, Regards, Cristian

stefor, you are not able to get the temp table in the drop down box..rt? by creating a global temp table through ssms will allow you to select the table from the drop down box Nitesh Rai- Please mark the post as answered if it answers your question This is my issue. I have created the global temp table but I cannot see it in the drop down box of an OLEDB destination. What should I do?

Do not create a Global temp table First.... Try below... 1. Create an OLE DB connection to your database. Set DelayValidation = True and RetainSameConnection=True in properties window. 2. Add OLE DB destination Task in your dataflow. 3. Double Click on Destination in Step#2. Then Select Connection Manager you created in step#1. Select Data access Mode drop down to Table or view - fast load. Then For Name of Table or View Click on New, replace the Table Create Script by yours Temp table. Then say OK. your table will be displayed in the drop down. Don't click on Preview or Column Mapping until you perform following step. Go to your SQL client tool. Then Create same Global Temp Table. Then Come Back to Preview or Column Mapping and you can see the temp table content.