0

I'm trying to find the code used for a Maintenance Plan SSIS package that is stored in SQL server as a SQL Server Integration Services Package. I've tried logging into the Integration Services on the server, but it returns an error that it failed to retrieve data for this request. Unfortunately I wasn't the one that set up this package, so I'm not sure where the original files are or if they even exist at this point.

I need to find the dtsx file or something similar so that I can determine what steps are being performed and modify them as necessary. This is on SQL Server 2012 and the package is being referenced from the SQL Server Job Activity Monitor.

Thank you in advance for any help you can provide.

Xiphoen
  • 33
  • 1
  • 6
  • Just checking - are you sure the login you are using has the needed permissions to access the SSIS packages / log in to the SSIS server? At what stage do you get the error? What is the exact error message? – Laughing Vergil Dec 14 '16 at 16:48
  • I receive the error the moment I try to connect to the server. The exact details are: Cannot connect to [servername]. Additional information: failed to retrieve data for this request (Microsoft.sqlserver.management.sdk.sfc) connecting to the integration services service on the computer [servername] failed with the following error: "The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.". – Xiphoen Dec 14 '16 at 16:54
  • This usually indicates that SQL Server Integration Services are not running on the SQL Server you are attempting to connect to. You may need to start the services before you can access the code. Alternately, SSIS could be running on a different server. If this is a scheduled job, look at the job to identify what server is running the package. – Laughing Vergil Dec 14 '16 at 16:59
  • While maintenance plans are SSIS packages, they are some weird variant that when I opened with Visual Studio, all of the tasks showed as the generic "Task". That said, [extracting SSIS packages](http://stackoverflow.com/a/8197227) from msdb is covered – billinkc Dec 14 '16 at 17:17

1 Answers1

0

First in the job located in SQL Agent in SSMS, go to the step where the type is SQL Server Integration Services.

The general tab will give you the location. What is the package source? If it's the file system, the file is located on the server you are logged into via SSMS and at the specified package destination.

If the source is SQL Server, you will need to log in to the panel there. If it's SSIS Package Store, you will use windows authentication.

This link will help provide more information on the location of the package. From there, you will have to edit it in visual studios.

Shaulinator
  • 206
  • 3
  • 9
  • The package source is SQL server and has a location under the package tag that indicates in the SSIS packages\Maintenance Plans folder. "you will need to log in to the panel there" - I'm not sure what this means other than trying to log in through the object explorer, which is giving me an error about not being able to retrieve data (referenced in the comment above) – Xiphoen Dec 14 '16 at 16:59