Cannot find SSISDB Catalog when trying to execute SSIS Package programmatically

Question

So I've been trying to execute an SSIS Package from my SharePoint Event Receiver for this past 2 days and almost went crazy.

The SSIS Package is deployed into Integration Service Catalogs in my dev environment (the SQL Server 2014 and its Integration Services is installed on the same machine as the SharePoint Server and my VS2013 Community Edition). Successfully open SSISDB Catalog from SSMS

Here is my code so far, basically following steps in this blog -> http://microsoft-ssis.blogspot.co.id/2013/01/call-ssis-2012-package-within-net.html

connString = @"Data Source=PIRSRV03;Initial Catalog=master;Integrated Security=SSPI;";

                using (SqlConnection sqlConnection = new SqlConnection(connString))
                {

                    IntegrationServices integrationServices = new IntegrationServices(sqlConnection);

                    PackageInfo myPackage = integrationServices.Catalogs[integrationServiceCatalog].Folders[integrationServiceFolder].Projects[integrationServiceProject].Packages[integrationServicePackage];

                    Collection<PackageInfo.ExecutionValueParameterSet> executionValueParameterSet = new Collection<PackageInfo.ExecutionValueParameterSet>();

                    executionValueParameterSet.Add(new
                    PackageInfo.ExecutionValueParameterSet { ParameterName = "ExcelFilePath", ParameterValue = TempDirectory, ObjectType = 30 });

                    long executionIdentifier = myPackage.Execute(false, null, executionValueParameterSet);

                    ExecutionOperation executionOperation = integrationServices.Catalogs["SSISDB"].Executions[executionIdentifier];

                    // Workaround for 30 second timeout:
                    // Loop while the execution is not completed
                    while (!(executionOperation.Completed))
                    {
                        // Refresh execution info
                        executionOperation.Refresh();

                        // Wait 5 seconds before refreshing (we don't want to stress the server)
                        System.Threading.Thread.Sleep(5000);
                    } 
                }

Everything goes fine until the line of code where it accessed the catalog SSISDB and throw an error, after a little debugging session I figured out that the integrationServices object didn't have any catalog, by observing the Catalogs.Count property which in this case equals to zero.

Is there any hint on why this happens?

Any help would be so much appreciated, Thanks!

It's using integrated authentication, who is running the package? You or have you run this from something like SQL Agent? — billinkc
I don't know why your code doesn't work, but the best way I have found to programmatically launch packages is to create a job that runs the package, and programmatically start the job. — Tab Alleman
@billinkc Yes, it turns out to be the permission problem of the account that responsible of running my code; the SharePoint Service account. Thanks for your help! — Windu Risky Adiatma

Windu Risky Adiatma Windu Risky Adiatma · Accepted Answer · 2016-01-08T11:46:08

After some hours spent trying to figure out the problem with the help from my DBA, it turns out that the SharePoint Services account that runs my Event Receiver does not have the access to my integration services catalogs, hence the zero count of catalogs.

All we did to solve this was just giving sysadmin permission to the SharePoint Services account.

Hope this helps someone some hours, thanks!

Cannot find SSISDB Catalog when trying to execute SSIS Package programmatically

1 Answers