DTS question

How do I use a global rowset variable in a SQL statement where a value should be IN ( ? )
where ? is mapped to the global rowset variableI have two different data sources.

I need to pull from source (1) to Souce (2).

The data pulled from souce(1) needs to be based on values in a table in source(2).

Select fields
from source(1)
where field1 in ( Table in source 2)

Is this possible with DTS?|||try this link
[BOL] Using Global Variables with DTS Packages
and

[BOL] Execute SQL Task look lower into the doc for "Populating a Single Global Variable with an Entire Rowset"|||I have the global variable (rowset) populated, but how do I use it in my where condition?

where field in ( ? ) I get an error when I map the parm to the global rowset variable.

DTS Packages in SQL Server 2000

In a Transact SQL Statement which is the fastest way of extracting the data using

Exists & In in a subquery

With Explanation Please.

I am not sure what you mean by extracting data using sub-query. In any case, it depends on the query plan. Most cases you will see the same execution plan for EXISTS and IN. You should write SQL code that is intuitive for the problem rather that worrying about the construct. For example, if you want to retrieve rows based on presence/absence of a row(s) in another table then use EXISTS since it is more natural to read. You can also compare the plans between the two queries and decide based on the performance.|||

Thanks for responding.

I will compare the plans.

But I would like to know if you have any experience on this. Or the experts users has any recomendations for this - Exists/In

Books On line recommends Exists

Thanks

sqlsql

DTS package and nested OPENQUERYs?

Is there a way to use nested OPENQUERY's in a DTS package??

I have the following SQL statement (whichs execute without error) that I would like to make a DTS package out of:

INSERT tbl_Sku SELECT AX.sku_id, AX.style_id, AX.style_color_id, AX.style_size_id, AY.color_id, AY.size_master_id from OPENQUERY("SVR-1",'SELECT sku_id, style_id, style_color_id, style_size_id FROM merch.dbo.sku') as AX, OPENQUERY("SVR-2",'SELECT sku_id, style_id, color_id, size_master_id FROM ma.dbo.sku') as AY Where AX.sku_id = AY.sku_id

The problem I am having is the connection object of the DTS package. It will not allow an OPENQUERY inside an OPENQUERY. How do I set up a third comnnection??Your statement does not have an OPENQUERY inside an OPENQUERY. It has two joined OPENQUERY statements.|||I guess I could just repeat the question that I was actually asking. I'm a programmer, not a dba, so my 'nested' terminology was wrong in the last post.

Does anyone have experience with setting up a DTS package to insert into the target table data from 2 other connections, where the connection inserting into the target table is extracting from yet a third connection?|||Either:
1) Your use of OPENQUERY is unnecessary in a DTS package, since you can just set up a connection object within the package for each remote server.
or...
2) Your use of a DTS package is unnecessary, since you could just run this statement from a stored procedure through the Job Scheduler.
The best advice I can give you on DTS is put as little logic as possible into it. It is a quirky utility that is a hodgepodge of poorly integrated technologies. Use it to load data into staging tables, and then perform your logical processing on the data using stored procedures.|||Well, the dbs's at this contract want me to use DTS packages since all the existing stored procedures I am converting into DTS packages do nothing more than truncate the target table and insert data into it.

I already have them running about 15 times faster (taking 15 minutes what was taking almost 4 hrs using sp's) so I want to finish creating a DTS package out of the last 2 sp's I am converting since it is doing the exact same thing and I need it to run at the conclution of the sucess of other processes.

The issue I am having is that I do not know how to use 3 connections to do this. I have one table that is being loaded from tables on two different servers. Although they are currently linked servers, I want to be able to run the existing query using a DTS package. I just do not know how to set up the connections.

I found some references to transformation lookup and was wondering if anyone reading this has done this before.|||By the way, let me just say I have no problem setting up 2 connections and running a query, but I am having problems getting data out of two connections and inserting it into a third connection, which is the target table.|||I already have them running about 15 times faster (taking 15 minutes what was taking almost 4 hrs using sp's)Then likely the sprocs were poorly written.
all the existing stored procedures I am converting into DTS packages do nothing more than truncate the target table and insert data into itAlso a questionable practice.
I don't know all the details of what you are doing, but whether by choice or by direction from the "dbs's" (dba's?), your design does not appear to be following best practices. Avoiding stored procedures in favor of heavy DTS processing too often leads to solutions that look like they were designed by Rube Goldberg, as you appear to be finding out.
I still recommend you DTS from both remote systems into staging tables and then perform your joined insert.
One question...did the original "slow" stored procedures happen to use either cursors or programmatic looping?|||Forget it, I'll try a different forum.

DTS Package

I have created a DTS package that connects to the database fills a temp table with data based on my sql statement, generates a text file with the data in the temp table, and after the file is generated the temp table to truncated. The package has been added as a job to run daily, but the problem is sometimes the job will not generate data in the text file. I get an email with the file attached as soon as the job ends, and if i see that no data is in the file I run the package manually, and for what ever reason the file will then have data. Can someone tell me why the job will sometimes pull data, and sometimes not. But manualy running it will always pull data.

Thanks in adavance for helping!!

Is it possible that the dependency for the text file generation is not quite 'right', and that the file is generated before the temp table is completed?|||Also enable the DTS package log to see the underlying events during the data administration.

DTS Global Variables

Hello,

In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.

I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?

BrianAre you saying that you need to use this string variable in your WHERE clause? If so, you should be able to use a question mark (?) where you need to use that string value, then use the Parameters button to tell DTS which global variable to use for the parameter.

Terri|||Hey,

That was correct. THanks.

Brian|||Cool! I have found DTS to be one of the most powerful yet frustrating tools to use. Could they make it any more obtuse? Took me 2 years to realize there was such thing as global variables, which then opened up a whole new world.

Anyway, Marcin Policht has written a nice series on SQL Server 2000 DTS. There are 12 parts to it, and part 5 is:SQL Server 2000 DTS Part 5 - DTS Designer Tasks and Global Variables. Definitely worth bookmarking.

Terri