I need to create this Transform Data Task:

SELECT ...
FROM <TeradataDB>
WHERE <TeradataDB>.customer IN (?)

The parameter needs to be: SELECT DISTINCT customer FROM <localDB>

The first problem is that the TeradataDB driver is ODBC, which does support
parameters. The second problem is that my parameter is going to be a dynamic
query from the localDB. I've read the article
http://www.sqldts.com/default.aspx?205 that talks about how to build a
statement using global variable with an ActiveX Script Task. Can the ActiveX
Script Task also make a query to the local database? And then parse it into
a comma-delimited customer list?

Thanks for any advice!



--
Disclaimer: This post is solely an individual opinion and does not speak on
behalf of any organization.

What part of it is going to be dynamic? The name of the DB?

OK so what I think you will need to do is this

The Transform Data task has as the Source a connection. For you to be able
to issue that Query as is you will need to have a reference (linked server)
between the two if the 2 DBs are on differing servers which I suspect they
are.

Now the cooler thing to do would be to retrieve the resultset from your
"SELECT DISTINCT customer FROM <localDB>" first and then make it into a
comma sep' list and then plug that into the statement. Yes this should be
doable.

ExecuteSQL task into a resultset.
ActiveScript task to read the resultset and make the comma sep' list
Same AX script task plug that into the statement for the DataPump task.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


[quoted text, click to view]

[quoted text, click to view]

Thanks Allan, I'll give that a try.

--
Disclaimer: This post is solely an individual opinion and does not speak on
behalf of any organization.

Allan, your suggestion worked perfectly. Thank you! I wasn't able to set up
a linked server, but I used the second suggestion. Here's the summary for
others' future reference:

1. Execute SQL Task - get customer list and store recordset in global
variable

2a. ActiveX Script Task - iterate through the recordset to build a
comma-delimited string of customers.

2b. Plug the generated SQL statement into the DataPumpTask.

--
Disclaimer: This post is solely an individual opinion and does not speak on
behalf of any organization.