I do not know that I understand the problem. You have a Query to
execute against DB2. You want to put the result of that query into SQL
Server. Something is parmeterised. So say

SELECT col1, col2 FROM TABLE WHERE col3 = ?

You can use the Transform Data task to do this.

You map a Global variable value to the ? By using the parameters button


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

The problem is that as soon as I use a global variable in a parameterized query I get an error "wrong number of arguments".

select col1, col2 from tbl1 where tbl1_pk = ? will give me an error.

I don't have any trouble using lookups in any of my transformations on other databases in the same package. Nor do I have trouble using lookups in this database, but I can't use any of the SQL tasks to pull a subset of data from the DB2 table. Looking up a single row/column/field isn't a problem.

What I'm trying to do is pull ~1500 rows on a daily basis and push them to the SQLServer database. If the ETL process doesn't run one day for whatever reason, it detects that by looking at a batch control table in the SQL. It then uses those parameters to build the external SQL, but as soon as I pass the properly initialized and populated variables to the parameterized query I get that wrong number of arguments error message.

If I use an ActiveX Script with the *exact* same SQL statement with the Global Variables it works just fine. It looks like the OLE DB parameter based functions between SQL and DB2 are losing track of parameters, or some other hiccup. I'm pretty sure I'm doing this correctly.

There's one thing I'm unsure of when using parameterized queries. I kind of rigged it to work the way I needed it, and maybe that's the problem. I'm using an IN statement in the query, and I have ActiveX scripts that build the Global Variable for that In statement in the format I would expect to write in SQL.

For example, I only want work done by particular individuals from the DB2 database. These individuals come from a person table in my SQL Server database.

My activeX script essentially builds a variable like this:

'Miller, Jon', 'Jones, Bob', 'Blow, Joe'

I then use that variable like this:

Select Name, Count(*) From Work_Done where Name In ( ? ) group by Name

Will that not work?

I also build the date parameter that way. The format I have to use in my DB2 database to specify a date range is '2004-04-16'. So I transform the SQL 04/16/2004 into '2004-04-16' and then that string is my parameter for this query:

select * from table1 where theDate = ?

Is that not the way to use it?

Thank you for your help, sorry for the long post.

-Brad

"Brad" wrote: