Cluster and User Defined Functions

Has anyone had difficulty with UDF's in a clustered environment? We have
some DTS code that references a user defined function. When we run the DTS
package on non-clustered servers, the DTS package executes in several
minutes. However, when we run the same code against the database in a
clustered environment, the package never finishes. Watching the proc
monitor shows fairly decent server activity, but just never returns.
Anyone?
Thanks
Rob Heyman
I'm not sure what the UDF could be doing that will only cause problems in a
cluster so maybe that is not the real issue. Have you tried tracing the
execution of the package under Profiler to see where the package execution
gets "stuck"? You should also check for blocking when the package is
running. Please see the following article for more info:
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

CLONING PACKAGES

I loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.

Are there any analogous solutions for SSIS? I cannot find anything.

My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don’t have to go manually through each of them to replace certain items such as stored procedure names, etc.

Thank you!

Besides copying the .dtsx file and editing the copy to suit your needs?|||You can create a program to generate SSIS packages. If you want to reverse your current packages into C# code, take a look at http://www.ivolva.com/ssis_code_generator.html.|||You could also create a template for each type of load process and store them in the \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems folder. I think that's the correct place.|||Phil Brammer: Yes, I want to be able to change all hard-coded string dynamically. Assuming I use standard naming convention all object names will have the same structure. All I want to do is loop through table names and replace appropriate strings within the existing template package to generate new packages on the fly.|||jwelch, that's an interesting solution. I'll try to play with you and let you know if it works. But it seems it's what I need.|||

LoveDanger wrote:

Phil Brammer: Yes, I want to be able to change all hard-coded string dynamically. Assuming I use standard naming convention all object names will have the same structure. All I want to do is loop through table names and replace appropriate strings within the existing template package to generate new packages on the fly.

Using expressions, you can build just ONE package to do this though.

Provided the structures of all of the tables (both sources and destinations) are all the same... That is, you can dynamically adjust the tables the OLE DB sources and destinations go against.|||Thanks Phil, this should work. I assume the transformation part would be tricky (I'm not sure how to map all the columns dynamically), but I'll check if that could be done. Also, I believe the solution posted by jwelch (http://www.ivolva.com/ssis_code_generator.html) should work if one still wants to generate numerous packages.

Thank you all!
|||Note: My solution works if you are looping through tables that have the same structure. If they don't, then my solution won't work. Once you build the package (mappings and all) you can start using expressions to dynamically change the table names.|||Yeah, in my case we have different set of attributes for each dimension that I'm not sure how to handle. I used your approach with BCP though - use only one package to transfer data from a number of flat files to SQL Server tables. Though you still have to generate format files for each that pretty much translates your column mappings.|||

LoveDanger wrote:

I loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.

Are there any analogous solutions for SSIS? I cannot find anything.

My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don’t have to go manually through each of them to replace certain items such as stored procedure names, etc.

Thank you!

Sounds to me like you want to use templates. Matt explained where to drop them elsewhere in this thread.

-Jamie

|||

Well, I am not really sure how would templates solve my problem. I will still have to go through each package task and replace the names of the objects manually.

Does template allow you to do search and replace for the entire package?

|||

LoveDanger wrote:

Well, I am not really sure how would templates solve my problem. I will still have to go through each package task and replace the names of the objects manually.

It sounds as though you'll have to do that regardless of the solution though?

LoveDanger wrote:

Does template allow you to do search and replace for the entire package?

No, not really. You could open up the package's XML (right-click on the package and select 'View Code') and do a Find-Replace that way of that's what you want to do.

-Jamie

|||Thanks Jamie, I think this would be the easiest in my case. At least it will save time on going through each task manually.

Anastasia|||

LoveDanger wrote:

Thanks Jamie, I think this would be the easiest in my case. At least it will save time on going through each task manually.

Anastasia

Cool. Take a copy of the package before you alter it