May 28, 2013 at 12:51 am
Hi Guys,
I am a newbie and I need to do the following task:
I have to process the rows in one table, get the column that has json string. I have to process this string and then enter this data into another table. I managed to do this by means of a script component.
But I also need to delete this processed row once I have populated the row in new table. How do I achieve this (how to check whether data was written into the new table before I actually delete the row from old table)?
Also, after I achieve this, I need to run a sql stored proc that generates temp tables in the the format that I need to export to excel. I think the 2nd task can be accomplished using "execute sql task" in the control flow section. Can someone point me to some example that makes use of passing parameters to the stored procedure? I have looked at very basic examples that do not help much.
TIA
May 28, 2013 at 8:55 am
Can you explain more about what you mean by process the json string? Are you parsing the string to populate multiple columns?
Is the source table in the same database or on the same server as the destination table? This changes what you'd need to do to accomplish the task.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 28, 2013 at 10:57 am
Yes. I parse the string and the tables are both in the same database on the same server.
May 28, 2013 at 11:11 am
Okay. Here's one way to do it:
Data Flow that transfers/processes the data -> On Success (using default settings this would mean all rows in your Source have been successfully processed) -> Execute SQL Task (Delete from source where {same criteria as your the source query from your dataflow task}) or, if you are also inserting a key from the source table (Delete from source where exists(Select 1 from destination where source.key = destination.key).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 28, 2013 at 12:27 pm
Thanks for your help. 🙂
May 28, 2013 at 2:59 pm
Oh, that's the easy way, and the way I would've done it, being a newbie myself. I wanted to see how you would delete each row after it was processed, using the Script Component in the Data Flow task.
Guess I'll live with the suspense.;-)
May 29, 2013 at 7:06 am
deandvorak (5/28/2013)
Oh, that's the easy way, and the way I would've done it, being a newbie myself. I wanted to see how you would delete each row after it was processed, using the Script Component in the Data Flow task.Guess I'll live with the suspense.;-)
If things had been on different servers/databases, I would have recommended 2 data flow tasks (1 loading a staging table in the destination server) and then the set-based delete.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 10:32 pm
Jack Corbett (5/29/2013)
deandvorak (5/28/2013)
Oh, that's the easy way, and the way I would've done it, being a newbie myself. I wanted to see how you would delete each row after it was processed, using the Script Component in the Data Flow task.Guess I'll live with the suspense.;-)
If things had been on different servers/databases, I would have recommended 2 data flow tasks (1 loading a staging table in the destination server) and then the set-based delete.
In my case, it was not much complex. I added a flag in the table and set it to true in the script task for all the rows that I process. Then at the end, I added a sql task that deletes all the rows whose flag was set to true. This might not be the case with the kind of situation that you are dealing with. Can you share the exact situation? I would like to do some brainstorming 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply