Hi all,

Apologies in advance for the long post, but I have been working on this for
several days and I'm at my wit's end. I have several DTS packages that
essentially just copy data from one database to another with some massaging
and de-normalizing here and there. These packages need to be database-name
independent -- I need them to work regardless of what the source/destination
databases are named. Therein lies my particular rub.

For the purposes of example, lets say I have a package that copies data from
MyTable in database MyTestSource to a table named MyTable in a database
named MyTestDestination. I've used a Dynamic Properties (DP) task to set the
DataSource and Catalog names in the Source and Destination connections for
the Transform Data task from some global variables. To make sure the DP task
runs before the transforms, I've added an On Completion workflow connection
between the DP task and the "source" connection for the transform task
(since transform tasks evidently can't have workflow connections
themselves). The transform task executes a stored proc to get the source
data. Both connections use Windows security.

In case my description has muddled things, here's what I'm talking about in
glorious ASCII art:

Dynamic ----> Source
Destination
Property Connection ---Transform (exec spd_proc1 ?)--> Connection

I've noticed that the package designer uses three-part object names
(database.owner.objectName) for source and destination table names, and that
these names do not change based on the Catalog name set for a connection by
the Dynamic Property task.

What I mean is, if I change my two connections with the Dynamic Property
task to use catalogs MyLiveSource and MyLiveDestination (respectively), then
the package copies data from MyTable in MyLiveSource (as it should) to
MyTable in the MyTestDestination database instead of MyLiveDestination
(huh?).

Best as I can tell after exporting the package as a BAS file, this is
because the destination table name is hardcoded as
[MyTestDesination].[dbo].[MyTable] in the Transform step. After taking a
closer look at the Transform step properties window, I can see that I'd have
the same problem were I not using a stored procedure for my source data.

Is this by design or a bug? Nevermind, I don't want to know. Instead, can
someone tell me, is there a fix or a good work-around for this behavior?
I've been googling and knowledge-basing without much luck.

Now, I've been working with a solution for this embedded three-part name
problem that involves using an ActiveX script task with an On Completion
workflow pointer to my DP task. The ActiveX task can look at the global
variable holding the catalog name and construct a new three-part name for
each destination table the package needs, and in turn stick these into
globals for the DP task to configure the transform. Again with the ASCII
art:

ActiveX Dynamic Source
Destination
Script ---->Property ---->Connection ---Transform (exec spd_proc1 ?)--->
Connection

The way I see it, I should be able to remove the catalog name (set it to
"(default)") from both connections, and still have a working package because
the script/dynamic configuration combination sets the connection databases
and transform destination table name from package globals.

Alas, that's not the case. When I run the package with both connections set
to "(default)", I get an OLE-DB error that the stored procedure defined as
the source for the transform can't be found. This gets written to the
package exception file:

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description: Could not find stored procedure 'spd_proc1'
(Microsoft OLE DB Provider for SQL Server (800004005): Syntax error or
access violation)
Step Error code: 80040E14

The message doesn't say where the OLE DB Provider is looking for spd_proc1,
but I do know that if I change the source connection from "(default)" to,
say "master", I get the same error. If I change the connection to point to
the my testing database (the only one at this point with a proc named
spd_proc1), then the package executes without error.

A msgbox in my script tells me that the script task is executing and the
globals are being set properly. This tells me that: The transform is
executing before the ActiveX script, the dynamic configuration isn't
working, there is more hard-coded information about the source embedded in
the transform, or I'm doing something dreadfully stupid.

If I am being stupid, please point out the error of my ways for me.
Otherwise, any help would be appreciated.

And if you got this far, thanks for reading!

--
Lamont Adams
MCP