5

This is a add on to this question that was asked previously with no answer.

The problem I have is a need to occasionally update a set 35 children SSIS packages with one parent. They are all the same, differing only in what data they process. When I make a change, I delete all the children and paste them again in the same folder, updating the value of a variable that tells the package which child package it is so it knows which data to process (has a value 1-35).

My goal was to find a solution that allows the packages to somehow be aware to who they are (by file name, variable, configuration, etc) so that it would cut down on maintenance and setup up for production after a update.

The file names of the package keep the appended numbered value after the paste (packagename 1, packagename 2,....packagename X) in the same folder. I am using package deployment in SSIS 2012, so I don't have access to the file name as a parameter like would if I were using project deployment. All of the packages are in a SSDT solution with a parent package calling all 35 children. With Package Deployment, I'm using configurations in a SQL table to change the file path as its promoted from server to server.

I'd love to automate other things related to the children, but I can't unless I get this part solved first. Also, I need to add another 15 children or so and this would save a LOT of time.

Any help is appreciated

Community
  • 1
  • 1
Jason R.
  • 379
  • 1
  • 6
  • 19

2 Answers2

0

Have you tried to use environment variables? And start the packages with diffrent parameters. Packages_with_Parameter_from_Environments

(Sorry I am not allowed to comment.)

tom
  • 15
  • 9
  • Parameters are for project deployment, I'm using package deployment. If I had project deployment, I could use the filename parameter to solve the problem. Unfortunately, project deployment isn't a option for me. Thanks! – Jason R. Mar 24 '16 at 17:08
0

update a set 35 children SSIS packages with one parent. They are all the same, differing only in what data they process.

It seems like you shouldn't be using 35 different copies of the same package as a child and instead should just use parameters to fix the problem.

If the way they are processed is in the filename, you can use a filename parameter with a mask to pull out the variables using a for each loop, feed those parameters into the package being called. If not, you can store the processing options in a sql table, load those with file name and parameters and have that contain all your information and have the parent package pull that information out and use it to call the child packages.

xenapan
  • 87
  • 7
  • I think there is slight misunderstanding. Im processing data in a sql table with a script task. To make the process go faster, I divide the list of things to process up between the children packages. Those children packages are what has the file names Im referencing. When I update the packages, i have tell the package what number it is so it knows what section of the list it needs to process. Im trying to eliminate the numbering of the packages. – Jason R. Jul 20 '16 at 23:03
  • Yes.. and to do that, im saying instead of putting the file names in the package, you have the parent package split them up to each of the 35 children via a control table. Don't tell them within the package. Parameterize it in the child packages, have the parent pass them that info. – xenapan Jul 22 '16 at 14:24