I am relatively new to the SQL world and recently started managing a number
of SQL server. I have discovered DTS and find it a very powerful tool. I
have been working with it enough that I am finding myself needing the
variability that I can get from my VB scripts. I keep reading about this
great new functionality in SQL 2000 where you can uses parameters and Global
Variables to define different values for your source data in a Data
Transformation Task. I have followed all of the different directions I have
found (which basically boil down to putting question marks as place holders
and click Parameters and set corresponding Global Variables) and it is not
working. When I try to run the package, it is giving me an error as below:

Error Source: Microsoft JET Database Engine
Error Description: Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.

I am trying to pull data out of 4 access database tables into a single SQL
table. I have a dynamic value task that queries the SQL table for the ID of
the last value for each source database and set these values into Global
Variables. I then have four tasks that connect to each different source
access database and I try using the question mark to set the according
Global Variable.

I have not tried this functionality against other SQL tables as the source.
Is this a limitation that it doesn't work when querying Access? Right now, I
am trying to use some of the SQL 7 tricks to get this working.

Any help would be greatly appreciated.

Charles J. Palmer
cjpalmer at outsourceimsg.com

It sounds like the Access driver you are using does not support parameters.
I am using Office 2K and can use parameters.

There is a way around this and build the SQLStatement of the task before the
task itself.

Global Variables and SQL statements in DTS
http://www.sqldts.com/default.aspx?6,102,205,7,1

--

----------------------------
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



"Charles J. Palmer" <cjpalmer (AT) outsourceimsg (DOT) com> wrote

I don't think I have office installed on the machine that is running SQL, so
I must be just using the default driver and it must not support it. I got it
working on Friday using the old SQL 7 trick where I used VBScript to rewrite
the Source SQL, which is basically what you suggested. I knew that I could
do it that way, I am primarily trying to figure out why I am having to jump
through that hoop... Know what I mean. Thanks for your help.

Charles J. Palmer

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