I am setting up a DTS packet that looks at a view on one SQL Server
instance and uses a query to insert data, in a table, on another SQL
server instance. Anyone, know of a way to do this?


Thanks

If I understand you correctly then you can use as the SourceSQLStatement
of the DataPump task (Use a Query instead of a table object) the query
you want

SELECT <col list> from VIEW

You set up one connection object as the Source SQL Server and another as
the destination SQL Server.

Allan

"Tim" <Timothy.Juarez (AT) Ipaper (DOT) com> wrote

Okay,

I looked up datapump task through the help files and found nada. Here
is what I am trying to do:

1. Open a view to get the recordset in the view. The view resides on
instance SQL1.

2. Take said recordset and use a particular field for a conditional ie.
if recordset is null, then run an insert query saying, no records. Else
insert information, from the above view on instance SQL2. The Two
instances, actually reside on the same server.


Thanks for giving me a hand on this.

Timothy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Have a look at this article then, although dealing with text files it
does give an introduction to DTS.

Building a Package in the DTS Designer
(http://www.sqldts.com/default.aspx?278)



"Timothy Juarez" <timothy.juarez (AT) ipaper (DOT) com> wrote

I set up the packet for the Data Pump Task but I am not directly
dropping the information in, this is more of what I what to do.

Insert into SQL1.database1.dbo.table1( field1, field2, field3, field4,
field5)
Values (constant1, SQL2.database1.dbo.table1.field1, getdate(),
SQL2.database2.dbo.table2.field2, SQL2.database2.dbo.table2.field3)


So far, I haven't seen a way to do this yet.


Timothy Juarez


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

OK

You do not want to do it like this.

Try this

On "SQL 2" you create a view which does exactly this

" constant1, SQL2.database1.dbo.table1.field1,
getdate(),SQL2.database2.dbo.table2.field2,
SQL2.database2.dbo.table2.field3"

You then setup a connection to SQL 2 and use this as the source. Set up
a connection to SQL1 and use this as a destination. In the Transform
Data task you use your view as the Source Statement.

Make sense?

Allan




"Timothy Juarez" <timothy.juarez (AT) ipaper (DOT) com> wrote

Yep, makes perfect sense.


Thanks for all the help.


Timothy Juarez


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!