SSIS SQL Server configuration uses design time connection string

0
votes

I have created SSIS package and scheduled it on SQL 2008 R2 server using SQL Server Agent. This package uses SQL Server Configuration. The SSIS package source is "File System". Under "Data Sources", I have selected connection and changed the connection string (results into /CONN option in command line). When I run the job, even though it succeeds, it creates error message that login failed and I believe that it is because somehow it tries to use design time connection string first, then uses the connection string provided in /CONN option and gets all the configuration parameter and executes it successfully.

If I change the design time connection string to prod connection string and then try to execute the same job, it does not log any error.

Does anyone know what's going on?

Update 4/17: I am using windows authentication to connect to the sql server. Design time connection string contains DEV server. DEV and PROD systems (SQL servers/users everything) are completely separate and do not have access to each other. SSIS package runs under service account which has access to all the PROD environments that SSIS package is referring to but does not have access to DEV environments. Here is how SSIS is configured. SSIS has only one sql connection which is used only for getting the configuration data from sql table. Configuration data is used to populate the variables which will be used in child packages.

Update 4/22: SSIS is using sql server configuration because child package's design time variables are different than what is in configuration. If SSIS configuration would not have worked, child package would have thrown an error as configuration data is used to populate the parent variable which in turn will be fed to child package variable.

1
sql-serversql-server-2008configurationssis
I don't think you are on the right track, which should be self-evident from your own test results. Try adding more context to your question e.g. what sort of data sources, how logon is supposed to work. – Mike Honey
You can install BIDS helper and check if the variables you are using in your connections are dynamically populated? – WaZ
To be certain, use SQL Profiler to ascertain for sure which database you are logging into and with which credentials. – Nick.McDermaid
I wonder if the login failed error comes from the "one sql connection"? From your description this doesn't appear to have any configuration. – Mike Honey

1 Answers

0
votes

Starting in SQL Server 2008 R2, design time configurations trump those from the command line. Notably, the set of actions taken during configuration is now different. First, the SSIS Package Configurations are applied. Then, command line options are applied. Lastly, the SSIS Package Configurations are reloaded and re-applied. This allows you to use the /Conn switch to change the ConnectionString property of one or more connection managers in your package. This helps redirect the retrieval of settings to a different SQL Server from the one originally used. At any rate, the reload and reapplication of settings in this manner means that there is no way to directly overwrite a design-time configuration setting. The best practice is to redirect the connection string that is used to retrieve settings. Or, simply do not set up an SSIS Package Configuration for the variable/setting in question, and always set it from the command line.

See: Behavior Changes to Integration Services Features in SQL Server 2008 R2

Review the section titled Understanding How SSIS Package Configurations Are Applied at Run Time: SSIS Package Configurations