November 8, 2011 at 11:39 am
Hello,
I am not sure , If I can use this OLEDB transformation for this req
A column from a table is sent out as a output, now i sholud use every row in the column.
Say column ids
1
2
3
4
Now for every row value above (column ids), I should delete particular row in another tables ( Has to delete rows in 10 different tables for every column ids above )
Can I do this using OLE DB Source and OLE DB Command in Data flow task
November 8, 2011 at 12:00 pm
Your request isn't really very clear ... I can think of several solutions, but can you elaborate a bit on the problem?
November 8, 2011 at 12:38 pm
kramaswamy (11/8/2011)
Your request isn't really very clear ... I can think of several solutions, but can you elaborate a bit on the problem?
Here you go ....
Operation 1. Select distinct column_ids from Table A
Results you
1
2
3
4
Operation 2.
Delete table B where column_ids = ( result set of 1 task above )
---- Here, rows in table B are delete for each Column_ids value from operation 1
delete table C where column_ids = ( result set of 1 )
--- rows in table C are delete for each Column_ids value( 1,2,3,4,5) from operation 1
this way I have 10 tables to delete
Can I use OLE DB Command in place of 2. and Inplace of 1 OLE DB Source.
I Could do this with Foreach Loop. But I wan to see, If I can acheive the same thing through OLE DB Command...
Please let me know, If I am not clear Still.... I can try out something different
November 8, 2011 at 12:43 pm
That's definitely more clear, thanks.
You *could* do it with an OLE DB Command, but I don't think it's the most efficient means to what you're trying to accomplish.
A ForEach loop, iterating over all 10 of your tables, and then executing an Execute SQL task is probably best. Your Execute SQL task will just be something like:
"DELETE FROM " + @TableName + " WHERE ColumnValues IN (SELECT ColumnValue FROM TableA)"
November 8, 2011 at 1:19 pm
kramaswamy (11/8/2011)
That's definitely more clear, thanks.You *could* do it with an OLE DB Command, but I don't think it's the most efficient means to what you're trying to accomplish.
A ForEach loop, iterating over all 10 of your tables, and then executing an Execute SQL task is probably best. Your Execute SQL task will just be something like:
"DELETE FROM " + @TableName + " WHERE ColumnValues IN (SELECT ColumnValue FROM TableA)"
You are right i could do it through Foreach loop...
But the Table A is an Orcale table and the remaing tables are SQL.
And all the 10 delete statements has different logic.. they are not going to be same ...this some what complex for me to explain... but any way I will try to tell what exactly I am trying to acoomplish...
Now I Guess I can't make one EXecute Sql task and embedde all the different 10 Deletes statement with one Parameter on each... it fails
So I have to make 10 different Execute sql task for each Delete statement ... which i Don''t want to do ... its going to be a very long package in terms of look....
Just had a thought, If I can embedde all 10 delete statement in OLE DB command ... as this looks much simple and easier
November 8, 2011 at 1:30 pm
Why not have a stored procedure, which takes as an input the name of the table?
Then in the procedure you can have a CASE statement, and depending on the table name, dynamically build your query?
November 8, 2011 at 1:58 pm
kramaswamy (11/8/2011)
Why not have a stored procedure, which takes as an input the name of the table?Then in the procedure you can have a CASE statement, and depending on the table name, dynamically build your query?
I donot.. get it either ... thhey donot want it to have an SP....
But any way ... Here is thought I got of no where.... please do correct me ...
Data Flow
OLE DB Source
Extracting all the Distinct rows from Orcale Table
Then OLE DB Command 1
Connecting the Column name from source to Param_0.
Delete statement 1
then connecting again OLEDB Command 2 to OLEDB command
--- this way, for all the delete statements connecting 10 OLE DB command.
How is this ...do you think it sounds good
November 8, 2011 at 2:01 pm
You can't have multiple outputs from an OLE DB Source.
What you'll need to do is put a Conditional Split, and direct each to it's own OLE DB Command
November 8, 2011 at 2:13 pm
kramaswamy (11/8/2011)
You can't have multiple outputs from an OLE DB Source.What you'll need to do is put a Conditional Split, and direct each to it's own OLE DB Command
have a look at attachment.....
Each OLE DB command Task refer to Delete statement ... so the source columns are same for every Task..
November 8, 2011 at 2:19 pm
There's no need to execute every one of those tasks. If you use a Conditional Split instead, you have all of them executing in parallel instead of in series. You can keep the multicast to get the rowcount, put the split right after it and then attach each OLE DB Command to one of the conditions from the split.
November 8, 2011 at 2:26 pm
kramaswamy (11/8/2011)
There's no need to execute every one of those tasks. If you use a Conditional Split instead, you have all of them executing in parallel instead of in series. You can keep the multicast to get the rowcount, put the split right after it and then attach each OLE DB Command to one of the conditions from the split.
Ok...I got you
Here is a reason behind me excuting Series of those ...My logic wants the delete statements in order .... they are depended...
Coming to your logic, Conditional split ...y Do you want to have it ... when evey row from source must under go Delete Statements down the line ... I donot get you ...Please try to explain ... If you feel like, you have the right logic
November 8, 2011 at 2:29 pm
If the order is important, then yeah your solution is probably the only option. The whole purpose of using a conditional split is to have things go in parallel, which obviously can't retain an order.
Does your solution work?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply