none
Package Configuration Problems SQL 2008 R2 RRS feed

  • Question

  • I have an SSIS package that is using a SQL Server configuration to store connection strings for the other connection managers used by the package.  The connection string for the connection manager used to get the SQL Server configuration data is passed to DTExec when the package is executed.  The SQL configuration sets the values of variables that hold the connection strings and the connection managers use the variables in an expression to set the connection string.  I'm doing this so that I can move the packages from dev to qa then to production. 

    I also need to have the child packages use the same connection strings as the parent package so each child package has a parent package variable configuration containing the connection strings.  It uses these to set the values of child package variables which are then used to set the connection string property of the connection managers using an expression.

    I tested my packages by connecting to the development SSIS server, clicking on the package and selecting run package.  I select the connection managers tab so that I can change the connection string of the connection manager that points to the SSIS Configurations table which contains the development connection strings.  This works great.

    I deployed my packages to the production server and again tested the packages by running them from the production SSIS server.  I used the connection managers tab to change the connection string of the connection manager that points to the SSIS Configurations table containing the production connection strings.  This works great too - the variables in the master package get set correctly from the connection strings in the SSIS Configurations table.  All of the connection managers are set to the correct production databases and all is good.  At this point, the packages are being executed by the SSIS server on my development machine.  The packages are being pulled from the production server but my machine is doing all of the work.

    I created a SQL Agent job on the production server to execute the SSIS package and I changed the connection string of the SQL configuration manager to point to the production database.  When the job runs, the packages are being executed by the production server.  The package fails.  I'm getting login timeout expired messages.  I tried changing the job to execute DTExec specifiying the connection string using /CONNECTION, this failed in the same way.  Admin is the first connection manager that uses the variable/expression to define the connection string.

    OnError,SERVERNAME,xxxxxxx\xxxxxxxxxxxxxxxx,Master Load,{3530D637-F831-45FB-861E-2706826A945C},{92B5E71E-CA4B-4A54-AF3B-E4FFC74C5775},9/10/2010 15:45:20,9/10/2010 15:45:20,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired".
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ".
    
    User:OnError,SERVERNAME,xxxxxxx\xxxxxxxxxxxxxxxx,Load FactIPLocation,{623FE48F-5127-481E-A9A7-AE0C5B90F32B},{92B5E71E-CA4B-4A54-AF3B-E4FFC74C5775},9/10/2010 15:45:20,9/10/2010 15:45:20,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Admin" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    
    User:OnError,SERVERNAME,xxxxxxx\xxxxxxxxxxxxxxxx,Load Facts,{09A3868D-1A74-4A9D-BE5E-08299C69C948},{92B5E71E-CA4B-4A54-AF3B-E4FFC74C5775},9/10/2010 15:45:20,9/10/2010 15:45:20,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Admin" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    
    User:OnError,SERVERNAME,xxxxxxx\xxxxxxxxxxxxxxxx,Master Fact Load,{D49C785D-77D8-477C-BA7F-3B0E386E4808},{92B5E71E-CA4B-4A54-AF3B-E4FFC74C5775},9/10/2010 15:45:20,9/10/2010 15:45:20,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Admin" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    
    OnError,SERVERNAME,xxxxxxx\xxxxxxxxxxxxxxxx,Execute Master Fact Load,{893BC339-64FF-42D0-BC1B-FB7B9E502DF4},{92B5E71E-CA4B-4A54-AF3B-E4FFC74C5775},9/10/2010 15:45:20,9/10/2010 15:45:20,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Admin" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    
    OnError,SERVERNAME,xxxxxxx\xxxxxxxxxxxxxxxx,Master Load,{3530D637-F831-45FB-861E-2706826A945C},{92B5E71E-CA4B-4A54-AF3B-E4FFC74C5775},9/10/2010 15:45:20,9/10/2010 15:45:20,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Admin" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    
    

    It looks like DTExec is using the /connection value to connect to the right SSIS config database but it is somehow failing to set the connection manager connection strings before attempting to verify the connections.  Reviewing the data, the connection strings in the connection managers do get set to the correct values at some point and I see data in the right databases.  It seems like SSIS is trying to validate the connection managers using the design time values of the variables - which contain the development server names.  These servers are not accessible from production.  When I execute the packages from my machine, it works ok - I can access both development and production servers from my machine.

    Does anyone have any idea what's happening and how I can prevent the login timeout errors? 

    Why don't I get the same errors running the packages from my machine?  The connection strings in the design time variables don't have passords and I don't save sensitive information so that should fail too.

    I'm using SQL 2008 R2 64bit enterprise edition in production and SQL 2008 R2 64-bit developer edition on my desktop.

    Is there any way to prevent SSIS from tring to validate the connection manager connection before applying the configuration values, if this is what is happening?

    Any comments or suggestions would be greatly appreciated.

    Thanks,

    Tim

    Friday, September 10, 2010 5:28 PM

Answers

  • Hi Arthur,

    Thanks for your response.  I found the cause of my problem.  The package is using a sql logging provider which uses one of the connection managers that I'm trying to set dynamically using the package configurations.  If I remove the SQL logging then the package runs without error.  I can also use the /CONNECTION option to specify the connection string for the SQL log provider and that works ok too.  It fails only if I try to set the connection string using an expression equal to a package variable.  I guess it must be something to do with the order in which SSIS access the log provider and when it populates the package variables from the parent package variables.

    Tim

    • Marked as answer by TimGreenan Friday, September 10, 2010 10:43 PM
    Friday, September 10, 2010 10:43 PM

All replies

  • I think a band-aid solution is to indeed disable the validation which is DelayValidation property you may want to set to TRUE.

    Let us know if that helps.


    Arthur My Blog
    Twitter Button
    Friday, September 10, 2010 7:47 PM
    Moderator
  • Hi Arthur,

    Thanks for your response.  I found the cause of my problem.  The package is using a sql logging provider which uses one of the connection managers that I'm trying to set dynamically using the package configurations.  If I remove the SQL logging then the package runs without error.  I can also use the /CONNECTION option to specify the connection string for the SQL log provider and that works ok too.  It fails only if I try to set the connection string using an expression equal to a package variable.  I guess it must be something to do with the order in which SSIS access the log provider and when it populates the package variables from the parent package variables.

    Tim

    • Marked as answer by TimGreenan Friday, September 10, 2010 10:43 PM
    Friday, September 10, 2010 10:43 PM