April 22, 2010 at 8:30 am
I have to create a flow in which i need to check a column value based on
that i need to insert ,update or delete in the table but this should be row
by row operation.
is it possible in SSIS.
April 22, 2010 at 1:16 pm
Would you mind explaining why you have to have it as a RBAR (row-by-agonizing-row) process? Best would including some fake sample data so we can better understand your requirement...
April 22, 2010 at 1:31 pm
Yes, it is possible; but I too would like to better understand your requirement before teaching you something that may not be the best approach.
April 23, 2010 at 1:28 am
This is a more detailed description of what I am trying to achieve, Im faily new to SISS.
I have a Excel 2007 file with 187999 rows of data (as shown below), so I figure its going to take a fair amount of time.
F1 ¦ F2 ¦ F3 ¦ F4
-----------------------------------------
21 ¦I ¦1 ¦10024414482
24 ¦I ¦2 ¦10024414482
21 ¦U ¦3 ¦10004678137
24 ¦U ¦4 ¦10004678137
24 ¦I ¦5 ¦10004678137
21 ¦U ¦6 ¦10004678205
The first column is the table which the data needs to be manipulated
The second column is whether the data in the tables 21,24 is (I) inserted, (D) deleted and (U) updated.
The third column is the order they have to be processed in (row by row)
Ihave tried useing a conditional split and then running the isert update or delete sql statments, but it selects all the inserts and then inserts into the table , it then selects all the updates and then updates the update table.
It has to run in order F3 (row by row)
April 23, 2010 at 2:29 am
I have absolutely no experience implementing RBAR, but this is how I would try to do it:
write the contents of the Excel file to a table (temp table or persistent staging table, that is up to you. But beware, temp tables behave quirky in SSIS.
Then, add an Execute SQL Task with some T-SQL code that runs against this table. In this T-SQL task, do the following:
* loop over the table using the third column.
* get the value of the first, second and fourth column. Create a case statement that contains 4 SQL statements. Two for each table (21 and 24). One for an update, one for an insert. This case statement uses the values of the first and second statement to determine which statement to execute. The value of the fourth column gets embedded in the SQL statements.
The downside of this (and of RBAR in general I think) is that there will be 187999 different SQL statements issued against the database, so performance wise, it's not so good.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 23, 2010 at 4:01 am
can i not use th loop trough container to read through each row
April 23, 2010 at 4:25 am
Probably yes, but I have more experience setting up a while loop in T-SQL than configuring a for each loop with an ADO.NET enumerator, so I personally would choose for a script. Implement it in the way you feel most comfortable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 23, 2010 at 10:27 am
What is the scope of the insert/update/deletes? Do they all impact one single row or mutiple rows in the destination tables?
April 26, 2010 at 1:20 am
Cheers for the replies.
The inserts,updates and deletes will only effect one row ie. it could update the row then delete the row and then insert the row all acording to the sequence order (column 3). there are 27 columns for each row which could be changed.
I am being a bit cheeky, but could someone supply me with some sample code. 🙂
I have attached what i have done so far
April 26, 2010 at 10:01 am
I have been battling this problem all day and seem to have taken backwards
Iam using Excel 2007 and have installed AccessDatabaseEngine.exe, after adding another execute sql task an error appears
as shown below
[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "C:\Documents and Settings\Administrator\Desktop\NLPG\e1cl73-7016-01-JAN-2010to01-FEB-2010-firecontrol_test.xlsx" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[DTS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
also I am not sure that am using the correct syntax for the sql statement for the Execute sql task.
SET NOCOUNT ON
SELECT F1 INTO #temptable FROM 'ecl73-7016-01-JAN-2010to01-FEB-$'
i think its not the correct file name, this is the table view name from the OLE DB Source Editor
April 26, 2010 at 1:24 pm
clucasi (4/26/2010)
Cheers for the replies.The inserts,updates and deletes will only effect one row ie. it could update the row then delete the row and then insert the row all acording to the sequence order (column 3). there are 27 columns for each row which could be changed.
I am being a bit cheeky, but could someone supply me with some sample code. 🙂
I have attached what i have done so far
I would recommend getting rid of your conditional split. Add in a sort step to sort by primary key, then your sequence column. Feed the data set into a single OLE DB Command step. Have that step call a stored procedure. Use the SP logic to read in the DML Type (INSERT/UPDATE/DELETE) and perform the appropriate action on the row.
April 26, 2010 at 1:45 pm
I would try to do it set based as much as possible:
If the last step is DELETE then DELETE the sequence order if it exist.
If the last step is INSERT then do an UPSERT (conditional UPDATE or INSERT).
If there is only one step and it's an UPDATE, do so.
Perform the RBAR solution only to the sequence orders not already covered.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply