question

mushtaq avatar image
mushtaq asked

Passing Variable in SSIS

Hi Everyone, I need some help to pass row value to the other task in SSIS package.here is my sample query select distinct txnno from tbltxn, what I need is to get distinct txnno from this qurey and delete records from other table based on this txnno. I think we can pick txxno in some variable in a foreach in a container and pass that recordset value to the query which is used to delete.But I have not done this before , so I need some clues and examples to solve this problem. Thank you Mushtaq
ssissql-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
My suggest is to use lookup tool and OLEDB Command or Script Component tool instead of using foreach in a container - Create tbltxn as your OLEDB Data source - Use lookup tool to find the matching txno records between tbltxn and your other table(Say DestTable) that you wish to delete - use OLEDB Command or Script Component to delete the records from DestTable based on the matching record.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you ur suggestion realy helped me , I got it
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Mushtaq. If I understand you correctly, you want to delete records in one table by selecting values from another. You do not need to do this using a loop, you should use set-based T-SQL to achieve this result. Something like this would do the trick DELETE FROM dbo.TableA WHERE txnno IN (SELECT DISTINCT txnno from dbo.tbltxn) This query could be run in SSIS if you want it to be part of a chronologically ordered set of commands.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Along with the IN statement, you might want to exclude NULLs too just in case. -> (SELECT DISTINCT txnno from dbo.tbltxn WHERE txnno IS NOT NULL). I just got a headache because of a NULL in a similar situation!
1 Like 1 ·
I'd be interested to see the execution plan and table/index creation scripts to see if this really was a problem. I cannot imagine it being such a terrible problem as you suggested. Is this table part of a high concurrancy system, or are you guessing that you will experience problems? Deleting all the records in one step should be better than deleting them one after the other in a loop. You can batch the deletion into 10000 steps and that will still be hugely faster than one record at a time.
1 Like 1 ·
you suggestion leads to bad performance because if i have millions of records to delete with IN operation it leads to performance degradation and server will perform slow. Due to this reason i have not used this approach and i was looking for some feasible and performance oreinted approach to solve this problem.
0 Likes 0 ·
Daniel Ross avatar image
Daniel Ross answered
Hi Mushtaq, see this answer i gave a little while ago [ http://ask.sqlservercentral.com/questions/17156/how-to-consolidate-multiple-similar-ssis-packages-that-export-data-to-txt-file][1] Basically, you can add your distinct values to a recordset variable, and use the ado.net enumurator in the for each loop task to use the variable value. Then you can use the variable any way you want. [1]: http://ask.sqlservercentral.com/questions/17156/how-to-consolidate-multiple-similar-ssis-packages-that-export-data-to-txt-file
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.