Hello,
i want to get data from a table, but only the data that is specified
(dynamically) with a filter that is stored in a local table.

SQL-Statement
SELECT * FROM external_table1
INNER JOIN internal_table2 ON table1.datafield1 = table2.datafield2

How do I get this in my dts-package if i only can have one reference to
external_table1?

Thankx.

Frank

On Wed, 29 Jun 2005 22:59:10 +0200, muc80339 (AT) mnet-online (DOT) de wrote:

Upload your local table to a #temp table?

A number of ways really but here is my preferred way

Use an ExecuteSQL task to get those filter values and put them into Global
Variables.

With those variables if your source provider accepts it you can now use the
values in the SourceSQL Statement property of the datapump task by using
this method

1. Use ? as a placeholder in the statement (select ... FROM ... WHERE col =
?) and using the parameters button map onto the ? , a global variable

If not then you can still do this

1. Use an active Script task to dynamically build the SourceSQLStatement.

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


<muc80339 (AT) mnet-online (DOT) de> wrote

I will try today with the global variable, I think the upload is not
possible because the source system is a SAP System.

"Ross Presser" <rpresser (AT) NOSPAMgmail (DOT) com.invalid> schrieb im Newsbeitrag
news:j0zm98knmzl2.1gmlhcmwi7w56.dlg (AT) 40tude (DOT) net...

Thnx.
I did as you described, but I have now the problem that my Table contains
more than one value.
What do I have to do with:

SELECT.... WHERE col IN ?

It reports Access Violation...

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You can't do it like this

You will need to build up your statement then if you want to use IN

Here is an article theat will get you cooking

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Frank Mayer" <frankmayer (AT) gmx (DOT) de> wrote

Hello,
thanks.

Problem is still:
sSQLStatement = "SELECT PSPNR, PSPID, POST1, POSKI, POSID FROM PROJ WHERE
PSPID IN (SELECT Filter_Projects FROM Flt_Proj)"

OR

sSQLStatement = "SELECT PSPNR, PSPID, POST1, POSKI, POSID FROM PROJ WHERE
PSPID IN ('" & DTSGlobalVariables("FilterProject") & "')"
The GlobalVariable contains a rowset of a SQL Statement that is executed
before...

Errors ( I Think)
Possibility 1 the sub-query does not contain data --> no datarecords chosen
Possibility 2 Error-Message Type mismatch



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote