Offline Connection Managers


Today’s post is from Carla Sabotta, lead writer for the SQL Server Integration Services team.

——————————————————————-

The SSIS Designer in BIDS tries to connect to each data source that is used by your package to validate the metadata associated with sources and destinations. There may be times when you can’t connect to your data sources or the connection is slow. This slows validation and prolongs a package startup or opening a package data flow.

In previous versions of SSIS, you worked around this issue by setting the entire package to work offline. In SQL Server “Denali”, you can turn off the slow and unavailable connections by setting the connection managers to work offline. You configure a connection manager to work offline, by right-clicking it in the Connection Managers area of SSIS Designer and then clicking Work Offline.

clip_image002

When you set a connection manager to work offline, it will remain offline while the project is open. When you close the project, the connection manager is reset to work online.

For more information about troubleshooting package validation, see Troubleshooting Tools for Package Development in Books Online.

Comments (4)

  1. Ron Patrick says:

    It would be really nice if these blogs were updated to reflect the actual menus that exist in SSDT since it has been released.

  2. Jeff Schwartz says:

    Hi – thanks for the post – I was getting an error that the connection manager property offline was set to TRUE so my connection couldn't be made (that's not the actual error and I can't seem to recreate it).  The strange part was that in the SSIS menu for the connection manager (and the connection configuration being used) the "work offline" option was NOT selected.  I selected it, then unselected it and my problem went away.  

  3. Jeff Schwartz says:

    Hi – I got the error again – it is "The connection manager will not acquire a connection because the connection manager OfflineMode property is TRUE.  When the OfflineMode is TRUE, connections cannot be acquired".  When I do  my previously mentioned resetting of the "work offline" option the password for the connection seems to be changed because I get a login failed message when I try to connect.  Unfortunately when I refresh the password in the properties of the connection configuration it switches the connection back to offlinemode TRUE.  By the way – I'm trying to make a dynamic connection using variables.  Also, I'm very new to SSIS so I could be doing something dumb.

  4. Sumana says:

    Hi Jeff,

    I am facing the same issue as Password not getting saved after changing it and offline mode automatically gets set while changing the database server.

    Did you find any workaround to your issue?

    -Sumana