How to set source and target externally.

[jambu]

I haven't done too much DTS recently and mostly used the wizards before so I would be grateful for some help on this.

I have to make changes to the source and target of an existing set of packages, one main one that calls two others, one for the extract and one for the load/transformation. Each of these packages consist of calls to over a dozen other packages.

All the Packages use a source/workflow copy/target layout and I will have to go through each one and change their properties if I want to change the databases used.

Is it possible for me to set the source/target at the top level package and then have all the other packages use the same?

 

[Catadmin]

Not that I'm aware of. You'll have to do something to actually change each package's connection.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[gradley]

Not that I'm aware of either unless you are using UDL files for your connections.

You may want to consider changing the packages to use UDL files going forward. Another consideration is to use the "Dynamic Properties Task" to soft code external files and directories.

Sorry, I know that this doesn't solve today's problem but it could prevent you from having to go throught this in the future.

 

[yksvaan]

It is possible to relay information with Global variables, and change the source / dest databases with Dynamic Properties Task. But there is no easy way, you have to modify you packages quite a lot and know what you are doing.

How about saving packages as vb files, search/replace database names and compile to vb programs ?

BTW, gradley, is UDL enough, don't you still have to change source/destination objectnames in DataPump Tasks ? Or something like disconnected-edit those so that they do not include databasenames. Any way, good point frmo you to use udl-files.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[jambu]

Thanks for the replies, I thought it would either be impossible or involve a lot more coding than I want to do at the moment.
I will look into the udl and vb options later though.

 

[gradley]

Thanks yksvaan!

UDL is useful if you only need to change the database name or other connection string values, since you can just change it in the UDL file. When using UDL connections in DTS, you should always check the "Always read properties from UDL file" checkbox. That way the latest connections are brought in at run time.

I agree with yksvaan that saving as VB files would be your best option. You can then scan and replace as necessary. The vb option actually saves the DTS in a format that allows the recreation of the package after changes are made. Be careful with this though, because it creates a workable but pretty ugly workflow design for the new package.

Good luck with this!