Abstract
I have a client that has already started migrating existing SQL Server environments to SQL Server 2012. Thus, this article will demonstrate creating a catalog and catalog folder using a Script Task component.
Requirements
Article
We begin by launching Microsoft Visual Studio and creating a new Integration Services Project which is located under Business Intelligence Projects category.
After you have assigned a project name, proceed to click and drag the Script Task into the Control Flow pane from the toolbox.
Right-click the script task and click on “Edit”
Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2010”.
In Project Explorer, ensure the following references are added:
System;
Microsoft.SqlServer.Management.IntegrationServices;
Microsoft.SqlServer.Management.Smo;
Back to the code window, ensure that the following namespaces are declared:
using System;
using Microsoft.SqlServer.Management.IntegrationServices;
using Microsoft.SqlServer.Management.Smo;
After the above declarations, proceed to create an instance of SMO Server to server “SQL12-PC”
Server SMO_DisHBI = new Server(\”SQL12-PC\”);
Create an instance of MOM – Managed Object Model class
Initialize MOM with SMO Server
IntegrationServices Is_DisHBI = new IntegrationServices(SMO_DisHBI);
//Create a catalog and assign a password.
//Ensure that the catalog name is “SSISDB” as per SQL Server 2012 RTM guidelines
Catalog sS_Vct = new Catalog(sS_Is, \”SSISDB\”, \”password\”);
sS_Vct.Create();
//Create a catalog folder and assign a description
CatalogFolder sS_Vctf = new CatalogFolder(sS_Vct, \”selectSifiso\”, \”Description of selectSifiso folder created via Script Task.\”);
sS_Vctf.Create();
Conclusion
It’s that simple!
Cheers.
Sifiso.