Update sybase table based on sql server table

Question

Update sybase table based on sql server table

sonal_anjaria

SSC Eights!

Points: 854
More actions
June 6, 2011 at 11:50 am

#389665

i was able to do a straight insert into sybase using SSIS
i want to do an update to a sybase table based on a sql server table
created a data flow
ole db source = sql server
ole db destination = sybase
i go into destination and go to build query, query builder was smart and did the where clause,
update test
set field1 = sqlservertable.field1
where relevant_id = relevant_id
I am having trouble setting the newvalue, how do i tell ssis to use the value in the sql server table, i hope i have been eloquent enough in my question
sonal.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

SSIS does not work like that.

You'll need to pull the SQL Server data into an SSIS recordset, iterate over it and issue an update in Sybase for each row.

Or you can stage the SQL Server data in a Sybase table and issue an update-join statement in Sybase.

Or you can setup a Sybase Linked Server in SQL Server and issue an update-join statement in SQL Server against the Sybase Linked Server.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 2

opc.three (6/6/2011)
SSIS does not work like that.
You'll need to pull the SQL Server data into an SSIS recordset, iterate over it and issue an update in Sybase for each row.
Or you can stage the SQL Server data in a Sybase table and issue an update-join statement in Sybase.
Or you can setup a Sybase Linked Server in SQL Server and issue an update-join statement in SQL Server against the Sybase Linked Server.

While it is possible to use a linked server in this case I would absolutely NOT do it. I used to be a BIG proponent of linked servers, however, years of experience (and pain) have led me to be VERY careful with their use.

He could also use an OLEDB Command in the data-flow here he could issue a command like that.

However if he is doing 10's of thousands of updates then dumping the data into a table on the sybase side and running an update over the whole set would be preferable.

But you are right SSIS doesn't work that way, it isn't some super database that sits on top of the source and destination.

CEWII

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Elliott Whitlow (6/7/2011)
While it is possible to use a linked server in this case I would absolutely NOT do it. I used to be a BIG proponent of linked servers, however, years of experience (and pain) have led me to be VERY careful with their use.

Agreed, the last option presented is the least attractive. I just threw it out as just taht, an option, but it definitely deserved a disclaimer. I will usually pick on a post that recommends a poor use of a Linked Server because I have spent a lot of time using and tuning queries that use them as well, so thanks for calling that out.

But you are right SSIS doesn't work that way, it isn't some super database that sits on top of the source and destination.

SSIS is definitely cool, but it's not magic pixie dust, at least not yet.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 4

Agreed, I love SSIS and it can do a lot. Unfortunately I see a lot of misunderstandings about what it can and can't do. If you understand its boundaries and limitations you can make it scream, its just unfortunate that the learning curve is SOOOOO steep. And with all the experience I have I'm still learning new things about it all the time..

CEWII