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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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