New to SSIS 2012? You may find the below info helpful in your journey through SSIS 2012 (which has seen some major changes from earlier versions).
This is the first in the series of articles that I plan to cover over the next couple of months on how we can deploy SSIS 2012 Projects and also how to execute them. I will try my best to churn out one article every week or two, fingers-crossed 🙂
This article would explain in detail about Creating the SSIS Catalog. (The next article would focus on the types of deployment in SSIS 2012 and also provide detailed steps on how to deploy an SSIS project to SQL Server).
Creating the SSIS Catalog:
Note: Use Windows Authentication (and not SQL Server Authentication as we cannot Create Catalog under SQL Server Authentication)
Step 1. Once you are logged into SQL Server Management Studio, Connect to the Database Engine as shown below.
Provide the Server name and set the Authentication to Windows Authentication
Step 2. Once logged in, right-click Integration Services Catalogs and select Create Catalog.
Step 3. Select ‘Enable CLR Integration’ checkbox and provide a password for the Catalog.
Note: Make sure to keep this password at a secure place because if you migrate and move the Integration services catalog to another SQL Server Instance, you can restore the key to regain access to encrypted content.
Step 4. You can now see that a new SSISDB Catalog is created under Integration Services Catalogs.
Step 5. Right-Click SSISDB and select Create Folder.
Step 6. Provide a Folder Name in the Create Folder dialog box. Optionally, enter a Folder description.
Step 7. Once done, you can see that a new folder by the given name is created under SSISDB.
This newly created folder has 2 sub-folders by default: Projects & Environments (which I will explain later in another article).
That’s it. Now you have created the SSIS Catalog where you can deploy your projects.
In my next article, I will provide detailed steps to deploy your SSIS project using the newly introduced Project Deployment Model (in SSIS 2012). Till then, stay tuned… 🙂