SSIS runs immediatley after creation but errors occur when trying to create a job for it.

  • Hello All, I hope someone can shed some light on my situation.

    I am trying to connect to a remote database using the host name and socket number, the connection tests succeed and I am able to create a package that simply takes one table from the remote db and puts it into a local db.

    If I choose to run it immediatley as well as save it in SQL Server, it will do the initial transfer, but I am unable to then connect to Integration Services and look in the MSDB folder (either on the server or on my computer on my desk).

    I get the error...

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

    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)

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

    I have made sure that remote access is enabled and using both named pipes and TCP/IP, and I assume the situation is the same on the remote server as I can register and view the db server in Management Studio (I'm not sure if this is anything to do with the remote server though, i'm sure i'll hit more problems due to that later on).

    If I try and create a job and try to select SSIS package as one of the steps I get the following error...

    TITLE: Microsoft SQL Server Management Studio

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

    A procedure imported by 'DTEParseMgd, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' could not be loaded. (SqlManagerUI)

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

    ADDITIONAL INFORMATION:

    The specified procedure could not be found. (Exception from HRESULT: 0x8007007F)

    In a nutshell, I am just having real trouble in using SSIS and DTS. Some of my old DTS packages have stopped working when run from the server, but run OK when I run them from my workstation. I'm sure it's to do with permissions somewhere along the line, but I've not changed anything and cant really go changing the SQLSVRAGENT account for fear of damaging other processes.

    I'm a relativley new DBA/Developer and would appreciate any advice or documentation you can think of.

    Kind Regards,

  • I had a similar problem once and I had to change a setting (I think it was the instance name) in the following config file

    C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

    David

Viewing 2 posts - 1 through 1 (of 1 total)

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