Post reply

Connection to SSIS failing

  • July 28, 2009 at 7:55 am

    #134622

    I have a 2 node Active-Passive SQL 2005 64 bit Standard edition cluster with version:9.00.3042.

    Yesterday when I tried connecting to SSIS from Management Studio on Active Node,I got below error:

    Cannot connect to DBSQL.

    ===================================

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

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

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

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

    Program Location:

    at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)

    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

    at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    ===================================

    Connect to SSIS Service on machine "DBSQL" failed:

    Error loading type library/DLL.

    .

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

    Program Location:

    at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)

    at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)

    at Microsoft.SqlServer.Management.Smo.Environment.GetData()

    at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)

    at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)

    at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)

    ===================================

    Connect to SSIS Service on machine "DBSQL" failed:

    Error loading type library/DLL.

    .

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

    Program Location:

    at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)

    at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)

    Since then I found that the Integration Services is at version 9.00.3310 and i could connect to it from the Passive node SSMS.

    Probably the Windows Team applied some Windows Hotfixes on the cluster few weeks back and the SSIS was also patched then,I was not informed about this.

    So basically the DB engine and client tools are at a lower version than SSIS which is why I cant connect.I have also gone through the below KB article and tried registing the dlls,but with no success in connecting to SSIS.

    http://support.microsoft.com/kb/919224

    I would like to ask if there is any other work-around besides bringing all of them to the same version level,inorder to connect to SSIS from Management studio from active node?

  • July 28, 2009 at 9:43 am

    #1031133

    Any suggestions guys?

  • July 28, 2009 at 10:02 am

    #1031143

    I had a similar issue.

    Also tried the regsvr32 approach.

    Only thing that resolved it was to remove and re-install the client tools.... :crying:

  • July 28, 2009 at 12:22 pm

    #1031235

    integration services is not cluster aware or instance aware, the connection is derived from MsDtsSrvr.ini.xml file which is stored locally on each node, I have a feeling the issue you are seeing is due to this either being configured differently on each node, or the packages you have set up using the physical node name instead of the cluster. Check the article below to figure how this can be set.

    http://bloggingabout.net/blogs/mglaser/archive/2007/03/01/multiple-sql-server-integration-services-ssis-database-instances-on-one-machine.aspx

  • July 29, 2009 at 10:31 am

    #1031867

    Thanks for yr suggestion..but i have moved on from the time i used to do this around a year back.The server name in the xml file is the sql cluster virtual name.

    Any other suggestions guys? Is this too common an issue or just not an interesting one?

  • July 29, 2009 at 11:15 am

    #1031905

    When you did the install for the clustering, the SQL portion that is, did you install the client tools on both nodes, the client tools will install on the active node you are installing from but you need to install them seperatley on the other node as well, just making guesses really, these are a couple of issues I have come across.

  • July 29, 2009 at 11:28 am

    #1031916

    SQL was clustered around a year back and i had installed client tools separately on both nodes as well as Integration services.

    I think I will just patch all nodes with SQL 2005 SP3..database,client tools,integration services so that all are at the same level.

  • January 27, 2010 at 8:23 am

    #1109331

    hi,

    I'm having similiar problems with not being able to access SSIS from SSMS - did you SSIS or just the workstation components and reinstall?

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • January 27, 2010 at 9:10 am

    #1109366

    Just to let anyone else who hits this problem know.

    I too tried the msdn support article listed and registering the dll, but my solution was to remove and reinstall client tools.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply