looking for ways to validate SSIS package configuration/property settings

Question

looking for ways to validate SSIS package configuration/property settings

barry.andreasen

Newbie

Points: 3
More actions
December 16, 2015 at 7:18 am

#303298

I am coming up to speed on SSIS packages for use in a product solution that I am involved with. We have defined a number of packages that will be part of the product package, each requiring a small set of configuration settings that will be specific to each client's installation (connection string, processing properties, etc.).
My problem is that since these settings are client specific, we cannot provide them with the packages and need to work with the client to perform this configuration manually (using a scripted tool or through any of the SQL tools) however, we don't know if the values are correct until we run the package. This is resulting in lots of time spent going back to find and correct configuration errors (usually incorrect data, typos, etc.), so I am trying to figure out if there is a way to simply validate the configuration separately at config time to be able to identify these errors up front. Ideally this would also be possible from our scripting configuration tool.
I see lots of discussion about using 'Validate' method to validate the package processing, but I really only need to validate the configuration settings. Can someone point me to resources to help implement this?
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247206 More actions · Answer 1

barry.andreasen (12/16/2015)
I am coming up to speed on SSIS packages for use in a product solution that I am involved with. We have defined a number of packages that will be part of the product package, each requiring a small set of configuration settings that will be specific to each client's installation (connection string, processing properties, etc.).
My problem is that since these settings are client specific, we cannot provide them with the packages and need to work with the client to perform this configuration manually (using a scripted tool or through any of the SQL tools) however, we don't know if the values are correct until we run the package. This is resulting in lots of time spent going back to find and correct configuration errors (usually incorrect data, typos, etc.), so I am trying to figure out if there is a way to simply validate the configuration separately at config time to be able to identify these errors up front. Ideally this would also be possible from our scripting configuration tool.
I see lots of discussion about using 'Validate' method to validate the package processing, but I really only need to validate the configuration settings. Can someone point me to resources to help implement this?
Thanks

If you are using the project deployment model and performing configuration via SSISDB 'environments', everything can be scripted (though the script may be a little complex ...).

barry.andreasen Newbie Points: 3 More actions · Answer 2

Phil Parkin (12/16/2015)
If you are using the project deployment model and performing configuration via SSISDB 'environments', everything can be scripted (though the script may be a little complex ...).

Sorry, I'm new enough that I think I don't know what I don't know (or what I need to ask to know more :-D)

Yes, we are using a deployment model. The configuration is being done with scripting directly to the SSISDB package properties using SQL (or in the case of our debugging using manual package configuration through SQL EM or similar tools).

The problem I've seen with client configuration is that I have no way of knowing if the property values entered/provided by clients for the configuration are accurate, and as a result, when they have errors, we don't know until long after the configuration has been completed (i.e. runtime).

At the moment our most common error is with the connectionString property. Since most clients are not SQL savvy, they often don't know how properly to setup the connection access accounts that we ask for. When we work with them to generate the connectionString, we just enter in the data we receive. I'd like to exploit the Validate method to allow this data to be tested while we are entering it so we can immediately test these settings.

Is there a similar 'Validate' method that can be used for the project configuration settings as well?

Thanks

Phil Parkin SSC Guru Points: 247206 More actions · Answer 3

barry.andreasen (12/16/2015)
Phil Parkin (12/16/2015)
If you are using the project deployment model and performing configuration via SSISDB 'environments', everything can be scripted (though the script may be a little complex ...).
Sorry, I'm new enough that I think I don't know what I don't know (or what I need to ask to know more :-D)
Yes, we are using a deployment model. The configuration is being done with scripting directly to the SSISDB package properties using SQL (or in the case of our debugging using manual package configuration through SQL EM or similar tools).
The problem I've seen with client configuration is that I have no way of knowing if the property values entered/provided by clients for the configuration are accurate, and as a result, when they have errors, we don't know until long after the configuration has been completed (i.e. runtime).
At the moment our most common error is with the connectionString property. Since most clients are not SQL savvy, they often don't know how properly to setup the connection access accounts that we ask for. When we work with them to generate the connectionString, we just enter in the data we receive. I'd like to exploit the Validate method to allow this data to be tested while we are entering it so we can immediately test these settings.
Is there a similar 'Validate' method that can be used for the project configuration settings as well?
Thanks

There are two deployment models: package and project. You are certainly using one of them 🙂

As you mention SSISDB, you are probably doing project deployment.

You should be able (with some significant coding) to create a script which extracts:

1) Details of SSISDB environment variables and their values, and

2) Details of the mappings from the environment variables to the package connections and parameters.

Armed with this info, along with knowledge of the SQL Agent job which calls the package(s), you should be able to work out what config settings are being used.

There is no OOTB way that I know of to easily validate this stuff. And given the flexibility offered by SQL Agent to modify parameters/variables/connections/environments at runtime, I can understand why!

barry.andreasen Newbie Points: 3 More actions · Answer 4

Phil Parkin (12/16/2015)
There are two deployment models: package and project. You are certainly using one of them 🙂

sorry - typo - omitted the project deployment model 😀

Thanks again.