none
Where is SSIS package stored?

    Question

  • I have used Import-Export wizard to import some data from a flat file into a db table. When prompted, I have chosen to store the package in SQL Server. The package is executing correctly, scheduled to run (job) once a day...the problem is that I can not see this package in EM and I do not know how to open it in BIDS (since i can not point to a certain file). I can see the package in msdb sysdtspackages90 table, but nowhere else in EM - under management->Maintenance Plans I can see the packages created for db maintenance, but not the one created using import-export wizard. I can se the GUID in the job (command line tab) but the search for the GUID # does not yield any results. The searc for .dtsx does not show this package either. Where is it? If I wanted to open and edit this package with BIDS, how would I do that?

    Thanks in advance!

    Thursday, July 20, 2006 9:05 PM

Answers

  • Since you chose to save the package in SQL Server - The Package will be saved in the MSDB Database in SQL Server.

    You can access the package from SQL Server Management Studio.

    1) Select 'Integration Services' in the connection option instead of 'Database Engine'.
    2) Expand Stored Packages - Expand MSDB and search for your package (With the same name which you used during saving the package.)

    In order to manipulate the SSIS Package - You need to right click on it and Export to a .dtsx file and Open the DTSX file in BIDS / SQL Server Management Studio.
    After modification - import the package again to MSDB database by right clicking on the MSDB folder in SSMS (Integration Services) and selecting Import Package option.

    Thanks,
    Loonysan

    Thursday, July 20, 2006 11:46 PM
  • Actually, you may modify the package without export it to dtsx file. This is what you need to do:

    • Start "Integration Services" on the server.
    • Strart BIDS and open a SSIS project.
    • Click 'Project' in mamu bar and select 'Add existing package' from the pull_down.
    • In the pop-up window, type in the server name, and set the right package path. Then you will be able to work on the package directly.

    With this way, you may keep the package you change always on server.

    Friday, July 21, 2006 2:40 PM

All replies

  • The SSIS package is stored in the file system as XML files.

    If you need to schedule this, you can simply create a SQL Server agent job and point your (package).dtsx and a config file if you have one.

    But, it cannot be managed from the Management Studio environment if that is what you are asking. The solution can be opened and worked on using the BIDS env.
    Thursday, July 20, 2006 9:44 PM
  • Since you chose to save the package in SQL Server - The Package will be saved in the MSDB Database in SQL Server.

    You can access the package from SQL Server Management Studio.

    1) Select 'Integration Services' in the connection option instead of 'Database Engine'.
    2) Expand Stored Packages - Expand MSDB and search for your package (With the same name which you used during saving the package.)

    In order to manipulate the SSIS Package - You need to right click on it and Export to a .dtsx file and Open the DTSX file in BIDS / SQL Server Management Studio.
    After modification - import the package again to MSDB database by right clicking on the MSDB folder in SSMS (Integration Services) and selecting Import Package option.

    Thanks,
    Loonysan

    Thursday, July 20, 2006 11:46 PM
  • Yes! Thanks Loonysan, this is exactly what I needed...
    Friday, July 21, 2006 1:59 PM
  • Actually, you may modify the package without export it to dtsx file. This is what you need to do:

    • Start "Integration Services" on the server.
    • Strart BIDS and open a SSIS project.
    • Click 'Project' in mamu bar and select 'Add existing package' from the pull_down.
    • In the pop-up window, type in the server name, and set the right package path. Then you will be able to work on the package directly.

    With this way, you may keep the package you change always on server.

    Friday, July 21, 2006 2:40 PM
  • Ok guys, now when I try your suggestions, I am getting a following error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    Login timeout expired
    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
    Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Native Client)

    ------------------------------

    Login timeout expired
    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
    Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Native Client)

    I have checked and Integration service is running, named pipes and shared memory enabled, but I am able to connect to the pipe using osql - I get an error that SQL server does not exist, or access denied. Actually, if I try to use osql I am getting the same error.

    Every suggestion will be mostly appreciated.

     

    Friday, July 21, 2006 3:48 PM
  • Actually, a copy of the package is downloaded and stored locally. When editing a package this way, you are not editing it on the server. You'll need to save the package back to the server to see the edits.

    K

    Saturday, July 22, 2006 4:42 PM
  • I have a slightly different, but similar, question. When I schedule a package to run, it asks me where the package source is: SQL Server (i.e. msdb database), File system (.dtsx. file), or SSIS Package Store.

    What and where is this "SSIS Package Store"?   (I thought the Package store was a generic name that referred to either the SQL Server or File system option. Apparently that is not the complete story.)

     

     

    TIA,

     

    Barkingdog

     

     

     

    Sunday, July 23, 2006 10:40 PM
  • I can connect to Integration Services thru EM (named instance), but when I click on the MSDB folder, I receive same error referencing link 20476.  I already have SAC set to use Local and Remote connection (using both tcp/ip and named pipes).  Also need help !
    Wednesday, August 02, 2006 2:56 PM
  • The question is to AEGO in particular:

     

    Q1 Can you be clearer on this..I did not get the steps. Am a beginner in Sql Server 2005 so pls be bit more detailed on how to edit the package without exporting it to .dtsx file.

     

    Q2: Whats the difference b/w storage location: "SSIS package store" and "SQL Server". One of my package was giving an error in "SSIS package store" but not in "SQL Server".

    Wednesday, December 19, 2007 10:01 AM
  •  

    MSDB.sysdtspackages90
    Thursday, March 20, 2008 2:28 PM
  • I have also made a package in Integration services but now I want to open it in BIDS but it is not opening nor any error is coming.
    I am opening bids and click project-->add existing package..
    Monday, July 06, 2009 8:38 AM