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!
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.|||
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?
|||
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?
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|||
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 
No comments:
Post a Comment