March 16, 2015 at 3:46 pm
Hello, SQL newbie here. I've recently built a package in Visual Studio 2012 that connects and updates a SSMS database, producing output files from the data. This all runs fine when I run the package in VS. However, we are wanting to automate the process, so I wanted to run this via SQL Server Agent by creating a job, and scheduling it to run at different intervals. Following various sites, I was told to click on Integration Services Catalog in SSMS and create the SSISDB catalog, as the Integration Services Catalog is showing that it's currently empty on the SSMS (no drop down/plus box). However, when i attempt to create the SSISDB catalog, it gives me this error: The database file with name 'SSISDB' already exists in directory 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'. Make sure there is no existing database file with the name 'SSISDB' under this directory and try again. (Microsoft.SqlServer.Management.IntegrationServices). Now, i've followed the file path and there is indeed a file there, but removing deleting it breaks my DB connection/messes up my project. As you can probably tell by the above, i'm very inexperienced with SQL, and i'm unsure what I need to do to get the SSISDB to actually show up in the Integration Services Catalog folder on SSMS, so that I can automate the package process. Thanks for any insight you can give.
March 17, 2015 at 9:18 am
Removing it breaks your project? Are you developing directly on the SQL server, if so I'd highly discourage that.
I'd say look in Object Explorer under databases and see if there's an actual SSISDB attached, if there is, then when you right-click 'Integration Services Catalogs' the Create Catalog option should not be enabled. Maybe someone manually created an SSISDB database, i.e. not through the Create Catalog wizard, and if so you'd have to check with whomever to see if there's actual data and then backing it up before dropping it and recreating through the Create Catalog wizard.
Also, as a side not, you can optionally deploy to the msdb database (SQL Server) instead of an SSIS Package Store. When you choose "Save Copy of 'package' as" from VS, there are 3 package locations, SQL Server saves to the msdb system database. Packages there can then be viewed in SSMS by going to Registered Servers / Integration Services, selecting a SQL Server, and navigating to Stored packages \ MSDB.
MCSA SQL 2014
March 17, 2015 at 10:13 am
Thanks so much! Your comments really helped. It appears that the SSISDB had been set up previously but the file was corrupted, so it was not showing on the SSMS like it should have been. When I went through the process to build it, it would "check" for the file and find it, and deny me the ability to build it. I cleared the file and now it's working perfectly. The comment about it breaking my project was my own stupidity in not reconnecting to the server after I removed the file lol. Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply