10

Getting the error below while trying to create a catalog in sql server 2014 integration services. Any idea what I missed in installation or anywhere else?

The catalog backup file 'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak' could not be accessed. Make sure the database file exist, and the SQL Server Service account is able to access it(Microsoft.SqlServer.IntegrationServices.Common.ObjectModel)

  • I always check if the SSIS Service ("SQL Server Integration Services") is installed and running in Services.msc (in Windows), if not, install it, if already installed but stopped then Start this service. – Eddie Kumar Oct 4 at 14:30
14

I decided to see what happens when you actually click Create catalog in SSMS. There are a host of steps that are performed but the basic premise is that a backup exists at

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak

and as part of the catalog creation, that backup is restored to make the SSISDB.

The pathway under Program Files is going to be protected to prevent accidental of the files in there. The error you are encountering indicates that the person running SSMS does not have access to the file at C:\Program Files\Microsoft SQL Server\120\DTS\Binn

Assuming you're not using runas or something like that to launch SSMS, try opening Windows Explorer (not Internet Explorer) and navigate to that folder. If you can get there, you might have to click Yes to something like "show me the files, I know I'm in a restricted area", then I propose UAC is affecting your ability to install SSISDB.

If that is the case, close out all your SSMS instances. Then, right-click on SSMS and choose "Run as Administrator" That should prevent the authorization popup from tossing a spanner into the works when the catalog installation is happening.

How file system permissions became fouled up is an issue for another site (SuperUser or sysadmin)

I stumbled upon this Connect item that indicates if you're using a different version of SSMS than the underlying SQL Server you've connected to (SSMS 2014 pointed at 2012 installation), the creation can fail. So, verify your 120 (SQL Server 2014) matches your instance. The other possibility in there "To resolve it, I simply ran the SQL Server 2012 setup from the installation media and added the Integration Services feature."

14

I had this problem and it wasn't related to user permissions. I had done an earlier install of SQL server and chose to install many options except for 'Integration Services', so I ran the SQL Server Installation Centre application again and this time chose to install the 'Integration Services' feature. I think that the reason I could see the 'Integration Services Catalogs' node in SMSS despite not having installed 'Integration Services', was due to choosing to install 'Master Data Services' during my earlier SQL Server install.

8

If you're still stuck, be sure to check that:

  1. Integrations Services is installed on the Instance you are working with
  2. If you have a previous SQL version installed - check that you are using the matching version of SSMS to connect to your Instance

SSIS for various reasons is version specific and offers limited to no backwards/forward compatibility. It is important to treat SQL versions as separate products and use the VS and SSMS versions that were installed with your instance.

During the installation it has been my experience that minimum version requirements can offer some backward studio compatibility for Visual Studio. During a 2014 Enterprise install I noticed it linked back to VS 2010 instead of installing a newer version. However, I still had to connect to SQL using SSMS 2014 in order to create a catalog, SSMS 2012 would not work.

  • I had the same issue trying to access SSIS 2016 with SSMS 2012, not good. With SSMS 2014 it works fine. – user3752281 Feb 17 '17 at 11:47
6

Just solved that issue myself, your SQL Server is missing a few features, that's why SSISDBBakup.bak file is missing, it should be under:

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak

Install the SQL Server Integration Services feature and it will work.

  • I did that and give me more problems when I try to create folder. I find out I just have to reinstall the SSIS and it is resolved now. – AbuTaareq Dec 1 '16 at 19:45

protected by Martin Smith Jun 8 '16 at 15:06

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.