Please forgive me if my question is pre-emptive (as in, I haven't throughly researched this yet), but I'd like to know in advance the following question: Q. When scheduling SSIS jobs to run packages (in msdb), do I specify a configuration file in the job? Is this how one uses the configuration file? Or is the configuration file "configured" at the SSIS package level, before you schedule the job? Or both? Also, is there a good link on this topic? I'm already afraid I'm going to be confused! Thanks much!

sadie519590 wrote: Please forgive me if my question is pre-emptive (as in, I haven't throughly researched this yet), but I'd like to know in advance the following question: Q. When scheduling SSIS jobs to run packages (in msdb), do I specify a configuration file in the job? Is this how one uses the configuration file? Or is the configuration file "configured" at the SSIS package level, before you schedule the job? Or both? Also, is there a good link on this topic? I'm already afraid I'm going to be confused! Thanks much!A job (SQL Agent) is nothing more than a mechanism for executing DTEXEC.EXE. When you're in the designer, that's DTEXECUI.EXE - same thing but one's a GUI and the other is a command line tool. With that said, you can either setup package configurations at the package level or pass in a configuration via command line parameters (or via an Agent job). If you are using configuration files, as opposed to SQL Server based configurations, then the path you specify in the designer via "Package Configurations" needs to be available and valid when the job runs the package.

Also, be aware that if you specify a configuration at design-time, and you also specify one at runtime, the runtime configuration is applied in addition to the design time configuration. So, the order of events is: Apply all design time configurations Apply all runtime configurations

Help. If someone could just explain HOW configurations are useful for going btwn test and production environments that would be extremely useful. For example, what I want to do is have 2 configurations for a single package: 1.) one for test, 2.) one for production. That is, is there an easy way to "switch" from one configuration to the other? I would like to be able to "switch" from within BIDS and from a job. The package itself remains on the SAME server, it is not moving anywhere. However, I need to change the connections on the fly. Please tell me how I can do this. Thanks!!

You switch the configuration files. Keeping the same name.So, you might have three config files, one that the package points to and the two for each environment.myPackageConfiguration.dtsConfigmyPackageConfigurationTest.dtsConfigmyPackageConfigurationProd.dtsConfigSo you'd simply delete the myPackageConfiguration.dtsConfig file and copy either the test or prod file to that name. So you'd point your package to the configuration file that doesn't have an environment specific name in it.Does that make sense?

Yes, that makes sense. However, I still don't understand how to set up the configuration file! How do I know I'm creating it correctly? There are SO many checkboxes - how do I know which settings to pick?? I just want connections: flat file, database, ftp, etc Also, how do I create the separate TEST and PROD config files? When I use the configuration wizard, it just selects whatever settings are in the current package. So if I'm in a test package, how do I create the config file for PROD? I am very confused. Thanks p.s. I tried doing a sample config file - when enabled, my package no longer runs! This is not encouraging. When I disable it, it runs again. What am I doing wrong?

Phil Brammer wrote: You switch the configuration files. Keeping the same name.So, you might have three config files, one that the package points to and the two for each environment.myPackageConfiguration.dtsConfigmyPackageConfigurationTest.dtsConfigmyPackageConfigurationProd.dtsConfigSo you'd simply delete the myPackageConfiguration.dtsConfig file and copy either the test or prod file to that name. So you'd point your package to the configuration file that doesn't have an environment specific name in it.Does that make sense? I use Phil's approach on a lot of projects, and it is the easiest if you aren't switching environments a lot. If you are, use the /CONF switch of DTEXEC, and specify a path to a config file that overrides all the settings in the original config file. The easiest way to do this is to make a copy of the first file, open it in Notepad, and edit all the settings to point to the new environment. That way, you know all package properties specified by the original config will be overlaid by the new config. Oh, and if you are doing this, I recommend using only XML configurations and not SQL Server configurations. See this for the reasons why: http://agilebi.com/cs/blogs/jwelch/archive/2007/12/04/fun-with-ssis-configurations.aspx

sadie519590 wrote: Yes, that makes sense. However, I still don't understand how to set up the configuration file! How do I know I'm creating it correctly? There are SO many checkboxes - how do I know which settings to pick?? I just want connections: flat file, database, ftp, etc Also, how do I create the separate TEST and PROD config files? When I use the configuration wizard, it just selects whatever settings are in the current package. So if I'm in a test package, how do I create the config file for PROD? I am very confused. Thanks p.s. I tried doing a sample config file - when enabled, my package no longer runs! This is not encouraging. When I disable it, it runs again. What am I doing wrong? If you just want connection info, locate the connection managers in the package browser, expand them, and check the connection string property. To create seperate Test and Prod versions, copy the first one to a new file, and edit the settings in notepad. Repeat for each environment.

Ok, I tried doing this. When I run my package, it fails. For my OLE DB properties in the config fileI selected: Connection String Initial Catalog Name Password Server Name I get this error: [OLE DB Destination [26]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "myConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Why isn't this working?

sadie519590 wrote: Ok, I tried doing this. When I run my package, it fails. For my OLE DB properties in the config fileI selected: Connection String Initial Catalog Name Password Server Name Just tick the ConnectionString property. You don't need those others.

To clarify my issue, I have a very simple package that is just a Data Flow Task with a Flat File Source and OLE DB Destination. I have 2 connection managers, one for the flat file and one for the database connection. So far so good. In the Configuration Wizard, I want to create a config file for the Flat File Source and OLE DB connection manager. So,in the Config Wizard dialog,I select ALL the checkboxes for the OLE DB connection manager (don't want to miss anything!), and I selected "Connection String" only for the flat file source. Then I save the config file. Next I save and re-load the package. Here's the problem: 1.) If I attempt to run the package, it now hangs. 2.) If I attempt to "execute task" by right-clicking my data flow icon, I get a large grey pop-up window with a bunch of errors on it (too numerous to name here). I figured out I can make the error window go away by RE-ENTERING the password for the OLE DB connection manager (but WHY should I have to do this every single time??) 3.) Once I re-enter the password, the package still hangs. However, if I attempt to run the Data Flow Task by right-clicking it, it attempts to run but I get this error: [OLE DB Destination [26]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "myOLEDBConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. ????? What am I missing here?

Ok, I did as you said, still having the same problems. Please see my post "To clarify the issue..." It seems to be stripping out the password from my OLE DB conn mgr. Even if I re-enter it, execution fails with the error message I posted. My package protection level is "encrypt sensitive with password" if that makes any difference. However, if I disable the config file, everything works ok again. Somehow my config file is not set up right. Thanks

Again, just select ConnectionString for your connection managers. The rest of the properties just make up the connection string anyway.Right-click, executing a task will not load configurations.When you enter a password and check the save password box, the password is retained, even though when you come back later it looks as though it has disappeared. (So long as you are using some other ProtectionLevel than "DontSaveSensitive")Are you using Windows authentication to the database, or SQL Server logins?

It's a SQL Server login. And yes, the save password checkbox is enabled. Everything works fine UNLESS I enable configurations. Ok, I understand that right-clicking won't load the configuration, but then WHY does the execution fail if configurations are enabled? If I disable configurations, it works fine. Somehow the configuration is interfering with it working altogether. Also, as I mentioned, if I run the entire package it just HANGS, and does nothing. What is going on?

This is my configuration file OLE DB connection string: Data Source=myServer;User ID=mySQLLogin;Initial Catalog=myDB;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False; There's no reference to password here. Does that make a difference? Even if I check ALL the checkboxes, including "password" it still fails to connect to the database, so that isn't the problem anyways... What am I missing?

Ok, I seemed to have "resolved" the problem by taking the advice from another blog. Instead of checking the "Connection String" box, I only check "ServerName", "User Name", "Initial Catalog" Now it runs. Which makes me not really trust configurations if doesn't even work the way it supposed to. Because it is supposed to work if you select "Connection String." Is this another bug?

sadie519590 wrote: This is my configuration file OLE DB connection string: Data Source=myServer;User ID=mySQLLogin;Initial Catalog=myDB;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False; There's no reference to password here. Does that make a difference? Even if I check ALL the checkboxes, including "password" it still fails to connect to the database, so that isn't the problem anyways... What am I missing?Don't enable any checkboxes other than ConnectionString. FORGET about the others!Just add the password to the ConnectionString. "Data Source=myServer;User ID=mySQLLogin;Password=mySQLPass;Initial Catalog......."

sadie519590 wrote: Now it runs. Which makes me not really trust configurations if doesn't even work the way it supposed to. Because it is supposed to work if you select "Connection String." Is this another bug?No.

I have to add the password myself? Why isn't it included with ConnectionString?

Ok, I get that you want me to use the Connection String property only. But what no one has explained to me, is WHY doesn't this work when I use this?

sadie519590 wrote: I have to add the password myself? Why isn't it included with ConnectionString? Yes, you have to add it yourself. SSIS will not save passwords to external files - it's a security issue. If you want the password in the connect string, you have to add it yourself.

sadie519590 wrote: Ok, I get that you want me to use the Connection String property only. But what no one has explained to me, is WHY doesn't this work when I use this? Because the password wasn't in the connection string? Seriously, can you post the error message you are getting when you select only the connection string to store in the configuration and after you have added the password in the config file?

Yes, I'm sure the password missing is the problem. However, I was just surprised that I had to add it myself. I did not see this information on any of the sites I researched nor in my SSIS book. I guess everybody is using windows authentication?

sadie519590 wrote: Yes, I'm sure the password missing is the problem. However, I was just surprised that I had to add it myself. I did not see this information on any of the sites I researched nor in my SSIS book. I guess everybody is using windows authentication?For the most part, yes.Just know that SSIS will not save passwords anywhere. When you use EncryptSensitiveWithUserKey, it saves the password but it does so by encrypting it. If you were to view the package XML, you would not be able to see the password as entered. There is no way to retrieve a password out of an SSIS package. So, as such, when you create a configuration file, it doesn't put the password there. It's a security thing.This is a good read: http://msdn2.microsoft.com/en-us/library/ms137833.aspxSince configuration files are external to the package, the "sensitive" information cannot be encrypted/managed by the SSIS package and as such it is just left out.

jwelch, I am wondering if you can please clarify what you mean by using the /conf switch - that is, what context are you referring to - like running from a job? or a command line? Thanks

sadie519590 wrote: jwelch, I am wondering if you can please clarify what you mean by using the /conf switch - that is, what context are you referring to - like running from a job? or a command line? Thanks The CONF switch is one of the command line switches of the DTEXEC utility. Look here: http://msdn.microsoft.com/en-us/library/ms162810.aspx You can use DTEXEC to execute packages from the command line, from a batch file OR from a SQL Server Agent Job. Just specify the CmdExec (Operating System Command) job step type. This is the preferred way to execute SSIS packages from SQL Server Agent because it gives you more control and better error output.

What Matthew said You can also set the /CONF switch if you are using an Integration Services job step - if you specify a configuration on the Configurations tab, it results in a /CONF switch being appended to the command line that the Integration Services step uses behind the scenes.

So, it seems to me that the reason to use the /conf switch either on the command line or a job, is to OVERRIDE the design-time configuration file, yes? Otherwise, just let the package run its design time configuration file... or are there other reasons as well? Thanks

Not override - append. This switch allows you to specifiy additional configurations that were not specifiedduring package development, not change what is already included in the package.

Well, it may not be able to find the design-time configuration file, if it was located in a different directory. So you might use it in that scenario to apply the configuration from a different directory. The package applies the design time configuration first (assuming it can find it), then applies the configuration specified by /CONF on top of that. If you set the sameproperty in both the design time config and the /CONF config, the /CONF config will take precedence.

Although it can override... but you're saying not to use them this way?

See my previous post - it's fine to use them to override values. You just need to be aware that it does not prevent the design time configuration from being applied first.

Another thought on this - this will not force other configurations to reload. As an example, perhaps your design time setup is to use an XML config to get a connection string to a SQL Server db, and then all other configurations are SQL Server (based on the connection string from the XML file). If you attempt to override the override the initial XML config by using the /CONF switch, it won't work. The order of events is: Load the design time XML config, which sets the initial connect string. Load the design time SQL Server configurations. Load the runtime /CONF config, which updates the connect string. Run the package Nothing forces the SQL Server configs to be reapplied after the connection string is changed, so you end up with the design time config values. A little more detail here: http://agilebi.com/cs/blogs/jwelch/archive/2007/12/03/fun-with-ssis-configurations.aspx

There is now an utility available in CodePlex which can be used to batch update SSIS Package Configuration File paths without using BIDS: http://ssisconfigeditor.codeplex.com/