Hi, I have requirement. I have a temporary table with and identity column,table will get populated with data on the execution of a dataflow task except the identity Column.now I need to insert value into identity column. How can I acheive this. Thanks, A2H

In data flow task, identity column will be populated automatically. In case you want to insert value into identity column you need to set identity insert on as: Set Identity_Insert TableName OnNitesh Rai- Please mark the post as answered if it answers your question

I have a temporary table with and identity column,table will get populated with data on the execution of a dataflow task except the identity Column.now I need to insert value into identity column. Do you mean a true #temptable, or a permanent table discarded (DROP TABLE) after use? #temptable is only accessible in the session(SPID) it was created, therefore you need to be really careful in designing Data / Control Flow. Generally better to use permanent tables in SSIS packages (see article following). The following T-SQL script creates a "permanent" table in tempdb; Note that this table, if not dropped, will be wiped out when SQL Server restarted. For persistent permanent table you have to use an application database. CREATE TABLE tempdb.dbo.Alpha (AlphaID int identity(1,1) PRIMARY KEY, Col1 varchar(64), ModifiedDate datetime default (getdate())) INSERT Alpha(Col1) SELECT 'New York City' INSERT Alpha(Col1) SELECT 'Washington' Related link: SSIS: Using temporary tables Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Hi Nitesh, Can u pls explain me little elaboratly.I am new to this.If it is inserting after the data flow task.is there any way where we can see the data in temporary table inbetween the SSIS package execution. Thanks, AjeeshThanks, A2H

as Nitesh said you can use check the IDENTITY Insert in oledb destination to enable identity to insert. BUT my question is: why you want to use Temporary table? SSIS has lots of features and abilites to check the datastream in the time of transfer, you can use lots of transformation for this. let us know what you want to do exactly and we will help you in right solutionhttp://www.rad.pasfu.com

Can u pls explain me little elaboratly.I am new to this.If it is inserting after the data flow task.is there any way where we can see the data in temporary table inbetween the SSIS package execution. I would suggest you to use global temp table (##). Once the table is populated inside the data flow task, you can (for test purpose) add one more data flow task and use the same conneciton manager that was used to populate the global temp table inside first data flow task. Select the global temp table as source and take the op of source to copy column component. Double click the green connector (between source and copy column) and add a dataviewer. Rememebr to set the "Retain Same Connection" property of the conneciton manager (which is pointing towards global temp table) as True. Check this link for how to use temp tables in SSIS: http://consultingblogs.emc.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspxNitesh Rai- Please mark the post as answered if it answers your question

Hi Reza,Nithesh,SqlUSA, My Requirement is: I have a select query which will fetch the records based on a condition(Condition is fetch records whose creation date is less than 15 days) Once I got the ids of records whose creation date is less than 15 days,I have to insert the records from tableA to tableB based on ID and delete the records from table A(Basically cleaning up the table) Ex : If I get a count 50 rows,I have to insert the 50 rows in table B and the same 50 rows need to get deleted from Table A.Thanks, A2H

I would suggest you to use global temp table (##). Why wouldn't you use a permanent table instead which is dropped at the end of the SSIS package? Permanent table persists therefore it can be useful in debugging.Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Hi SQLUSA, My DBAdmin will not allow to create Permanent table in database whiich wil be creating and dropping all time. Thanks, A2H

your requirement whole can be done in single data flow, no need to any temp table use oledb source connect it to a query which fetch rows which creation date is less than 15 days. then use a MULTICAST trasnformation, this will generate copies of your data stream connect two outputs from multicast to these components: add an oledb destination after multicast connect it to tableB add an OLEDB command which has delete statement and pass ID column in data stream to it. http://www.rad.pasfu.com

My choices in order - assumption: single user batch job, no concurrent execution (3. would work for concurrent execution as well) 1. Permanent table in tempdb like tempdb.dbo.StageInventory 2. Global temporary table: ##StageInventory 3. Temporary table: #StageInventory Another option: you ask the DBA to create a permanent staging table and you just TRUNCATE it to empty it.Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

I would suggest you to use global temp table (##). Why wouldn't you use a permanent table instead which is dropped at the end of the SSIS package? Permanent table persists therefore it can be useful in debugging. Only reason to use global temp table is not to create a seperate connection manager for tempdb. However, using a permanent table would be easy for debugging purpose as you mentioned.Nitesh Rai- Please mark the post as answered if it answers your question

Hi Reza, I am done with implementing your logic.I have one small doubt how I can give Transaction like Rollback and Commit in OLEBD Command Task. Please help me.Thanks, A2H