December 18, 2013 at 4:37 am
Hi all,
I have to transfer data from Source to Destination based on a condition, how can I achieve this?
Currently I took OLEDB as Source and after that I put a conditional split so that I can transfer data only for rows who has StatusFlag = 0 only and then I used OLEDB as Destination.
Is it Ok or Do we can achieve this scenario in another way too?
Thanks in advance....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 18, 2013 at 4:43 am
If your source data comes from a SQL Server table then why not use SQL command as your Data Access mode (in the OLEDB Data Source component) and have a SELECT statement along the lines of:
SELECT *
FROM yourTable
WHERE StatusFlag = 0
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 18, 2013 at 4:48 am
Abu Dina (12/18/2013)
If your source data comes from a SQL Server table then why not use SQL command as your Data Access mode (in the OLEDB Data Source component) and have a SELECT statement along the lines of:
SELECT *FROM yourTable
WHERE StatusFlag = 0
OK I will do in that way...
One more thing, when the data gets transfered for StatusFlag = 0, I want to update statusFlag to 1 for those rows....
How can I achieve this?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 18, 2013 at 4:51 am
kapil_kk (12/18/2013)
Abu Dina (12/18/2013)
If your source data comes from a SQL Server table then why not use SQL command as your Data Access mode (in the OLEDB Data Source component) and have a SELECT statement along the lines of:
SELECT *FROM yourTable
WHERE StatusFlag = 0
OK I will do in that way...
One more thing, when the data gets transfered for StatusFlag = 0, I want to update statusFlag to 1 for those rows....
How can I achieve this?
With an UPDATE statement. Write the IDs of those rows to a staging table. Then use an Execute SQL Statement to update the rows.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 18, 2013 at 4:57 am
+1 to Koens's suggestion.
I like to keep things simple so I would link the Data Flow Task to an Execute SQL Task (with On Success precedence constraint) that contains your UPDATE statement.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 18, 2013 at 5:03 am
Koen Verbeeck (12/18/2013)
kapil_kk (12/18/2013)
Abu Dina (12/18/2013)
If your source data comes from a SQL Server table then why not use SQL command as your Data Access mode (in the OLEDB Data Source component) and have a SELECT statement along the lines of:
SELECT *FROM yourTable
WHERE StatusFlag = 0
OK I will do in that way...
One more thing, when the data gets transfered for StatusFlag = 0, I want to update statusFlag to 1 for those rows....
How can I achieve this?
With an UPDATE statement. Write the IDs of those rows to a staging table. Then use an Execute SQL Statement to update the rows.
What I understand is that-
Means after filtering rows from Source for StatusFlag = 0, I need to transfer those rows to destination and temp table simultaneously...
Please correct me If I am wrong
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 18, 2013 at 5:05 am
kapil_kk (12/18/2013)
Koen Verbeeck (12/18/2013)
kapil_kk (12/18/2013)
Abu Dina (12/18/2013)
If your source data comes from a SQL Server table then why not use SQL command as your Data Access mode (in the OLEDB Data Source component) and have a SELECT statement along the lines of:
SELECT *FROM yourTable
WHERE StatusFlag = 0
OK I will do in that way...
One more thing, when the data gets transfered for StatusFlag = 0, I want to update statusFlag to 1 for those rows....
How can I achieve this?
With an UPDATE statement. Write the IDs of those rows to a staging table. Then use an Execute SQL Statement to update the rows.
What I understand is that-
Means after filtering rows from Source for StatusFlag = 0, I need to transfer those rows to destination and temp table simultaneously...
Please correct me If I am wrong
Depends. Maybe you can write them only to the destination table. I don't know. Your requirements are vague, so you get vague answers. You didn't even specify which table you want to update.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 18, 2013 at 5:07 am
You don't really have to transfer the IDs into a staging table if you don't want. As I said above, you can have an On Success precence constraint from your Data Flow Task to your Execute SQL Task which will have an UPDATE statement along the lines of:
UPDATE YourTable
SET StatusFlag = 1
WHERE StatusFlag = 0
But I'm not an SSIS expert and without knowing more about your process I can't make better suggestion. It depends on what tyou're doing really!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 18, 2013 at 5:07 am
Abu Dina (12/18/2013)
+1 to Koens's suggestion.I like to keep things simple so I would link the Data Flow Task to an Execute SQL Task (with On Success precedence constraint) that contains your UPDATE statement.
I am also thinking doing in that way only but little confuse that If I write query using Precedence constraint as Succees in ExecuteSQLTask as
Update table1
Set StatusFlag = 1
Then does it update only those rows which comes from source for StatusFlag = 0 or it will update the statusflag = 1 for whole table...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 18, 2013 at 5:09 am
kapil_kk (12/18/2013)
Abu Dina (12/18/2013)
+1 to Koens's suggestion.I like to keep things simple so I would link the Data Flow Task to an Execute SQL Task (with On Success precedence constraint) that contains your UPDATE statement.
I am also thinking doing in that way only but little confuse that If I write query using Precedence constraint as Succees in ExecuteSQLTask as
Update table1
Set StatusFlag = 1
Then does it update only those rows which comes from source for StatusFlag = 0 or it will update the statusflag = 1 for whole table...
It will update the entire table.
Again, clear requirements please.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 18, 2013 at 5:10 am
kapil_kk (12/18/2013)
Abu Dina (12/18/2013)
+1 to Koens's suggestion.I like to keep things simple so I would link the Data Flow Task to an Execute SQL Task (with On Success precedence constraint) that contains your UPDATE statement.
I am also thinking doing in that way only but little confuse that If I write query using Precedence constraint as Succees in ExecuteSQLTask as
Update table1
Set StatusFlag = 1
Then does it update only those rows which comes from source for StatusFlag = 0 or it will update the statusflag = 1 for whole table...
kapil kapil kapil..... what's wrong bro? 😛
You're transferring all records where StatusFlag = 0 so your UPDATE statement will be changing all records with StatusFlag = 0 to StatusF;ag = 1 (in other words updating the rows you've just transferred).
UPDATE YourTable
SET StatusFlag = 1
WHERE StatusFlag = 0
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 18, 2013 at 5:14 am
Abu Dina (12/18/2013)
kapil kapil kapil..... what's wrong bro? 😛
You're transferring all records where StatusFlag = 0 so your UPDATE statement will be changing all records with StatusFlag = 0 to StatusF;ag = 1 (in other words updating the rows you've just transferred).
UPDATE YourTableSET StatusFlag = 1
WHERE StatusFlag = 0
True if you are updating the source table. Not necessarily true when updating the destination table.
ps: you were quick with your edit 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 18, 2013 at 5:20 am
Koen Verbeeck (12/18/2013)
kapil_kk (12/18/2013)
Abu Dina (12/18/2013)
+1 to Koens's suggestion.I like to keep things simple so I would link the Data Flow Task to an Execute SQL Task (with On Success precedence constraint) that contains your UPDATE statement.
I am also thinking doing in that way only but little confuse that If I write query using Precedence constraint as Succees in ExecuteSQLTask as
Update table1
Set StatusFlag = 1
Then does it update only those rows which comes from source for StatusFlag = 0 or it will update the statusflag = 1 for whole table...
It will update the entire table.
Again, clear requirements please.
I have to transfer data from table1 to table2 which has same structure.. From table1 only those rows should get transferred whose statusflag = 0 to table2. After data gets transferred change the status flag to 1 for those rows... I think now its get cleared to you
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 18, 2013 at 5:22 am
kapil_kk (12/18/2013)
After data gets transferred change the status flag to 1 for those rows...
In which table? Table1 or table2?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 18, 2013 at 5:25 am
Koen Verbeeck (12/18/2013)
kapil_kk (12/18/2013)
After data gets transferred change the status flag to 1 for those rows...
In which table? Table1 or table2?
in table1 from which I am transferring data..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply