none
having troubles deploying SSIS packages to a named instance for SQL 2005

    Question

  • Hello,

    I have  a user that is having trouble deploying a SSIS package to a named instance in SQL 2005.  After he deploys it, he doesn't see the package when he logs into SQL 2005

    I added the named instance in the XML and restarted the sql service but to no effect.  Also, I am unable to find a guide/walkthrough on what to do when deploying SSIS packages to a named instance.

    Comments would be greatly appreciated.

    Thanks,

    Tuesday, July 02, 2013 10:43 PM

All replies


  • If you have deployed the packages as a SQL Server, then in the Specify Target SQL Server page you would need to give the named instance of the server where you want the packages to be deployed say TESTSERVER\INSTANCEA. You can then log on to it from SSMS by selecting Server Type as "Integration Services" in "Connect to Server" dialog box. You will then find the deployed packages.

    Check link for SQL Deployment.



    Regards,
    Karthik

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, July 03, 2013 7:32 AM
  • Wednesday, July 03, 2013 4:08 PM
  • OK, the user was able to deploy the package but he is unable to log into the instance to see the package.

    This is what the user does:

    1.    Run SQL Server management studio

    2.    Choose Integration Services in the drop down

    3.    Then pick sv400 even though the sql server 2005 is on sv400\SQL5 but when I browse for more it only give me sv400 as an option.

    4.    At this point it only gives me the ‘Windows Authentication’ option since it’s grayed out.  When you choose Database Engine you get to choose. 

    5.    When I choose Stored packages, MSDB and click on + I get the message below.  I think since it goes to sv400 and not sv400\SQL5 which is the SQL Server 2005 instance then SSIS is not available there.  We need to see why I can’t get to sv400\SQL5 integration services.

    ----

    I guess the issue now is how to connect to the correct instance to confirm if the package has been deployed properly.

    thanks,

    Wednesday, July 03, 2013 5:56 PM
  • Please follow these instructions:

    http://msdn.microsoft.com/en-us/library/ms137789.aspx

    In the config file, replace:

          <Name>MSDB</Name>
          <ServerName>.</ServerName>

    With:

          <Name>MSDB</Name>
          <ServerName>Servername\InstanceName</ServerName>

    Wednesday, July 03, 2013 7:44 PM
  • Hi,

    Previously, I edited the config file in C:\Program Files\Microsoft SQL Server\90\DTS\Binn to include the instance name but I had a leftover ".".  I have removed that now and restarted the SQL Server Integration Services Service.  Are there any other services which I should restart for my changes to take effect?

    Thanks,

    Friday, July 05, 2013 9:51 PM
  • Its a config file and the changes would get affected even without a restart. Are you able to see the packages now under MSDB?

    Regards,
    Karthik

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, July 08, 2013 6:16 AM
  • Hello,

    First I would like to thank you all for all the help in this thread.  We finally got the the package to appear on the instance now.  Before it was not showing up in the instance.  The user went to Tasks and export the data into a package, this method is different from the method in the MSDN article.  We then tried to run the package but it cannot run because of a permission issue with the SA account.  Even though there were no errors when exporting the data using the SA account.

    I suggested the user to follow the method in the MSDN article and we can troubleshoot again.  But he is going on vacation soon (of course..) and I won't be able to troubleshoot with him until the end of the month.

    I consider the question in this thread resolved (since we can find the package but now it is a permission issue) and I will post again when we are stuck.

    Thanks!

    Tuesday, July 09, 2013 10:10 PM