I have a simple package which transforms data from an excel
spreadsheet into
a table in my SqlServer 2000 database. The package contains two
connections, a
dynamic properties task, and a data transform task. I have setup the
workflow so that the dynamic properties task finishes execution before
the connection to
the excel spreadsheet is made.

I've setup the dynamic properties task so that it sets the Catalog,
DataSource,
user Name and password values as well as the Excel DataSource value
from
environment variables. The settings for the Excel connection seem to
take but
the values for the DB connection are ignored. When executed, DTS
complains that
the destination for the first data pump task is invalid. The
destination listed
is the default for the DB connection.

BTW the package is executed from a DOS batch file using the DTSrun
command.

Is it possible that the db connection is being made before the dynamic
properties task is executed?

Thanks.

Jeff

If the DP task is the first thing in the package and everything logically
hangs from it using workflow then it will be the first thing to fire.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Jeff Robinson" <jeff.robinson (AT) aquilent (DOT) com> wrote

Alan,


The DP task is the first. I have workflow defined
between the DP taks and the Excel connection. I can't
seem to create workflow for the DP - DB connection.
The DB connection is connected to the Excel connection
through a transform task. Is that good enough to ensure
that the db connection is loaded after the DP task
completes? Thanks.

Jeff



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes

A DataPpump exists between Excel --> SQL Server. The workflow will exist
between the Dynamic Properties task and the Excel connection.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Jeff Robinson" <jeff.robinson (AT) aquilent (DOT) com> wrote

Any idea why the properites aren't taking then?

The first data pump task complains that it can't find
the destination. And the destination is pointing to the
correct table in the wrong database.

Am I missing something here? Do I need to set any other
properties than the ones I mentioned in the original post?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

OK

So when you look at the destination in the DataPump task what does it say
after you execute the Dynam Properties task ?

Right Click on task | Execute

Set it to something outrageous.

I think what you will see is that the DestinationObjectName is 3 part
qualified

Dbname.ownername.tablename





--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Jeff Robinson" <jeff.robinson (AT) aquilent (DOT) com> wrote