I have a database (A) with one single table. I have a database (B) in
an alternate location. I would like to move a subset of the data in
database (A) to database (B) on a monthly basis.

Database (A) is fed from a download of financial information which is
imported from a text file. Once a month a file is downloaded off the
main frame. Then a script I wrote is run in query analyzer.

I was thinking of creating a DTS task to push the data from a query on
database (A) to a table on database (B). I am having some trouble
setting it up. I created a new package with two connections. Then I
tried to set up a data drive query to push the data from the query in
database(A) to the table in database(B). But at this point I am
wondering if this is even possible. Maybe I am going about this in
the wrong way. Any suggestions would be appreciated

TIA

Tom Dauria (tdauria@bu.edu) writes:
[quoted text, click to view]

The way I would do it, is probably just to run the script from SQL Agent.
Sometimes there can be problems with running things direct from SQL Agent,
but you could define a command-line task which call OSQL to run the script.

I would not use DTS - but that is because I have never used DTS, and
don't know about it. DTS might very well be the right thing, but you
may get better help in microsoft.public.sqlserver.dts with DTS questions.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

I got it to work using DTS once I found the right task (Transform Data
Task). Now I need to figure out how to call a DTS package from a .sql
script that will be run in query analyzer. Is that possible?

There is also a send mail task which I'd like to use to send a email
notification to these people where database (b) is located to let them
know that there has been an update to the financial data on their
server.

Tom Dauria (tdauria@bu.edu) writes:
[quoted text, click to view]

Only by firing off dtsrun from xp_cmdshell, I guess. Or if there is a
COM interface, you could use the sp_OAxxxx functions.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at