May 14, 2020 at 1:18 am
We are at a client that is using SSIS 2014 File based packages, and we are in the process of deploying the packages to an integrated environment. We have packages that execute other packages (master with sub-packages) and we are running into issues in the environment because of the absolute paths. Our attempt at a relative path doesn’t work from .dtsConfig files (master package calls other packages by referencing absolute path in .dtsConfig file). We just wanted to get your thoughts around how to resolve this issue and what would be “recommended” approach. Also, the client won't let us use the Project Deployment Model.
May 14, 2020 at 8:08 am
we are in the process of deploying the packages to an integrated environment
What do you mean by that, as in deploying them to SSISDB? Are the packages all in the same project?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 14, 2020 at 8:41 am
we are in the process of deploying the packages to an integrated environment
What do you mean by that, as in deploying them to SSISDB? Are the packages all in the same project?
Client will not allow them to use SSIS Catalog.
but even with Catalog their issue of resolving the path to the package to call would still exist
To the OP - in order to allow "dynamic" path to the packages you need to use expressions to determine the path to the package to execute - using dtsconfig alone will not work - but dtsconfig can be used to store the base path to the package and that is then used, within the master package, to build the final path to the package to call
would be better if you told us what you attempted - if possible even with a sample master and child package being posted here
you can also refer to this link - may give you some ideas.
May 14, 2020 at 9:07 am
Client will not allow them to use SSIS Catalog.
Hadn't noticed that tiny, side note at the end (feels like it should have been made more obviously).
I never, however, understand these odd requirements. Why doesn't the client want you to use the better deployment method? SSISDB is a vast improvement over the older package deployment models.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 14, 2020 at 3:03 pm
The client does have this odd requirement to use package, not project deployment.
Also, the client does NOT allow environment variables. Strange, I know...
The dev environment is the developer's local machine. The dtsconfig has the file locations for the child packages. But the test and prod environments non-local, shared environments. Currently, we to copy, paste, and alter the config files manually for each child package. To make matters worse, the client won't give us the entire path for us to manually configure the locations of the child packages. So we need to find a way to derive paths of the child packages based on the location path of the parent package.
We going to try the expression approach. We might also experiment with some other approaches. What do you recommend?
May 14, 2020 at 4:23 pm
The client does have this odd requirement to use package, not project deployment.
You did say this in your initial post. Thom's question, however, was 'why is this restriction in place?'
We're looking for a technical reason, not "the client asked us to do it".
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 14, 2020 at 5:23 pm
Also, the client does NOT allow environment variables. Strange, I know...
Clients, managers, "the business" ask us to do odd things all the time, the impoprtant thing though is to recognise when those requests might have some merit (however odd) and when others are not good for the business. Maybe there is some "good" technical reason for not using "better" technology, but it does help us understand that. I really can't understand why they would say no to environmental variables too; they are very useful.
The main reason I've heard people don't want to switch project deployment is because it doesn't allow package deployment; which simply isn't true in SQL Server 2016+ and is becoming an excuse that I find is just "throw out" as a reason. Others because they "don't like it" but that's because it's different. Often those just need educating on the advantages of SSISDB and some additional learning and they come around.
So why is your client so against using what are core elements of SSIS on all currrently supported versions of SQL Server?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 14, 2020 at 6:15 pm
All I know is our firm begged, pleaded and debated with the client over this issue for weeks before asking me. I do not know the technical reason why, it might be more about policy than technology.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply