Using Multiple connections in a DTS Package at the same time.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Using Multiple connections in a DTS Package at the same time.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-11 : 07:16:40

Pavan Madiraju writes "I was just wondering whether I can access two datasources at the same time in an Sql.

Following is the example
DELETE [DB2].TAB1 FROM [DB2].TAB1 T
INNER JOIN [SQL].DBO.TAB2 A
ON T.ENDORSE_ID = A.ENDORSE_ID

SQL is the sqlserver connection
DB2 is the db2 connection

I am trying to do a join between a sqlserver table and a db2 table. I have created two connection in the dts package.

I could not use two connections in Execute Sql task.

Thanks in advance for the help. "

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-12 : 11:24:20

You'll need to set up a link between the servers using sp_addlinkedserver and sp_addlinkedsrvlogin. Then you can do it with a connection to just one server. Look up the above SP's in BOL.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-12 : 13:00:05

BTW,

From BOL:

Accessing Package Data from External Data Sources
DTS Designer package data can be made available to an external source, such
as SQL Server Query Analyzer, by:

Querying the step with the Transact-SQL OPENROWSET statement.
Defining the package as a linked server and joining package data in a distributed
query. You make package data available by enabling the DSO rowset provider
option (on the General tab of the Workflow Properties dialog box) for a package
step. The data at that point in the package workflow then becomes available to
an external data consumer.

You should only enable the DSO rowset provider option for packages that you
intend to query; when the option is set, the package step where you set the
option does not complete execution. If you want to set up a package that both
executes and serves as a data source, you may need to set up a separate
branch in the package workflow to handle external queries.
-----------------------------------------------------------------------------
Note These methods are typically used to query packages from an external source;
however, you can also query other packages from within a package by issuing
OPENROWSET queries and distributed queries in an Execute SQL task.

Great so far... I had a try on it and got oops:

SELECT * FROM OPENROWSET
('DTSPackageDSO', '/Usa /P /S /NmyPackage', 'select *')

Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'DTSPackageDSO'.

What wrong I do? In myPackage I have only one SQL Server connection and
an Execute SQL Task with a simplest SQL statement: select * from a...

PS 'DSO rowset provider' option is checked.

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-14 : 03:58:05

Bad news (from MSDN):

When querying package rowset data, the following conditions apply:

The package supplying the data must be launched by the application
getting the package data. You do not execute the package to send the
data to the requesting application or process.

You can only query a package step associated with a Transform Data task.

The package must have destination columns to bind to; the destination cannot
be a text file.

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-14 : 13:11:31

My investigation is over... So, how Pavan should act:

1) create Connection1 (for SQL DB with the 'lookup' table);
2) create Connection2 (for DB2 database);
3) create Data Driven Query Task, in which:

a) on 'Source' tab choose your 'lookup' table;
b) on 'Destination' tab choose your table for deleting;
c) on 'Queries' tab choose 'Delete' from the Dropdown and write in
textbox below following code (of course, corrected one):

delete from tt where nn=(?)

Then click 'Parse/Show Parameters' button!! Now all is OK with
this tab;

d) go to 'Transformations' tab and click 'Delete' button. Then choose from
Dropdown 'ActiveX Script' item and click 'New' button. In popped up window
you'll see something like this:

Function Main()
DTSDestination("nn") = DTSSource("n")
DTSDestination("mm") = DTSSource("m")
Main = DTSTransformstat_InsertQuery
End Function

replace this code as following:

Function Main()
DTSDestination("nn") = DTSSource("n") ' n is lookup field in my case
Main = DTSTransformstat_DeleteQuery
End Function

That's all. Now you can execute your package.

PS Dear All, just imagine, I was searching for this trick for two days! I could
find nowhere a word on how to do it! Plz point out my possible mistakes in it.

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources