Can I offer something else..

A trigger to do the work could easily cause unnecessary waits in the application. I would probably use the trigger to write an entry into...

Actually I think this is pretty simple..

Following the EXEC SQL task you have two success paths, one goes to the additional processing, the other goes to whatever process you will...

As someone new to SSIS I would not even THINK about dynamic packages.

A dynamic package is one created by code and then executed. This is a VERY advanced topic...

I would create templates for sprocs, views, function and provide them to the DB developers. These templates would include the drop then create as well as the GO statements.

I...

I have to agree the scale of these changes does appear to be out of line.

However, before we assume they are it is important t point out that...

Unfortunately you can't do it completely transparently. Keep in mind DNS ONLY addresses the IP address of the server, thats about 90% of your problem, you can get the...

No, I won't, sorry, you are STILL trying to re-invent the wheel. Add a variable at the package level and use the package configuration to set the variable, then...

I have to completely agree.

CEWII

The answer is that the machine that is running the package sits between the other two servers.

Also, why are you using the transfer objects task?

What are you trying to accomplish?

CEWII

You could use either a script component or an EXEC sql task to build the statement. I'd probably use the script component. I'm curious what is being used...

There is nothing built-in to do this. However this wonderful (sorry for the self agrandizing) tool can help:

http://ncldts.codeplex.com/%5B/url%5D

If you need assistance or have questions let me know.

CEWII

I have to agree with Steve..

CEWII

Great, thanks for the update.

CEWII

Can i ask a larger question then.. Why aren't you reading the DB2 data directly? Why this rube-goldberg contraption?

CEWII

Not too shaby.

CEWII