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

  • 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

    Monday, February 13, 2012 6:09 AM

All replies

  • 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

    Tuesday, February 14, 2012 3:40 AM
  • 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

    Tuesday, February 14, 2012 9:18 AM
  • 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

    Tuesday, February 14, 2012 3:46 PM
  • 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

    Wednesday, February 15, 2012 5:19 AM
  • 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

    Wednesday, February 15, 2012 3:39 PM
  • 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

    Thursday, February 16, 2012 4:58 AM
  • 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

    Tuesday, February 21, 2012 3:46 PM