Microsoft gave SSIS a complete makeover with the 2012 release. Most of the changes were cosmetic in nature – pun intended. The biggest change with SSIS 2012 is deployment to the new SSIS Catalog.
In earlier versions of SSIS you had an overwhelming number of decisions to make. Should the packages live on a file share or in MSDB? Should your configurations go in an XML file or maybe in a SQL Server table? How will you protect sensitive information? What about a using framework or third party tool to make managing SSIS easier?
Starting with SSIS 2012, you can forget all those decisions by using the new Project Model deployment and the SSIS Catalog. OK, I admit that switching your existing SSIS projects to the new model is going to take some planning and work, but the end results will be worth it.
The SSIS Catalog is the one place where you will manage SSIS projects and packages, including the configuration and monitoring. If you take a look at SSMS, you will see a new folder, Integration Services Catalogs. The catalog is not set up by default, you will have to create it. Even though the name is plural, at this time, you can create just one catalog.
To create the catalog, right-click Integration Services Catalog and select Create Catalog. You will be asked to enable CLR integration and to enter a password for encryption. If you don’t want your to enable CLR in your environment, you can always create an instance just for SSIS.
Once you click OK, you will see an SSISDB icon and an SSISDB database.
If you are so inclined, you can take a look at the tables and views in the new SSISDB database. The SSIS Catalog has built in logging and reports, but you could create your own reports if you wish.
Once the catalog is in place, you can deploy your projects, and they’ll show up in a folder under the catalog. From here, you can set up environments, map parameters, and run packages, but those are topics for another day.
How the SSIS project will identify the newly created Integration Services Catalog, SSISDB.
The project will not really need to identify the SSIS catalog. You can change the model from Project Model and Package Model. Otherwise, you can deploy the project to any server where the catalog is in place.
IS there a way we could update the passwords of oledb connection managers in SSIS Catalog thru powershell or t-sql. If it’s possible, could show some basic examples.
I haven’t tried anything like that. The passwords will be encrypted, so I doubt it.