Setting “Work Offline” without Opening the Project

If you’ve done much work with SSIS, you’re probably aware that on opening a package in BIDS, SSIS validates all the objects in the package. This can cause the packages to open very slowly, particularly if it has connections to a database that is currently unavailable.

I recently needed to upgrade several older packages to SSIS 2008. Unfortunately, these packages were referencing a database that I no longer have access to. On top of that, there are a number of data flows in each package, all of which use this non-existent database. Opening a package in BIDS was taking more than 10 minutes, which is about 9 minutes and 55 seconds past the point where my patience runs out. Normally, this wouldn’t be that big of a deal. Once the project was opened, I would just set the Work Offline option (located under the SSIS menu in BIDS), which prevents the validation from running.

However, each package was in its own project (for reasons I won’t go into in this post, but primarily failure to plan ahead), so I was looking at a very slow and painful process to upgrade these packages.

Fortunately, there is a way to enable the Work Offline option prior to actually opening the project. Locate the BIDS *.user file associated with the project. For SSIS, this file should be located in the same folder as the project (.dtproj) file, and will have a filename like “<project name>.dtproj.user”. Open this file in Notepad, and you should see something like the following (it’s got a few additional tags in 2008, but the general format is the same):

<?xml version=“1.0” encoding=“utf-8”?>

<DataTransformationsUserConfiguration xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=“http://www.w3.org/2001/XMLSchema”>

  <Configurations>

    <Configuration>

      <Name>Development</Name>

      <Options>

        <UserIDs />

        <UserPasswords />

        <OfflineMode>false</OfflineMode>

      </Options>

    </Configuration>

  </Configurations>

</DataTransformationsUserConfiguration>

Locate the <OfflineMode> tag (in red above) and change the value from false to true. Now, when the project is opened, it will already be in Offline mode, so you won’t have to suffer through a lengthy validation process.