How to get Package Properties Deployed under Catalog in SQL Server 2012(Denali)?

Question

I found two methods to retrieve and load packages deployed to sql server 2012(denali);

1.        LoadFromSqlServer
2.        GetPackageInfos

Both these methods load packages from “msdb”, But we need to load packages that are deployed under catalog; I tried specifying path(Project Name+ Folder Name) in “Folder Name”\”Package Path” parameter, But I got following error message:

What I found here is, It always look for packages deployed under msdb, I did not found any way to load packages deployed under catalog.

Can any one here provide his\her suggestion on this?

---

-Aman

Answer 1

Hi Aman,

PowerShell can be used to poll SSIS catalogs, I have not tried myself so here is a link that will give you an idea:

 http://blogs.msdn.com/b/mattm/archive/2011/11/17/ssis-and-powershell-in-sql-server-2012.aspx

---

Arthur My Blog

Answer 2

Hi Arthur,

Thanks for your perfect response, I clearly given me a direction, i am very close to the solution for my problem, Now I am able to retrieve packages deployed to Catalog, But the PackageInfo (Microsoft.SqlServer.Management.IntegrationServices.PackageInfo) class does not expose the property(LoggingMode) of packages for which i was looking, Then I check PackageInfo (Microsoft.SqlServer.Dts.Runtime.PackageInfo) Class, which exposes LoggingMode property, I tried converting object type, but that is not possible,

Is there any other solution here that you can suggest?

Thanks,

Aman

---

-Aman

Answer 3

You need to use

Microsoft.SqlServer.Dts.Runtime

like this:

Package pkg = //Set the package;
// get the logging mode
Console.WriteLine("The LoggingMode is: {0}", pkg.LoggingMode);

---

Arthur My Blog

Answer 4

foreach (Catalog catalog in isserver.Catalogs)
            {
                foreach (CatalogFolder folder in catalog.Folders)
                {
                    foreach (ProjectInfo prjInfo in folder.Projects)
                    {
                        foreach (Microsoft.SqlServer.Management.IntegrationServices.PackageInfo pkgInfo in prjInfo.Packages)
                        {
                            Microsoft.SqlServer.Dts.Runtime.Package pkg = (Package)pkgInfo;

                            Console.WriteLine("The LoggingMode is: {0}", pkg.LoggingMode);

Error:
Error 13 Cannot convert type 'Microsoft.SqlServer.Management.IntegrationServices.PackageInfo' to 'Microsoft.SqlServer.Dts.Runtime.Package' 

I am not able to convert\set package here.

---

-Aman

Answer 5

Microsoft.SqlServer.Dts.Runtime.Package pkg = (Package)pkgInfo;

is wrong use

 foreach (Microsoft.SqlServer.Management.IntegrationServices.Package Crnt_pkg in prjInfo.Packages)
                        {
                               Console.WriteLine("The LoggingMode is: {0}", Crnt_pkg.LoggingMode);

---

Arthur My Blog

Answer 6

But there is no LoggingMode property exposed in Microsoft.SqlServer.Management.IntegrationServices.Packageclass, Where as it is exposed in Microsoft.SqlServer.Dts.Runtime.Packageclass, That is why I am trying to convert the package type.

---

-Aman

Answer 7

Then how about:

 foreach (Microsoft.SqlServer.Dts.Runtime.Package Crnt_pkg in prjInfo.Packages)
                        {
                               Console.WriteLine("The LoggingMode is: {0}", Crnt_pkg.LoggingMode);

?

---

Arthur My Blog