85

Argh! I created an SSIS package via an Import Wizard and I can't find the SSIS packages on the server using Management Studio. Execute an SSIS package doesn't appear as an option when I go into job scheduler, either.

Dylan Corriveau
  • 2,561
  • 4
  • 29
  • 36
Caveatrob
  • 12,667
  • 32
  • 107
  • 187

7 Answers7

95

When you start SSMS, it allows you to choose a Server Type and Server Name. In the server type dropdown, choose "Integration Services" and connect to the server.

Then you'll be able to see what packages are in the db.

Casey Williams
  • 4,045
  • 2
  • 24
  • 15
  • This was the answer, but I had a firewall issue that was blocking me from connecting from my local PC; on the server I could see everything just fine. – Caveatrob May 20 '09 at 17:39
  • 5
    @Casey Williams, Yes you can see a LIST of the packages, but how do you actually VIEW them? As in, what they contain / do, etc. – Chiramisu Sep 25 '12 at 20:30
  • 12
    @Chiramisu I just had to figure this out. Right-click on the package, select to export it to a file system, and then you'll have a .dtsx file. You can then open the file in Business Intelligence Development Studio as JohnIdol describes. – Mark Meuer Oct 09 '12 at 20:27
  • Yeah, that's right @MarkMeuer, as I found out later. I guess it wasn't as cut and dry as I thought it was, having not been exposed to DTS packages before. Now I know, thanks. ;) – Chiramisu Oct 09 '12 at 22:07
8

The wizard likely created the package as a file. Do a search on your system for files with an extension of .dtsx. This is the actual "SSIS Package" file.

As for loading it in Management Studio, you don't actually view it through there. If you have SQL Server 2005 loaded on your machine, look in the program group. You should find an application with the same icon as Visual Studio called "SQL Server Business Intelligence Development Studio". It's basically a stripped down version of VS 2005 which allows you to create SSIS packages.

Create a blank solution and add your .dtsx file to that to edit/view it.

JohnIdol
  • 48,899
  • 61
  • 158
  • 242
dm.
  • 81
  • 3
  • 2
    @JohnIdol Once you have the .dtsx file you can open it in Business Intelligence Development Studio by just selecting Open from the File menu. You don't need to create a new solution. – Mark Meuer Oct 09 '12 at 20:29
  • "SQL Server Business Intelligence Development Studio" is the right answer. – Pete Alvin Jan 26 '15 at 16:51
7

If you have SQL Server installed there is also a menu option for finding local SSIS packages.

In the Start menu > All Programs > 'Microsoft Sql Server' there should be a menu option for 'Integration Services' > 'Execute Package Utility' (this is available if SSIS was included in your SQLserver installation).

When you open the Execute Package Utility, type your local sql server name in the 'Server Name' textbox and click on the Package button, you will see your saved package in the popup window. From here you can run your previously saved package

Spyder
  • 3,784
  • 3
  • 26
  • 15
7
  1. Open SQL server Management Studio.
  2. Go to Connect to Server and select the Server Type as Integration Services and give the Server Name then click connect.
  3. Go to Object Explorer on the left corner.
  4. You can see the Stored Package folder in Object Explorer.
  5. Expand the Stored Package folder, here you can see the SSIS interfaces.
Naveen Kumar
  • 582
  • 2
  • 8
  • 25
6

If you deployed the package to the "Integration Services Catalog" on SSMS you can retrieve the package using Visual studio.

enter image description here

fintangillane
  • 91
  • 1
  • 5
  • 2
    Hey, can you clarify how to retrieve the package using visual studio? I have the same error and can find the SSIS package in SSMS but I can't do much editing from there – Kieran Sep 14 '20 at 00:05
  • 1
    @kieran you can export the package to a folder, then the exported .dtsx file contains xml and can displayed or edited with VisualStudio or other editor – Daniel Perez Feb 19 '21 at 11:04
-1

Came across SSIS package that schedule to run as sql job, you can identify where the SSIS package located by looking at the sql job properties; SQL job -> properties -> Steps (from select a page on left side) -> select job (from job list) -> edit -> job step properties shows up this got all the configuration for SSIS package, including its original path, in my case its under “MSDB”

Now connect to sql integration services; - open sql management studio - select server type to “integration services” - enter server name - you will see your SSIS package under “stored packages”

to edit the package right click and export to “file system” you’ll get file with extension .dtx it can be open in visual studio, I used the version visual studio 2012

Code T
  • 61
  • 3
-3
  1. you could find it under intergration services option in object explorer.
  2. you could find the packages under integration services catalog where all packages are deployed.
Bidy Ray
  • 21
  • 4
  • 2
    I don't see here how your answer adds any information better than in the 6 other ones including an accepted one. – odalet Apr 22 '20 at 10:41