SSIS data flow update table

Question

SSIS data flow update table

princa

SSCommitted

Points: 1819
More actions
July 29, 2010 at 11:31 pm

#141484

Hi all,
I am a new SSIS user, and I am facing the following problems:
1. I want to update table A's certain columns from table B, is data flow the right way to do that?
2. Table B is a full table, contains all the information, table A is kind of sub-table, which contains certain data, match with ID for instance, If I want to update table A when table B has some updates, but I need to match table A's ID with table B to make sure that the update is correct for each ID in table A, how can I do that?
Say:
1. Table B has
ID Name Number price
1 apple 5 2.3
2 banana 6 3.2
2. Table A has
ID Name Number
1 apple 5
Now, if I update table B's data, change apple's number to 10, how could I update A table's number through SSIS？
Thanks!!

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 1

If you only want to update specific rows in your sub-table, you would use a SQL Task in the Control Flow and write a standard SQL update query.

If you do not need to persist any data in your sub-table, you could truncate it with a SQL Task and use a Data Flow task to completely reload the sub-table from the master table.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 2

Is this because you have the same data in multiple tables (ie, redundant data)?

princa SSCommitted Points: 1819 More actions · Answer 3

Ed Zann (7/30/2010)
If you only want to update specific rows in your sub-table, you would use a SQL Task in the Control Flow and write a standard SQL update query.
If you do not need to persist any data in your sub-table, you could truncate it with a SQL Task and use a Data Flow task to completely reload the sub-table from the master table.

Thanks Ed Zann, I think the SQL task would be good for me in this case. but I am still interested with dataflow with variables, is it gonna work in this case too?

princa SSCommitted Points: 1819 More actions · Answer 4

Phil Parkin (7/30/2010)
Is this because you have the same data in multiple tables (ie, redundant data)?

Hi Phil, I do have the same data in multiple tables, that's because they are in the different database, one is centralized database server and serve for all other sub-databases, all the sub-databases get whatever they need to get from the main database, of course only part of main database. any suggestions?

Phil Parkin SSC Guru Points: 247158 More actions · Answer 5

Sure - do a multicast to all of the tables you need to update ...

princa SSCommitted Points: 1819 More actions · Answer 6

Phil Parkin (7/30/2010)
Sure - do a multicast to all of the tables you need to update ...

multicast?! any simple examples?:D

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 7

princa (7/30/2010)
Ed Zann (7/30/2010)
If you only want to update specific rows in your sub-table, you would use a SQL Task in the Control Flow and write a standard SQL update query.
If you do not need to persist any data in your sub-table, you could truncate it with a SQL Task and use a Data Flow task to completely reload the sub-table from the master table.
Thanks Ed Zann, I think the SQL task would be good for me in this case. but I am still interested with dataflow with variables, is it gonna work in this case too?

The Data Flow Task is primarily used to INSERT rows into a destination. You can perform UPDATEs in a Data Flow by way of the OLEDB Command component, but I don't think it performs very well. Basically it runs an UPDATE statement for each row from the source. Your source query would have to identify the changed rows from your master table, or you would use the Lookup component to compare the sub table with the master table (another performance killer).

Perhaps I don't understand what you're trying to do... but if it is just synchronizing data between tables, you would probably be better off with some type of set-based approach.

princa SSCommitted Points: 1819 More actions · Answer 8

Ed Zann (7/30/2010)
princa (7/30/2010)
Ed Zann (7/30/2010)
If you only want to update specific rows in your sub-table, you would use a SQL Task in the Control Flow and write a standard SQL update query.
If you do not need to persist any data in your sub-table, you could truncate it with a SQL Task and use a Data Flow task to completely reload the sub-table from the master table.
Thanks Ed Zann, I think the SQL task would be good for me in this case. but I am still interested with dataflow with variables, is it gonna work in this case too?
The Data Flow Task is primarily used to INSERT rows into a destination. You can perform UPDATEs in a Data Flow by way of the OLEDB Command component, but I don't think it performs very well. Basically it runs an UPDATE statement for each row from the source. Your source query would have to identify the changed rows from your master table, or you would use the Lookup component to compare the sub table with the master table (another performance killer).
Perhaps I don't understand what you're trying to do... but if it is just synchronizing data between tables, you would probably be better off with some type of set-based approach.

Hi Ed Zann, thanks for your response, though it's been a long time.

Actually, you are right on the problem, I am doing a UPDATE from one DB to another DB, and I need to match with every row which from destination DB to source DB, as you said if use OLEDB Command Component, which will kills me due to the mass data and for each row it will send one query to DB server, it's not gonna work.

Yes, I am just synchronizing data between tables, but not all columns from destination, just some of them, what's your suggestion? (you mentioned about set-based approach,,,what's that? )

Thanks.

SSIS data flow update table

Cookies on SQLServerCentral