I have my packages set up to get connection strings from configs stored in a SQL Server database. This helps in moving things from dev to test to production. I have noticed that if for whatever reason the package cannot get to the config database to get its connection strings, it remembers whatever connections were in the package in Visual Studio when I deployed it and just goes with that. In my case, this is usually dev. So, I may have a package running in production that if it cannot get to the config database, it just runs anyway but uses the dev connections which can cause all kinds of problems, as you can imagine. I would want this package to just fail and not run if it cannot get the correct connection strings from the database. From the research I have done, it appears that this is how SSIS works, but does anyone know a way to trap the failure or something and cause the package to fail and not continue processing? Thanks.

Hello, Package will always read the configuration from Table and if it will not be able to access configuration due to connection break it will fail. But if you have connection string that is point to Dev in configuration ,it will never fail as entry is valid for configuration. So before you execute your package in production, make sure you have correct configured values in configuration tables. Thanks http://sqlage.blogspot.com/

Hello, Package will always read the configuration from Table and if it will not be able to access configuration due to connection break it will fail. But if you have connection string that is point to Dev in configuration ,it will never fail as entry is valid for configuration. So before you execute your package in production, make sure you have correction configured values in configuration tables. Thankshttp://sqlage.blogspot.com/

Have you tried keeping the connection strings blank (or otherwise purposely invalid) at design time? If you do that, you are then requiring that proper configurations exist and load properly in dev and prod... Talk to me now on

That does not appear to be true. The connections are somehow saved in the package even if you use configs. If the configs are in the table, that overrides what is in the package. If it cannot get it from the config table, it just goes with what is saved in the package. I have tested this with multiple packages.

Frankly, that does not seem feasible. That really complicates development. If you have package configs set up on your connections, when you open the package in VS, the connection info is populated. You would have to put invalid connection string data in your config table which means you would have to go into every connection and fix them every time you open it.

1) create package variables 2) configure the variable in the package configuration 3) using these variables , configure the connection managers in the package 4) change the package property DelayValidation to true so that all the validation will be carried out at runtime on changing the variables value in the packageconfiguration, the corresponding connection manager (configured using that variable) will also change and will point to the desired environment/server/database let me know if it helped

Can you elaborate a little? I don't quite understand. I am not having a problem getting the connection strings from the config database in each environment (dev,test,prod). That works, but if for some reason in prod, for example, the configuration cannot connect to the config db, it just runs anyway with a saved connection string in the package. That saved connection string is whatever was in the package when I had it open in Visual Studio the last time I had it open before I deployed it.

Let me guess: you run your package with SQL Agent, am I right? And the account that executes it has no right to access your config database tableArthur My Blog

I originally discovered the issue b/c of a permissions thing on the config db, but that's not really the issue. If for any reason the package can't get data from the config db, it just goes on anyway. I want to find out how to make the package fail if it can't get to the config db.

How are you configuring your connection string to the configuraton table in each environment? If you are not using an environment variable - having different configuraton tables per environment likely won't work (easily atleast)

Yes. I'm using an environ variable. But all that stuff works. It works right in dev, test, and prod just like I want. If I go change the value in the configurationfilter column in the config db just to make it "break" essentially. The package runs anyway. All the configs, environments, all that works. I'm just disturbed that if there is ever a problem, it won't fail.

Does setting "Fail the package on validation warnings" to true stop it from running?

Remove the design time settings from the package, store all the config stuff in the config file/tableArthur My Blog

How do I remove the design time settings?

Just do not use any key values hardcoded in your package, always use a config file/table. Then if you are not able to connect to your config table/file the package will fail. I suggest you use "Master" configuration approach as described here: http://consultingblogs.emc.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-Indirect-configurations-ROCK_2100_.aspxArthur My Blog

I use config values from the config table in all environments. When you open the file in Visual Studio when developing, the package connections get their connection strings from the dev config table. Then, when deployed the package gets its connection strings from the correct config table, but the dev connections are still stored in the package. So, if it cant connect to get the correct connection strings from the config table, it uses the dev ones which are stored in the package. As I understand it from my research and testing, SSIS packages store the connection strings in the package even if you have it set up to use configs. The configs when it runs overrides what is stored in the package. So, if it cant get them, it goes back to what is stored in there. It there is a way to get it to not store these connection string settings in the package and ONLY get them from the config table, that will be just what I need. I would've thought that is how it would work "out of the box", but that is not the case.

Does setting "Fail the package on validation warnings" to true stop it from running? Did you try this yet, the option is in the job configuration, IIRC missing configurations come across as a warning.

ok i assume that the connection managers in your package are configured using the package variables. keep the value of these variables empty. keep the DelayValidation property to true so that runtime validation will happen for the connection managers configured with these variables. Since you have your connection strings in tables, you need to use an ExecuteSqlTask at the beginning to populate those variables which are used to configure the connection managers. At the runtime first the variables will be populated with the correct Connectionstring, the when the control will reach the oledb source the corresponding connection manager will be configured with the Connectionstring present in the variables. As a result the connection manager will point to the server/database as per the value in the variable only during runtime.

Thanks. This may be something we want to try. But, it seems like a work around for using package configs for your connections, which is a "feature" of SSIS packages. Also, I'm unclear on how to develop the packages in Visual Studio without having some values in for the connection strings. The components in the package will error if there is not a valid connection to get the tables and columns, etc.

Have you tried keeping your connection strings invalid? When you debug your package, it will load connection strings from your configurations, so the package will debug properly. The only time you'll have any kind of issue is when you're actually dealing with Source or Destination components that need to retrieve table metadata. Talk to me now on

In development mode, there wont be any problem even if you execute the package with blank variable values. They will be populated at the runtime. The package property "DelayValidation" will take care about validating all the components at runtime itself.