Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    26

    Unanswered: How to open a SSIS package stored on the Server

    Hi,

    I've migrate a DTS package to SSIS package, and store it on the Server. When I logon to Integration Services expand the Stored Package, MSDB, I can see the package but how do I open this package to make changes to it?

    Thanks.

  2. #2
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    You will need to open the package from Visual Studio, or Business Intelligence Design Studio. You cannot edit SSIS packages directly in Management Studio.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    What used to be 2 steps is now 10 to edit a package, I'm outraged...

  4. #4
    Join Date
    Mar 2008
    Posts
    23
    How do you execute the package? When you set up a job under SQL Server Agent will that see it some place? I can't figure out how that works. Integration Services doesn't seem to be set up at my site, but I was able to create and save an SSIS package on the server. Can I execute it now or is there a big piece missing that I don't have to do that?

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    bhavdahl, I am not an expert at this but you indicate that "Integration Services doesn't seem to be set up at my site,..." and this might be a problem. For those who don't now SSIS stands for SQL Server Integration Services.

    I would guess you used BIDS (Business Intelligence Development Studio) to develop the SSIS package. I am not sure if you need IS (Integration Services) installed or just BIDS to accomplish this (my guess is just BIDS).

    The next step would be to send the Package you developed to IS on the server. Once it is on the Server, you can set up a job to run it (it will know where to find it on the server, I believe).

    I don't think you can set up a job to run a package you have just developed in BIDS.

    That is about the end of my (very) limited understanding of the process. I will leave it to the real experts to guide you better (and correct anything I might have misstated).

  6. #6
    Join Date
    Mar 2008
    Posts
    23
    Thanks Stealth! But no, didn't use BIDS. Sorry, I should have mentioned that I was using the Import/Export wizard in SQL Server Management Studio. My problem is that when I try to set up a job none of the dialogs I can find seem to have any kind of list that includes the import "package" I just saved on the server using the wizard. I gave it a name, etc. But I don't see where to find that name anywhere on the server. So even if I need IS to edit it further, I don't really see how to even execute it after saving.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Okay, try this:

    Open Management Studio and Connect to the Server.
    Expand SQL Server Agent.
    Right Click on Jobs and select New Job from the pop-up menu.
    (I am skipping the General page to get to the Steps but you should fill it out as needed).
    Click on the Steps page (top left corner).
    Click on New
    In the TYPE drop down box, select SQL Server Integration Services Packages.
    On the General tab in the drop down box for Package Source, select SSIS Package Store.
    In the Server box type in the Server name (or use the drop down if it is available).
    In the Package box, click the box with the three dots (...).
    in the little Select an SSIS Package window, expand MSDB.

    Your SSIS package should be listed.

    This may not be total accurate depending on where the Import placed the package. you may have to choose a different option to allow you to find it.

    PS If you find it, finish fill out the Job information as needed.

  8. #8
    Join Date
    Mar 2008
    Posts
    23
    Ok, thanks! That confirms I was stumbling in the right direction probably at one point. I got to the part where I selected that type from the drop down in steps. I get an error message telling me that, "The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)". And I think things pretty much go south from there. I don't see anything for a Package Source after that. Any idea why I'm getting this error and where I would have to go, say on the server, to straighten things out?

    ...also, I couldn't connect to Integration Services from managment studio on my workstation either. I get an Access Denied trying to connect to the SSIS service on the server. But I can connect to the database engine. In order to do that I had to set up a login for my network ID under security on the server and I could get in from management studio. There isn't a security section under Integration Services to do something similar in. I could connect to Integration Services once I logged into the server and things worked there. So I need to set up access permissions on the thing somewhere. I need to be able to connect to it from my work station so I can import and export BIDS packages from and to my workstation, because I can't connect to my workstation when I'm on the server. ? (...not sure if I'm saying it all right) Anyway, can somebody help me out with that?
    Last edited by bhavdahl; 06-10-09 at 17:08.

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I have not yet migrated my DTS packages to SSIS (Installed DTS Designer for SQL 2000) on 2005, I'm in protest!. They say you can't create a DTS package with 2005, but you can, simply edit an existing package, and 'save as' and viola new package to work on.

  10. #10
    Join Date
    Aug 2010
    Posts
    26
    Also, there is a system table called "sysssispackages" in the MSDB. I was never able to expand the MSDB folder when logging into Integration services through SSMS, but I was able to access the pacakges this way (and delete them, which was my goal at the time).

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •