I see that transformable subscriptions have been deprecated in SQL Server
2005. And although SQL Server BOL tells you how to setup SSIS to run DTS
packages it also notes that this will be discontinued in future releases...
What is my alternative to transformable subscriptions in SQL Server 2005 and
not having to setup DTS backward compatibility?

I want to publish from SQL Server 2000 and SQL Server 2005 will subscribe to
this publication. However the data needs to transformed. Any suggestions?

Thanks. Mike

Transformable subscriptions were designed to massage the data from one form
to another FOR HETERGENEOUS SUBSCRIBERS. For SQL Server subscribers you
would incorporate the massaging logic inside your custom replication stored
procedure.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Mike,
indexed views is one possibility. Another is snapshot replication with
post-snapshot scripts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Might have been designed for, but isn't how it is used in at least 1/2 of
the implementations that I've seen.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.


[quoted text, click to view]

Still wouldn't accomplish it.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.


[quoted text, click to view]

Unfortunately, there isn't a replacement. The closest you could get would
be with custom business logic. You'd have to come up with some method to
make a call out from the custom business logic to launch a SSIS package,
SQLCMD, or a command line untility.

I know of several that use transformable subscriptions to interface with an
AS/400. There is no replacement to this and you'd have to code everything
yourself. The same goes for the ones that I know that interface to Oracle
and DB2. There are several that I know of which utilized transformable
subscriptions between SQL Servers and those can at least be replaced with
custom business logic and a boat-load of coding.

It's a feature that I've very strongly recommended a replacement to in the
next version of SQL Server, but if I'm the only one asking for it, it will
never be done. So, if you have a need for transformable subscribers, make
it known within the poduct feedback center. The dev team would only have to
look about 50 miles away to find an implementation of transformable
subscribers that impacts their lives every single day.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.


[quoted text, click to view]

Mike,
I'm not too sure you can really say this with any degree of certainty as it
largely depends on what Mike Mullane is trying to achieve.
Certainly indexed views and post-snapshot scripts are not a complete
replacement for transformable subscriptions - or else the latter
functionality wouldn't exist. What I am offering are potential solutions to
help solve Mike's business requirements. Maybe they will suffice, maybe
not - (please update us on this matter Mike M).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)